[Home] [Help]
524: l_statem := l_statem||' p_payroll_id => :l_payroll, ';
525: l_statem := l_statem||' p_bus_grp => :l_bus_grp, ';
526: l_statem := l_statem||' p_asg_action => :l_asg_act); end; ';
527: --
528: sql_cursor := dbms_sql.open_cursor;
529: --
530: dbms_sql.parse(sql_cursor, l_statem, dbms_sql.v7);
531: --
532: --
526: l_statem := l_statem||' p_asg_action => :l_asg_act); end; ';
527: --
528: sql_cursor := dbms_sql.open_cursor;
529: --
530: dbms_sql.parse(sql_cursor, l_statem, dbms_sql.v7);
531: --
532: --
533: dbms_sql.bind_variable(sql_cursor, 'l_eff_date', p_effective_date);
534: dbms_sql.bind_variable(sql_cursor, 'l_start_date', p_start_date);
529: --
530: dbms_sql.parse(sql_cursor, l_statem, dbms_sql.v7);
531: --
532: --
533: dbms_sql.bind_variable(sql_cursor, 'l_eff_date', p_effective_date);
534: dbms_sql.bind_variable(sql_cursor, 'l_start_date', p_start_date);
535: dbms_sql.bind_variable(sql_cursor, 'l_payroll', p_payroll_id);
536: dbms_sql.bind_variable(sql_cursor, 'l_bus_grp', p_bus_grp);
537: dbms_sql.bind_variable(sql_cursor, 'l_asg_act', p_asg_action);
530: dbms_sql.parse(sql_cursor, l_statem, dbms_sql.v7);
531: --
532: --
533: dbms_sql.bind_variable(sql_cursor, 'l_eff_date', p_effective_date);
534: dbms_sql.bind_variable(sql_cursor, 'l_start_date', p_start_date);
535: dbms_sql.bind_variable(sql_cursor, 'l_payroll', p_payroll_id);
536: dbms_sql.bind_variable(sql_cursor, 'l_bus_grp', p_bus_grp);
537: dbms_sql.bind_variable(sql_cursor, 'l_asg_act', p_asg_action);
538: --
531: --
532: --
533: dbms_sql.bind_variable(sql_cursor, 'l_eff_date', p_effective_date);
534: dbms_sql.bind_variable(sql_cursor, 'l_start_date', p_start_date);
535: dbms_sql.bind_variable(sql_cursor, 'l_payroll', p_payroll_id);
536: dbms_sql.bind_variable(sql_cursor, 'l_bus_grp', p_bus_grp);
537: dbms_sql.bind_variable(sql_cursor, 'l_asg_act', p_asg_action);
538: --
539: l_rows := dbms_sql.execute(sql_cursor);
532: --
533: dbms_sql.bind_variable(sql_cursor, 'l_eff_date', p_effective_date);
534: dbms_sql.bind_variable(sql_cursor, 'l_start_date', p_start_date);
535: dbms_sql.bind_variable(sql_cursor, 'l_payroll', p_payroll_id);
536: dbms_sql.bind_variable(sql_cursor, 'l_bus_grp', p_bus_grp);
537: dbms_sql.bind_variable(sql_cursor, 'l_asg_act', p_asg_action);
538: --
539: l_rows := dbms_sql.execute(sql_cursor);
540: --
533: dbms_sql.bind_variable(sql_cursor, 'l_eff_date', p_effective_date);
534: dbms_sql.bind_variable(sql_cursor, 'l_start_date', p_start_date);
535: dbms_sql.bind_variable(sql_cursor, 'l_payroll', p_payroll_id);
536: dbms_sql.bind_variable(sql_cursor, 'l_bus_grp', p_bus_grp);
537: dbms_sql.bind_variable(sql_cursor, 'l_asg_act', p_asg_action);
538: --
539: l_rows := dbms_sql.execute(sql_cursor);
540: --
541: if (l_rows = 1) then
535: dbms_sql.bind_variable(sql_cursor, 'l_payroll', p_payroll_id);
536: dbms_sql.bind_variable(sql_cursor, 'l_bus_grp', p_bus_grp);
537: dbms_sql.bind_variable(sql_cursor, 'l_asg_act', p_asg_action);
538: --
539: l_rows := dbms_sql.execute(sql_cursor);
540: --
541: if (l_rows = 1) then
542: dbms_sql.variable_value(sql_cursor, 'l_start_date',
543: l_return_date);
538: --
539: l_rows := dbms_sql.execute(sql_cursor);
540: --
541: if (l_rows = 1) then
542: dbms_sql.variable_value(sql_cursor, 'l_start_date',
543: l_return_date);
544: dbms_sql.close_cursor(sql_cursor);
545: --
546: else
540: --
541: if (l_rows = 1) then
542: dbms_sql.variable_value(sql_cursor, 'l_start_date',
543: l_return_date);
544: dbms_sql.close_cursor(sql_cursor);
545: --
546: else
547: l_return_date := to_date('0001/01/01 00:00:00', 'YYYY/MM/DD HH24:MI:SS');
548: dbms_sql.close_cursor(sql_cursor);
544: dbms_sql.close_cursor(sql_cursor);
545: --
546: else
547: l_return_date := to_date('0001/01/01 00:00:00', 'YYYY/MM/DD HH24:MI:SS');
548: dbms_sql.close_cursor(sql_cursor);
549: end if;
550: --
551: end if;
552: --
1474: --
1475: if g_debug then
1476: hr_utility.set_location ('pay_balance_pkg.run_db_item', 15);
1477: end if;
1478: sql_cursor := dbms_sql.open_cursor; -- step 1
1479: --
1480: if g_debug then
1481: hr_utility.set_location ('pay_balance_pkg.run_db_item', 20);
1482: end if;
1479: --
1480: if g_debug then
1481: hr_utility.set_location ('pay_balance_pkg.run_db_item', 20);
1482: end if;
1483: dbms_sql.parse(sql_cursor, l_text, dbms_sql.v7); -- step 2
1484: --
1485: -- -- step 3
1486: -- At this stage we have parsed the route. Now bind the
1487: -- variables, starting with the contexts (B values).
1498: --
1499: -- Found a bind variable
1500: --
1501: if (con_type_tab (l_count) = 'D') then
1502: dbms_sql.bind_variable (sql_cursor, con_name_tab (l_count),
1503: fnd_date.canonical_to_date(ltrim(rtrim(con_value_tab (l_count)))));
1504: else
1505: dbms_sql.bind_variable (sql_cursor, con_name_tab (l_count),
1506: ltrim(rtrim(con_value_tab (l_count))));
1501: if (con_type_tab (l_count) = 'D') then
1502: dbms_sql.bind_variable (sql_cursor, con_name_tab (l_count),
1503: fnd_date.canonical_to_date(ltrim(rtrim(con_value_tab (l_count)))));
1504: else
1505: dbms_sql.bind_variable (sql_cursor, con_name_tab (l_count),
1506: ltrim(rtrim(con_value_tab (l_count))));
1507: end if;
1508: l_context_found := TRUE;
1509: exit; -- exit while loop
1531: --
1532: -- Now bind the where clause fillers (the 'U' values)
1533: --
1534: for l_par_num in 1..route_parameters_cache.sz loop
1535: dbms_sql.bind_variable (sql_cursor, 'U'||to_char(route_parameters_cache.seq_no(l_par_num)),
1536: route_parameters_cache.par_val(l_par_num));
1537: end loop;
1538: --
1539: if g_debug then
1538: --
1539: if g_debug then
1540: hr_utility.set_location ('pay_balance_pkg.run_db_item', 25);
1541: end if;
1542: dbms_sql.define_column (sql_cursor, 1, p_db_output, 240); -- step 4
1543: --
1544: if g_debug then
1545: hr_utility.set_location ('pay_balance_pkg.run_db_item', 30);
1546: end if;
1543: --
1544: if g_debug then
1545: hr_utility.set_location ('pay_balance_pkg.run_db_item', 30);
1546: end if;
1547: l_rows := dbms_sql.execute_and_fetch (sql_cursor, false); -- step 5
1548: --
1549: if (l_rows = 1) then
1550: if g_debug then
1551: hr_utility.set_location ('pay_balance_pkg.run_db_item', 35);
1549: if (l_rows = 1) then
1550: if g_debug then
1551: hr_utility.set_location ('pay_balance_pkg.run_db_item', 35);
1552: end if;
1553: dbms_sql.column_value (sql_cursor, 1, p_db_output); -- step 6
1554: --
1555: if g_debug then
1556: hr_utility.set_location ('pay_balance_pkg.run_db_item', 40);
1557: end if;
1554: --
1555: if g_debug then
1556: hr_utility.set_location ('pay_balance_pkg.run_db_item', 40);
1557: end if;
1558: dbms_sql.close_cursor(sql_cursor); -- step 7
1559: --
1560: if g_debug then
1561: hr_utility.trace ('DB value = ' || p_db_output);
1562: end if;
1561: hr_utility.trace ('DB value = ' || p_db_output);
1562: end if;
1563: --
1564: elsif (l_rows = 0) then
1565: dbms_sql.close_cursor(sql_cursor);
1566: if (l_notfound_allowed_flag = 'Y') then
1567: --
1568: -- its ok to not find a row
1569: --
1584: --
1585: -- More than 1 row have been returned. We must error as DB items can
1586: -- only return 1 row.
1587: --
1588: dbms_sql.close_cursor(sql_cursor);
1589: hr_utility.set_message(801, 'HR_7273_PAY_MORE_THAN_1_ROW');
1590: hr_utility.raise_error;
1591: end if;
1592: return p_db_output;
1600: l_error_text := sqlerrm;
1601: if g_debug then
1602: Hr_Utility.Set_Location('run_db_item', 100);
1603: end if;
1604: If (dbms_sql.is_open(sql_cursor)) then
1605: if g_debug then
1606: Hr_Utility.Set_Location('run_db_item', 105);
1607: end if;
1608: dbms_sql.close_cursor(sql_cursor);
1604: If (dbms_sql.is_open(sql_cursor)) then
1605: if g_debug then
1606: Hr_Utility.Set_Location('run_db_item', 105);
1607: end if;
1608: dbms_sql.close_cursor(sql_cursor);
1609: End If;
1610: hr_utility.set_message(801, 'HR_7276_PAY_FAILED_DB_ITEM');
1611: hr_utility.set_message_token ('ERROR_MESSAGE', l_error_text);
1612: hr_utility.raise_error;
1906: --
1907: if g_debug then
1908: hr_utility.set_location ('pay_balance_pkg.process_balance_statement', 35);
1909: end if;
1910: sql_cursor := dbms_sql.open_cursor; -- step 1
1911: --
1912: if g_debug then
1913: hr_utility.set_location ('pay_balance_pkg.process_balance_statement', 40);
1914: end if;
1911: --
1912: if g_debug then
1913: hr_utility.set_location ('pay_balance_pkg.process_balance_statement', 40);
1914: end if;
1915: dbms_sql.parse(sql_cursor, p_statement, dbms_sql.v7); -- step 2
1916: --
1917: -- -- step 3
1918: -- At this stage we have parsed the route. Now bind the
1919: -- variables, starting with the contexts (B values).
1936: if (con_type_tab (l_count) = 'D') then
1937: if g_debug then
1938: hr_utility.set_location ('pay_balance_pkg.process_balance_statement', 50);
1939: end if;
1940: dbms_sql.bind_variable
1941: (sql_cursor,
1942: con_name_tab(l_count),
1943: fnd_date.canonical_to_date(ltrim(rtrim(con_value_tab (l_count)))));
1944: else
1944: else
1945: if g_debug then
1946: hr_utility.set_location ('pay_balance_pkg.process_balance_statement', 55);
1947: end if;
1948: dbms_sql.bind_variable
1949: (sql_cursor,
1950: con_name_tab(l_count),
1951: ltrim(rtrim(con_value_tab (l_count))));
1952: end if;
1980: if g_debug then
1981: hr_utility.set_location ('pay_balance_pkg.process_balance_statement', 62);
1982: hr_utility.trace('p_balance_type_id = '||p_balance_type_id);
1983: end if;
1984: dbms_sql.bind_variable (sql_cursor, 'U1',
1985: p_balance_type_id);
1986: end if;
1987: --
1988: if (p_jur_level_required = TRUE) then
1985: p_balance_type_id);
1986: end if;
1987: --
1988: if (p_jur_level_required = TRUE) then
1989: dbms_sql.bind_variable (sql_cursor, 'JURISDICTION_LEVEL',
1990: p_jur_lvl);
1991: end if;
1992: --
1993: if g_debug then
1992: --
1993: if g_debug then
1994: hr_utility.set_location ('pay_balance_pkg.process_balance_statement', 65);
1995: end if;
1996: dbms_sql.define_column (sql_cursor, 1, l_db_output, 60); -- step 4
1997: if (p_batch_mode = TRUE) then
1998: dbms_sql.define_column (sql_cursor, 2, l_balance_type_id);
1999: end if;
2000: --
1994: hr_utility.set_location ('pay_balance_pkg.process_balance_statement', 65);
1995: end if;
1996: dbms_sql.define_column (sql_cursor, 1, l_db_output, 60); -- step 4
1997: if (p_batch_mode = TRUE) then
1998: dbms_sql.define_column (sql_cursor, 2, l_balance_type_id);
1999: end if;
2000: --
2001: if g_debug then
2002: hr_utility.set_location ('pay_balance_pkg.process_balance_statement', 70);
2000: --
2001: if g_debug then
2002: hr_utility.set_location ('pay_balance_pkg.process_balance_statement', 70);
2003: end if;
2004: ignore := dbms_sql.execute(sql_cursor);
2005: --
2006: l_retrieve := TRUE;
2007: l_value_retrieved := FALSE;
2008: while (l_retrieve = TRUE) loop
2006: l_retrieve := TRUE;
2007: l_value_retrieved := FALSE;
2008: while (l_retrieve = TRUE) loop
2009: --
2010: l_rows := dbms_sql.fetch_rows(sql_cursor);
2011: --
2012: if g_debug then
2013: hr_utility.set_location ('pay_balance_pkg.process_balance_statement', 80);
2014: end if;
2017: --
2018: if g_debug then
2019: hr_utility.set_location ('pay_balance_pkg.process_balance_statement', 85);
2020: end if;
2021: dbms_sql.column_value (sql_cursor, 1, l_db_output);
2022: if (p_batch_mode = TRUE) then
2023: dbms_sql.column_value (sql_cursor, 2, l_balance_type_id);
2024: --
2025: -- Search batch to populate value.
2019: hr_utility.set_location ('pay_balance_pkg.process_balance_statement', 85);
2020: end if;
2021: dbms_sql.column_value (sql_cursor, 1, l_db_output);
2022: if (p_batch_mode = TRUE) then
2023: dbms_sql.column_value (sql_cursor, 2, l_balance_type_id);
2024: --
2025: -- Search batch to populate value.
2026: --
2027: for cnt in p_batch_str_ptr..p_batch_end_ptr loop
2045: if (l_value_retrieved = TRUE) then
2046: if g_debug then
2047: hr_utility.set_location ('pay_balance_pkg.process_balance_statement', 90);
2048: end if;
2049: dbms_sql.close_cursor(sql_cursor);
2050: hr_utility.set_message(801, 'HR_7273_PAY_MORE_THAN_1_ROW');
2051: hr_utility.raise_error;
2052: end if;
2053: if g_debug then
2062: else
2063: l_retrieve := FALSE;
2064: end if;
2065: end loop;
2066: dbms_sql.close_cursor(sql_cursor);
2067: --
2068: if (l_value_retrieved = FALSE and p_batch_mode = FALSE) then
2069: --
2070: -- Error, no rows found when the entity has been defined to always
2083: l_error_text := sqlerrm;
2084: if g_debug then
2085: Hr_Utility.Set_Location('pay_balance_pkg.process_balance_statement', 100);
2086: end if;
2087: If (dbms_sql.is_open(sql_cursor)) then
2088: if g_debug then
2089: Hr_Utility.Set_Location('pay_balance_pkg.process_balance_statement', 105);
2090: end if;
2091: dbms_sql.close_cursor(sql_cursor);
2087: If (dbms_sql.is_open(sql_cursor)) then
2088: if g_debug then
2089: Hr_Utility.Set_Location('pay_balance_pkg.process_balance_statement', 105);
2090: end if;
2091: dbms_sql.close_cursor(sql_cursor);
2092: End If;
2093: hr_utility.set_message(801, 'HR_7276_PAY_FAILED_DB_ITEM');
2094: hr_utility.set_message_token ('ERROR_MESSAGE', l_error_text);
2095: hr_utility.raise_error;
2534: --
2535: if g_debug then
2536: hr_utility.set_location ('pay_balance_pkg.get_run_balance', 35);
2537: end if;
2538: sql_cursor := dbms_sql.open_cursor; -- step 1
2539: --
2540: if g_debug then
2541: hr_utility.set_location ('pay_balance_pkg.get_run_balance', 40);
2542: end if;
2539: --
2540: if g_debug then
2541: hr_utility.set_location ('pay_balance_pkg.get_run_balance', 40);
2542: end if;
2543: dbms_sql.parse(sql_cursor, l_text, dbms_sql.v7); -- step 2
2544: --
2545: -- -- step 3
2546: -- At this stage we have parsed the route. Now bind the
2547: -- variables, starting with the contexts (B values).
2564: if (con_type_tab (l_count) = 'D') then
2565: if g_debug then
2566: hr_utility.set_location ('pay_balance_pkg.get_run_balance', 50);
2567: end if;
2568: dbms_sql.bind_variable
2569: (sql_cursor,
2570: con_name_tab(l_count),
2571: fnd_date.canonical_to_date(ltrim(rtrim(con_value_tab (l_count)))));
2572: else
2572: else
2573: if g_debug then
2574: hr_utility.set_location ('pay_balance_pkg.get_run_balance', 55);
2575: end if;
2576: dbms_sql.bind_variable
2577: (sql_cursor,
2578: con_name_tab(l_count),
2579: ltrim(rtrim(con_value_tab (l_count))));
2580: end if;
2607: for l_par_num in 1..route_parameters_cache.sz loop
2608: if g_debug then
2609: hr_utility.set_location ('pay_balance_pkg.get_run_balance', 62);
2610: end if;
2611: dbms_sql.bind_variable (sql_cursor, 'U'||to_char(route_parameters_cache.seq_no(l_par_num)),
2612: route_parameters_cache.par_val(l_par_num));
2613: end loop;
2614: --
2615: if g_debug then
2614: --
2615: if g_debug then
2616: hr_utility.set_location ('pay_balance_pkg.get_run_balance', 65);
2617: end if;
2618: dbms_sql.define_column (sql_cursor, 1, p_db_output, 80); -- step 4
2619: --
2620: if g_debug then
2621: hr_utility.set_location ('pay_balance_pkg.get_run_balance', 70);
2622: end if;
2619: --
2620: if g_debug then
2621: hr_utility.set_location ('pay_balance_pkg.get_run_balance', 70);
2622: end if;
2623: l_rows := dbms_sql.execute_and_fetch (sql_cursor, false); -- step 5
2624: --
2625: if (l_rows = 1) then
2626: if g_debug then
2627: hr_utility.set_location ('pay_balance_pkg.get_run_balance', 75);
2625: if (l_rows = 1) then
2626: if g_debug then
2627: hr_utility.set_location ('pay_balance_pkg.get_run_balance', 75);
2628: end if;
2629: dbms_sql.column_value (sql_cursor, 1, p_db_output); -- step 6
2630: --
2631: if g_debug then
2632: hr_utility.set_location ('pay_balance_pkg.get_run_balance', 80);
2633: end if;
2630: --
2631: if g_debug then
2632: hr_utility.set_location ('pay_balance_pkg.get_run_balance', 80);
2633: end if;
2634: dbms_sql.close_cursor(sql_cursor); -- step 7
2635: --
2636: if g_debug then
2637: hr_utility.trace ('DB value = ' || p_db_output);
2638: end if;
2637: hr_utility.trace ('DB value = ' || p_db_output);
2638: end if;
2639: --
2640: elsif (l_rows = 0) then
2641: dbms_sql.close_cursor(sql_cursor);
2642: if (l_notfound_allowed_flag = 'Y') then
2643: --
2644: -- its ok to not find a row
2645: --
2663: --
2664: if g_debug then
2665: hr_utility.set_location ('pay_balance_pkg.get_run_balance', 95);
2666: end if;
2667: dbms_sql.close_cursor(sql_cursor);
2668: hr_utility.set_message(801, 'HR_7273_PAY_MORE_THAN_1_ROW');
2669: hr_utility.raise_error;
2670: end if;
2671: return p_db_output;
2679: l_error_text := sqlerrm;
2680: if g_debug then
2681: Hr_Utility.Set_Location('pay_balance_pkg.get_run_balance', 100);
2682: end if;
2683: If (dbms_sql.is_open(sql_cursor)) then
2684: if g_debug then
2685: Hr_Utility.Set_Location('pay_balance_pkg.get_run_balance', 105);
2686: end if;
2687: dbms_sql.close_cursor(sql_cursor);
2683: If (dbms_sql.is_open(sql_cursor)) then
2684: if g_debug then
2685: Hr_Utility.Set_Location('pay_balance_pkg.get_run_balance', 105);
2686: end if;
2687: dbms_sql.close_cursor(sql_cursor);
2688: End If;
2689: hr_utility.set_message(801, 'HR_7276_PAY_FAILED_DB_ITEM');
2690: hr_utility.set_message_token ('ERROR_MESSAGE', l_error_text);
2691: hr_utility.raise_error;
3276: --
3277: if g_debug then
3278: hr_utility.set_location ('pay_balance_pkg.check_bal_expiry_internal', 20);
3279: end if;
3280: sql_cursor := dbms_sql.open_cursor; -- step 1
3281: --
3282: if g_debug then
3283: hr_utility.set_location ('pay_balance_pkg.check_bal_expiry_internal', 25);
3284: end if;
3281: --
3282: if g_debug then
3283: hr_utility.set_location ('pay_balance_pkg.check_bal_expiry_internal', 25);
3284: end if;
3285: dbms_sql.parse(sql_cursor, l_expiry_chk_str, dbms_sql.v7); -- step 2
3286: --
3287: if g_debug then
3288: hr_utility.set_location ('pay_balance_pkg.check_bal_expiry_internal', 30);
3289: end if;
3286: --
3287: if g_debug then
3288: hr_utility.set_location ('pay_balance_pkg.check_bal_expiry_internal', 30);
3289: end if;
3290: dbms_sql.bind_variable(sql_cursor, 'l_bal_owner_pay_action', -- step 3:
3291: l_bal_owner_pay_action);
3292: --
3293: dbms_sql.bind_variable(sql_cursor, 'l_payroll_action',
3294: l_payroll_action);
3289: end if;
3290: dbms_sql.bind_variable(sql_cursor, 'l_bal_owner_pay_action', -- step 3:
3291: l_bal_owner_pay_action);
3292: --
3293: dbms_sql.bind_variable(sql_cursor, 'l_payroll_action',
3294: l_payroll_action);
3295: --
3296: dbms_sql.bind_variable(sql_cursor, 'p_bal_owner_asg_action',
3297: p_bal_owner_asg_action);
3292: --
3293: dbms_sql.bind_variable(sql_cursor, 'l_payroll_action',
3294: l_payroll_action);
3295: --
3296: dbms_sql.bind_variable(sql_cursor, 'p_bal_owner_asg_action',
3297: p_bal_owner_asg_action);
3298: --
3299: dbms_sql.bind_variable(sql_cursor, 'p_assignment_action_id',
3300: p_assignment_action_id);
3295: --
3296: dbms_sql.bind_variable(sql_cursor, 'p_bal_owner_asg_action',
3297: p_bal_owner_asg_action);
3298: --
3299: dbms_sql.bind_variable(sql_cursor, 'p_assignment_action_id',
3300: p_assignment_action_id);
3301: --
3302: dbms_sql.bind_variable(sql_cursor, 'l_bal_owner_eff_date',
3303: l_bal_owner_eff_date);
3298: --
3299: dbms_sql.bind_variable(sql_cursor, 'p_assignment_action_id',
3300: p_assignment_action_id);
3301: --
3302: dbms_sql.bind_variable(sql_cursor, 'l_bal_owner_eff_date',
3303: l_bal_owner_eff_date);
3304: --
3305: dbms_sql.bind_variable(sql_cursor, 'l_effective_date',
3306: l_effective_date);
3301: --
3302: dbms_sql.bind_variable(sql_cursor, 'l_bal_owner_eff_date',
3303: l_bal_owner_eff_date);
3304: --
3305: dbms_sql.bind_variable(sql_cursor, 'l_effective_date',
3306: l_effective_date);
3307: --
3308: dbms_sql.bind_variable(sql_cursor, 'p_dimension_name',
3309: p_dimension_name);
3304: --
3305: dbms_sql.bind_variable(sql_cursor, 'l_effective_date',
3306: l_effective_date);
3307: --
3308: dbms_sql.bind_variable(sql_cursor, 'p_dimension_name',
3309: p_dimension_name);
3310: --
3311: if (p_expiry_checking_level in ('A', 'D')) then
3312: dbms_sql.bind_variable(sql_cursor, 'p_bal_context_str',
3308: dbms_sql.bind_variable(sql_cursor, 'p_dimension_name',
3309: p_dimension_name);
3310: --
3311: if (p_expiry_checking_level in ('A', 'D')) then
3312: dbms_sql.bind_variable(sql_cursor, 'p_bal_context_str',
3313: p_bal_context_str);
3314: end if;
3315: --
3316: dbms_sql.bind_variable(sql_cursor, 'l_expiry_information',
3312: dbms_sql.bind_variable(sql_cursor, 'p_bal_context_str',
3313: p_bal_context_str);
3314: end if;
3315: --
3316: dbms_sql.bind_variable(sql_cursor, 'l_expiry_information',
3317: l_expiry_information);
3318: --
3319: if g_debug then
3320: hr_utility.set_location ('pay_balance_pkg.check_bal_expiry_internal', 35);
3318: --
3319: if g_debug then
3320: hr_utility.set_location ('pay_balance_pkg.check_bal_expiry_internal', 35);
3321: end if;
3322: l_rows := dbms_sql.execute (sql_cursor); -- step 4
3323: --
3324: if (l_rows = 1) then
3325: if g_debug then
3326: hr_utility.set_location ('pay_balance_pkg.check_bal_expiry_internal', 40);
3324: if (l_rows = 1) then
3325: if g_debug then
3326: hr_utility.set_location ('pay_balance_pkg.check_bal_expiry_internal', 40);
3327: end if;
3328: dbms_sql.variable_value(sql_cursor, 'l_expiry_information', -- step 5
3329: l_expiry_information);
3330: --
3331: if g_debug then
3332: hr_utility.set_location ('pay_balance_pkg.check_bal_expiry_internal', 45);
3330: --
3331: if g_debug then
3332: hr_utility.set_location ('pay_balance_pkg.check_bal_expiry_internal', 45);
3333: end if;
3334: dbms_sql.close_cursor(sql_cursor); -- step 6
3335: elsif (l_rows = 0) then
3336: dbms_sql.close_cursor(sql_cursor);
3337: hr_utility.set_message(801, 'HR_7274_PAY_NO_EXPIRY_CODE');
3338: hr_utility.set_message_token ('EXPIRY_CODE', p_expiry_checking_code);
3332: hr_utility.set_location ('pay_balance_pkg.check_bal_expiry_internal', 45);
3333: end if;
3334: dbms_sql.close_cursor(sql_cursor); -- step 6
3335: elsif (l_rows = 0) then
3336: dbms_sql.close_cursor(sql_cursor);
3337: hr_utility.set_message(801, 'HR_7274_PAY_NO_EXPIRY_CODE');
3338: hr_utility.set_message_token ('EXPIRY_CODE', p_expiry_checking_code);
3339: hr_utility.raise_error;
3340: else
3344: --
3345: if g_debug then
3346: hr_utility.set_location ('pay_balance_pkg.check_bal_expiry_internal', 111);
3347: end if;
3348: dbms_sql.close_cursor(sql_cursor);
3349: hr_utility.raise_error;
3350: end if;
3351: --
3352: -- At this point we have executed the dynamic pl/sql, 1 row was returned
3411: l_error_text := sqlerrm;
3412: if g_debug then
3413: Hr_Utility.Set_Location('check_bal_expiry_internal', 115);
3414: end if;
3415: If (dbms_sql.is_open(sql_cursor)) then
3416: if g_debug then
3417: Hr_Utility.Set_Location('check_bal_expiry_internal', 120);
3418: end if;
3419: dbms_sql.close_cursor(sql_cursor);
3415: If (dbms_sql.is_open(sql_cursor)) then
3416: if g_debug then
3417: Hr_Utility.Set_Location('check_bal_expiry_internal', 120);
3418: end if;
3419: dbms_sql.close_cursor(sql_cursor);
3420: End If;
3421: hr_utility.set_message(801, 'HR_7275_PAY_FAILED_IN_EXPIRY');
3422: hr_utility.set_message_token ('ERROR_MESSAGE', l_error_text);
3423: hr_utility.raise_error;