[Home] [Help]
90: );
91: --
92: type t_def_bal_tab is table of t_def_bal_rec index by binary_integer;
93: --
94: g_debug boolean := hr_utility.debug_enabled;
95: g_defbal_cache t_def_bal_tab;
96: --
97: --
98: -- tables for dimension route cache
323: l_proc VARCHAR2(72);
324: l_version VARCHAR2(30);
325: l_compatibility VARCHAR2(30);
326: BEGIN
327: g_debug := hr_utility.debug_enabled;
328: IF g_debug THEN
329: l_proc := 'pay_balance_pkg.get_oracle_db_version';
330: hr_utility.set_location('Entering:'||l_proc, 5);
331: END IF;
326: BEGIN
327: g_debug := hr_utility.debug_enabled;
328: IF g_debug THEN
329: l_proc := 'pay_balance_pkg.get_oracle_db_version';
330: hr_utility.set_location('Entering:'||l_proc, 5);
331: END IF;
332: -- check to see if the g_oracle_version already exists
333: IF g_oracle_version IS NULL THEN
334: -- get the current ORACLE version and compatibility values
350: g_oracle_version :=
351: TO_NUMBER(SUBSTRB(l_version,1,INSTRB(l_version,'.',1,2)-1),'99.99');
352: END IF;
353: IF g_debug THEN
354: hr_utility.set_location('Leaving:'||l_proc, 10);
355: END IF;
356: -- return the value
357: RETURN(g_oracle_version);
358: EXCEPTION
360: -- an unexpected error was raised and is most probably caused by
361: -- the TO_NUMBER conversion. Because of this, return NULL
362: -- indicating that the Oracle Version number could NOT be assertained
363: IF g_debug THEN
364: hr_utility.set_location('Leaving:'||l_proc, 15);
365: END IF;
366: RETURN(NULL);
367: END get_oracle_db_version;
368: --
437: l_statem varchar2(2000); -- used with dynamic pl/sql
438: sql_cursor integer;
439: l_rows integer;
440: begin
441: g_debug := hr_utility.debug_enabled;
442: --
443: l_return_date := to_date('0001/01/01 00:00:00', 'YYYY/MM/DD HH24:MI:SS');
444: --
445: if (p_period_type = 'YEAR') then
587: l_statem varchar2(2000); -- used with dynamic pl/sql
588: sql_cursor integer;
589: l_rows integer;
590: begin
591: g_debug := hr_utility.debug_enabled;
592: --
593: l_return_date := null;
594: --
595: if (p_period_type = 'YEAR') then
731: --
732: if not g_defbal_cache.exists(p_defined_balance_id) then
733: --
734: if g_debug then
735: hr_utility.set_location ('pay_balance_pkg.load_defbal_cache', 10);
736: end if;
737: select DIM.dimension_type,
738: DIM.expiry_checking_code,
739: DIM.expiry_checking_level,
795: --
796: -- Get the contexts needed.
797: for ctrec in get_bal_contexts(p_defined_balance_id) loop
798: if g_debug then
799: hr_utility.set_location('pay_balance_pkg.load_defbal_cache', 10);
800: end if;
801: if ctrec.context_name = 'TAX_UNIT_ID' then
802: g_defbal_cache(p_defined_balance_id).tu_needed := TRUE;
803: end if;
881: end;
882: --
883: end if;
884: if g_debug then
885: hr_utility.set_location ('pay_balance_pkg.load_defbal_cache', 15);
886: end if;
887: --
888: p_defbal_rec := g_defbal_cache(p_defined_balance_id);
889: --
950: v_con_value varchar2(60);
951: v_context_found boolean;
952:
953: BEGIN
954: g_debug := hr_utility.debug_enabled;
955: if g_debug then
956: hr_utility.set_location ('pybaluex.get_context_internal',1);
957: hr_utility.trace(no_rows_con_tab);
958: end if;
952:
953: BEGIN
954: g_debug := hr_utility.debug_enabled;
955: if g_debug then
956: hr_utility.set_location ('pybaluex.get_context_internal',1);
957: hr_utility.trace(no_rows_con_tab);
958: end if;
959: v_context_found := FALSE;
960:
953: BEGIN
954: g_debug := hr_utility.debug_enabled;
955: if g_debug then
956: hr_utility.set_location ('pybaluex.get_context_internal',1);
957: hr_utility.trace(no_rows_con_tab);
958: end if;
959: v_context_found := FALSE;
960:
961: for i in 0..no_rows_con_tab-1 LOOP
965: if con_name_tab(i) = p_context_name then
966: v_con_value:=con_value_tab(i); -- set return variable
967: v_context_found := TRUE; -- set found flag
968: if g_debug then
969: hr_utility.trace('i= '||i);
970: hr_utility.trace('name = '||con_name_tab(i));
971: hr_utility.trace('value = '||con_value_tab(i));
972: end if;
973: EXIT; -- drop out of loop
966: v_con_value:=con_value_tab(i); -- set return variable
967: v_context_found := TRUE; -- set found flag
968: if g_debug then
969: hr_utility.trace('i= '||i);
970: hr_utility.trace('name = '||con_name_tab(i));
971: hr_utility.trace('value = '||con_value_tab(i));
972: end if;
973: EXIT; -- drop out of loop
974: end if;
967: v_context_found := TRUE; -- set found flag
968: if g_debug then
969: hr_utility.trace('i= '||i);
970: hr_utility.trace('name = '||con_name_tab(i));
971: hr_utility.trace('value = '||con_value_tab(i));
972: end if;
973: EXIT; -- drop out of loop
974: end if;
975: end loop;
980: return NULL;
981: end if;
982: --
983: if g_debug then
984: hr_utility.set_location ('pybaluex.get_context_internal',3);
985: end if;
986: --
987: end get_context_internal;
988: --
992: v_con_value varchar2(60);
993: v_context_found boolean;
994:
995: BEGIN
996: g_debug := hr_utility.debug_enabled;
997: if g_debug then
998: hr_utility.set_location ('pybaluex.get_context',1);
999: hr_utility.trace(no_rows_con_tab);
1000: end if;
994:
995: BEGIN
996: g_debug := hr_utility.debug_enabled;
997: if g_debug then
998: hr_utility.set_location ('pybaluex.get_context',1);
999: hr_utility.trace(no_rows_con_tab);
1000: end if;
1001:
1002: v_con_value := get_context_internal(p_context_name);
995: BEGIN
996: g_debug := hr_utility.debug_enabled;
997: if g_debug then
998: hr_utility.set_location ('pybaluex.get_context',1);
999: hr_utility.trace(no_rows_con_tab);
1000: end if;
1001:
1002: v_con_value := get_context_internal(p_context_name);
1003:
1007: null;
1008: end if;
1009: --
1010: if g_debug then
1011: hr_utility.set_location ('pybaluex.get_context',3);
1012: end if;
1013: --
1014: end get_context;
1015: --
1043: l_context_type ff_contexts.data_type%type;
1044: l_count binary_integer;
1045: l_context_found boolean;
1046: begin
1047: g_debug := hr_utility.debug_enabled;
1048: if g_debug then
1049: hr_utility.set_location ('pay_balance_pkg.set_context', 10);
1050: hr_utility.trace('p_context_name : ' || p_context_name);
1051: hr_utility.trace('p_context_value : ' || p_context_value);
1045: l_context_found boolean;
1046: begin
1047: g_debug := hr_utility.debug_enabled;
1048: if g_debug then
1049: hr_utility.set_location ('pay_balance_pkg.set_context', 10);
1050: hr_utility.trace('p_context_name : ' || p_context_name);
1051: hr_utility.trace('p_context_value : ' || p_context_value);
1052: end if;
1053: l_context_found := FALSE;
1046: begin
1047: g_debug := hr_utility.debug_enabled;
1048: if g_debug then
1049: hr_utility.set_location ('pay_balance_pkg.set_context', 10);
1050: hr_utility.trace('p_context_name : ' || p_context_name);
1051: hr_utility.trace('p_context_value : ' || p_context_value);
1052: end if;
1053: l_context_found := FALSE;
1054: l_context_name := upper(p_context_name);
1047: g_debug := hr_utility.debug_enabled;
1048: if g_debug then
1049: hr_utility.set_location ('pay_balance_pkg.set_context', 10);
1050: hr_utility.trace('p_context_name : ' || p_context_name);
1051: hr_utility.trace('p_context_value : ' || p_context_value);
1052: end if;
1053: l_context_found := FALSE;
1054: l_context_name := upper(p_context_name);
1055: --
1073: --
1074: -- its a new context, insert into tables
1075: --
1076: if g_debug then
1077: hr_utility.set_location ('pay_balance_pkg.set_context', 20);
1078: end if;
1079: select context_id,
1080: data_type
1081: into l_context_id,
1094: -- print out the current state of the tables for debug purposes:
1095: --
1096: l_count := 0;
1097: if g_debug then
1098: hr_utility.trace
1099: ('i type context id context name context value');
1100: hr_utility.trace
1101: ('- ---- ---------- ------------ -------------');
1102: while (l_count < no_rows_con_tab) loop
1096: l_count := 0;
1097: if g_debug then
1098: hr_utility.trace
1099: ('i type context id context name context value');
1100: hr_utility.trace
1101: ('- ---- ---------- ------------ -------------');
1102: while (l_count < no_rows_con_tab) loop
1103: hr_utility.trace (rpad(to_char(l_count), 3) ||
1104: rpad(con_type_tab(l_count), 6) ||
1099: ('i type context id context name context value');
1100: hr_utility.trace
1101: ('- ---- ---------- ------------ -------------');
1102: while (l_count < no_rows_con_tab) loop
1103: hr_utility.trace (rpad(to_char(l_count), 3) ||
1104: rpad(con_type_tab(l_count), 6) ||
1105: rpad(con_id_tab(l_count), 12) ||
1106: rpad(con_name_tab(l_count), 23) ||
1107: con_value_tab(l_count));
1288: --
1289: ------------------------------- run_db_item -------------------------------
1290: --
1291: begin
1292: g_debug := hr_utility.debug_enabled;
1293: if g_debug then
1294: hr_utility.set_location ('pay_balance_pkg.run_db_item', 1);
1295: hr_utility.trace ('DB_item = ' || p_database_name);
1296: hr_utility.trace ('bus_grp = ' || to_char (p_bus_group_id));
1290: --
1291: begin
1292: g_debug := hr_utility.debug_enabled;
1293: if g_debug then
1294: hr_utility.set_location ('pay_balance_pkg.run_db_item', 1);
1295: hr_utility.trace ('DB_item = ' || p_database_name);
1296: hr_utility.trace ('bus_grp = ' || to_char (p_bus_group_id));
1297: hr_utility.trace ('leg_code = ' || p_legislation_code);
1298: end if;
1291: begin
1292: g_debug := hr_utility.debug_enabled;
1293: if g_debug then
1294: hr_utility.set_location ('pay_balance_pkg.run_db_item', 1);
1295: hr_utility.trace ('DB_item = ' || p_database_name);
1296: hr_utility.trace ('bus_grp = ' || to_char (p_bus_group_id));
1297: hr_utility.trace ('leg_code = ' || p_legislation_code);
1298: end if;
1299: --
1292: g_debug := hr_utility.debug_enabled;
1293: if g_debug then
1294: hr_utility.set_location ('pay_balance_pkg.run_db_item', 1);
1295: hr_utility.trace ('DB_item = ' || p_database_name);
1296: hr_utility.trace ('bus_grp = ' || to_char (p_bus_group_id));
1297: hr_utility.trace ('leg_code = ' || p_legislation_code);
1298: end if;
1299: --
1300: -- Get all the data to build up the route
1293: if g_debug then
1294: hr_utility.set_location ('pay_balance_pkg.run_db_item', 1);
1295: hr_utility.trace ('DB_item = ' || p_database_name);
1296: hr_utility.trace ('bus_grp = ' || to_char (p_bus_group_id));
1297: hr_utility.trace ('leg_code = ' || p_legislation_code);
1298: end if;
1299: --
1300: -- Get all the data to build up the route
1301: --
1359: -- Raise an error, as there are contexts that have not been set up in
1360: -- the pl/sql tables that are required by the route.
1361: --
1362: if g_debug then
1363: hr_utility.set_location ('pay_balance_pkg.run_db_item', 10);
1364: end if;
1365: select context_name
1366: into l_context_name
1367: from ff_contexts
1366: into l_context_name
1367: from ff_contexts
1368: where context_id = c1rec.context_id;
1369: --
1370: hr_utility.set_message(801, 'HR_7271_PAY_CONTEXT_MISSING');
1371: hr_utility.set_message_token ('CONTEXT_NAME', l_context_name);
1372: hr_utility.raise_error;
1373: end if;
1374: end loop; -- end of for c1rec loop
1367: from ff_contexts
1368: where context_id = c1rec.context_id;
1369: --
1370: hr_utility.set_message(801, 'HR_7271_PAY_CONTEXT_MISSING');
1371: hr_utility.set_message_token ('CONTEXT_NAME', l_context_name);
1372: hr_utility.raise_error;
1373: end if;
1374: end loop; -- end of for c1rec loop
1375: --
1368: where context_id = c1rec.context_id;
1369: --
1370: hr_utility.set_message(801, 'HR_7271_PAY_CONTEXT_MISSING');
1371: hr_utility.set_message_token ('CONTEXT_NAME', l_context_name);
1372: hr_utility.raise_error;
1373: end if;
1374: end loop; -- end of for c1rec loop
1375: --
1376: -- The following loop searches through and replaces all where clause fillers
1391: --
1392: -- Print the route text out for debug purposes. Normally keep this line
1393: -- commented out, as for a large route it causes an error in the trace
1394: -- utility.
1395: -- hr_utility.trace (l_text);
1396: --
1397: --
1398: -- now build up the full SQL statement:
1399: --
1472: -- 6. Get the sql value (providing there are rows returned)
1473: -- 7. Close the dynamic sql cursor
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
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;
1483: dbms_sql.parse(sql_cursor, l_text, dbms_sql.v7); -- step 2
1484: --
1485: -- -- step 3
1491: l_context_found := FALSE;
1492: while (l_count < no_rows_con_tab) loop
1493: if (con_id_tab(l_count) = route_contexts_cache.cxt_id(l_cxt_num)) then
1494: if g_debug then
1495: hr_utility.trace (con_name_tab (l_count) ||' = '||
1496: con_value_tab (l_count));
1497: end if;
1498: --
1499: -- Found a bind variable
1515: -- Raise an error, as there are contexts that have not been set up in
1516: -- the pl/sql tables that are required by the route.
1517: --
1518: if g_debug then
1519: hr_utility.set_location ('pay_balance_pkg.run_db_item', 101);
1520: end if;
1521: select context_name
1522: into l_context_name
1523: from ff_contexts
1522: into l_context_name
1523: from ff_contexts
1524: where context_id = route_contexts_cache.cxt_id(l_cxt_num);
1525: --
1526: hr_utility.set_message(801, 'HR_7271_PAY_CONTEXT_MISSING');
1527: hr_utility.set_message_token ('CONTEXT_NAME', l_context_name);
1528: hr_utility.raise_error;
1529: end if;
1530: end loop; -- end of for route contexts loop
1523: from ff_contexts
1524: where context_id = route_contexts_cache.cxt_id(l_cxt_num);
1525: --
1526: hr_utility.set_message(801, 'HR_7271_PAY_CONTEXT_MISSING');
1527: hr_utility.set_message_token ('CONTEXT_NAME', l_context_name);
1528: hr_utility.raise_error;
1529: end if;
1530: end loop; -- end of for route contexts loop
1531: --
1524: where context_id = route_contexts_cache.cxt_id(l_cxt_num);
1525: --
1526: hr_utility.set_message(801, 'HR_7271_PAY_CONTEXT_MISSING');
1527: hr_utility.set_message_token ('CONTEXT_NAME', l_context_name);
1528: hr_utility.raise_error;
1529: end if;
1530: end loop; -- end of for route contexts loop
1531: --
1532: -- Now bind the where clause fillers (the 'U' values)
1536: route_parameters_cache.par_val(l_par_num));
1537: end loop;
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
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;
1547: l_rows := dbms_sql.execute_and_fetch (sql_cursor, false); -- step 5
1548: --
1549: if (l_rows = 1) then
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);
1552: end if;
1553: dbms_sql.column_value (sql_cursor, 1, p_db_output); -- step 6
1554: --
1555: if g_debug then
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;
1558: dbms_sql.close_cursor(sql_cursor); -- step 7
1559: --
1560: if g_debug then
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;
1563: --
1564: elsif (l_rows = 0) then
1565: dbms_sql.close_cursor(sql_cursor);
1567: --
1568: -- its ok to not find a row
1569: --
1570: if g_debug then
1571: hr_utility.set_location ('pay_balance_pkg.run_db_item', 45);
1572: end if;
1573: p_db_output := null;
1574: else
1575: --
1575: --
1576: -- Error, no rows found when the entity has been defined to always
1577: -- find a row.
1578: --
1579: hr_utility.set_message(801, 'HR_7272_PAY_NO_ROWS_RETURNED');
1580: hr_utility.set_message_token ('DATABASE_NAME', p_database_name);
1581: hr_utility.raise_error;
1582: end if;
1583: else
1576: -- Error, no rows found when the entity has been defined to always
1577: -- find a row.
1578: --
1579: hr_utility.set_message(801, 'HR_7272_PAY_NO_ROWS_RETURNED');
1580: hr_utility.set_message_token ('DATABASE_NAME', p_database_name);
1581: hr_utility.raise_error;
1582: end if;
1583: else
1584: --
1577: -- find a row.
1578: --
1579: hr_utility.set_message(801, 'HR_7272_PAY_NO_ROWS_RETURNED');
1580: hr_utility.set_message_token ('DATABASE_NAME', p_database_name);
1581: hr_utility.raise_error;
1582: end if;
1583: else
1584: --
1585: -- More than 1 row have been returned. We must error as DB items can
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;
1593: exception
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;
1593: exception
1594: --
1598: --
1599: When Others Then
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);
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);
1609: End If;
1610: hr_utility.set_message(801, 'HR_7276_PAY_FAILED_DB_ITEM');
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;
1613: end run_db_item;
1614: --
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;
1613: end run_db_item;
1614: --
1615: --
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;
1613: end run_db_item;
1614: --
1615: --
1616: --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1685: l_select_clause || ')';
1686: end if;
1687: --
1688: if g_debug then
1689: hr_utility.trace(l_select_clause);
1690: end if;
1691: --
1692: --
1693: -- Now setup the balance type joining details.
1701: begin
1702: --
1703: l_select_component := ', '||p_balance_type_column;
1704: if g_debug then
1705: hr_utility.trace(l_select_component);
1706: end if;
1707: --
1708: l_select_clause := l_select_clause||l_select_component;
1709: l_from_clause := l_from_clause||' and '||p_balance_type_column||' in (';
1708: l_select_clause := l_select_clause||l_select_component;
1709: l_from_clause := l_from_clause||' and '||p_balance_type_column||' in (';
1710: if g_debug then
1711: if length(l_from_clause) <= MAX_TRACE_SIZE then
1712: hr_utility.trace(l_from_clause);
1713: end if;
1714: end if;
1715: --
1716: -- Now put the full statement in the from clause
1753: (MAX_DYN_SQL_SIZE - 50)) then
1754: --
1755: if g_debug then
1756: if l_comp_length <= MAX_TRACE_SIZE then
1757: hr_utility.trace(l_from_component);
1758: end if;
1759: end if;
1760: l_from_clause := l_from_clause||l_from_component;
1761: --
1774: --
1775: l_from_component := ' ) group by '||p_balance_type_column;
1776: if g_debug then
1777: if length(l_from_component) <= MAX_TRACE_SIZE then
1778: hr_utility.trace(l_from_component);
1779: end if;
1780: end if;
1781: l_from_clause := l_from_clause||l_from_component;
1782: --
1786: if (p_balance_type_column is null) then
1787: --
1788: if g_debug then
1789: if length(l_from_clause) <= MAX_TRACE_SIZE then
1790: hr_utility.trace(l_from_clause);
1791: end if;
1792: end if;
1793: --
1794: -- Now put the full statement in the from clause
1800: --
1801: --
1802: if g_debug then
1803: if length(l_from_clause) <= MAX_TRACE_SIZE then
1804: hr_utility.trace(l_from_clause);
1805: end if;
1806: end if;
1807: --
1808: -- Now put the full statement in the from clause
1810: --
1811: l_from_component := ' and '||p_balance_type_column||' = :U1';
1812: if g_debug then
1813: if length(l_from_component) <= MAX_TRACE_SIZE then
1814: hr_utility.trace(l_from_component);
1815: end if;
1816: end if;
1817: l_from_clause := l_from_clause||l_from_component;
1818: --
1819: if (p_decode_required = TRUE) then
1820: l_from_component := ' + decode(TARGET.input_value_id, 0, 0, 0)';
1821: if g_debug then
1822: if length(l_from_component) <= MAX_TRACE_SIZE then
1823: hr_utility.trace(l_from_component);
1824: end if;
1825: end if;
1826: l_from_clause := l_from_clause||l_from_component;
1827: end if;
1904: -- 6. Get the sql value (providing there are rows returned)
1905: -- 7. Close the dynamic sql cursor
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
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;
1915: dbms_sql.parse(sql_cursor, p_statement, dbms_sql.v7); -- step 2
1916: --
1917: -- -- step 3
1919: -- variables, starting with the contexts (B values).
1920: --
1921: for c1rec in ro_context (p_route_id) loop
1922: if g_debug then
1923: hr_utility.set_location ('pay_balance_pkg.process_balance_statement', 45);
1924: end if;
1925: l_count := 0;
1926: l_context_found := FALSE;
1927: while (l_count < no_rows_con_tab) loop
1926: l_context_found := FALSE;
1927: while (l_count < no_rows_con_tab) loop
1928: if (con_id_tab(l_count) = c1rec.context_id) then
1929: if g_debug then
1930: hr_utility.trace (con_name_tab (l_count) ||' = '||
1931: con_value_tab (l_count));
1932: end if;
1933: --
1934: -- Found a bind variable
1934: -- Found a bind variable
1935: --
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),
1942: con_name_tab(l_count),
1943: fnd_date.canonical_to_date(ltrim(rtrim(con_value_tab (l_count)))));
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),
1960: -- Raise an error, as there are contexts that have not been set up in
1961: -- the pl/sql tables that are required by the route.
1962: --
1963: if g_debug then
1964: hr_utility.set_location ('pay_balance_pkg.process_balance_statement', 60);
1965: end if;
1966: select context_name
1967: into l_context_name
1968: from ff_contexts
1967: into l_context_name
1968: from ff_contexts
1969: where context_id = c1rec.context_id;
1970: --
1971: hr_utility.set_message(801, 'HR_7271_PAY_CONTEXT_MISSING');
1972: hr_utility.set_message_token ('CONTEXT_NAME', l_context_name);
1973: hr_utility.raise_error;
1974: end if;
1975: end loop; -- end of for c1rec loop
1968: from ff_contexts
1969: where context_id = c1rec.context_id;
1970: --
1971: hr_utility.set_message(801, 'HR_7271_PAY_CONTEXT_MISSING');
1972: hr_utility.set_message_token ('CONTEXT_NAME', l_context_name);
1973: hr_utility.raise_error;
1974: end if;
1975: end loop; -- end of for c1rec loop
1976: --
1969: where context_id = c1rec.context_id;
1970: --
1971: hr_utility.set_message(801, 'HR_7271_PAY_CONTEXT_MISSING');
1972: hr_utility.set_message_token ('CONTEXT_NAME', l_context_name);
1973: hr_utility.raise_error;
1974: end if;
1975: end loop; -- end of for c1rec loop
1976: --
1977: -- Now bind the where clause fillers (the 'U' values)
1977: -- Now bind the where clause fillers (the 'U' values)
1978: --
1979: if (p_batch_mode = FALSE) then
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);
1978: --
1979: if (p_batch_mode = FALSE) then
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;
1990: p_jur_lvl);
1991: end if;
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);
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);
2003: end if;
2004: ignore := dbms_sql.execute(sql_cursor);
2005: --
2006: l_retrieve := TRUE;
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;
2015: --
2016: if (l_rows > 0) then
2017: --
2015: --
2016: if (l_rows > 0) then
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);
2028: if (p_balance_list(cnt).balance_type_id = l_balance_type_id) then
2029: p_balance_list(cnt).balance_value :=
2030: fnd_number.canonical_to_number(l_db_output);
2031: if g_debug then
2032: hr_utility.trace('****Bal = '||l_balance_type_id||' Value = '||
2033: l_db_output);
2034: end if;
2035: end if;
2036: end loop;
2039: --
2040: -- Have we already got a value
2041: --
2042: if g_debug then
2043: hr_utility.set_location ('pay_balance_pkg.process_balance_statement', 87);
2044: end if;
2045: if (l_value_retrieved = TRUE) then
2046: if g_debug then
2047: hr_utility.set_location ('pay_balance_pkg.process_balance_statement', 90);
2043: hr_utility.set_location ('pay_balance_pkg.process_balance_statement', 87);
2044: end if;
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;
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
2054: hr_utility.trace('Value = '||l_db_output);
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
2054: hr_utility.trace('Value = '||l_db_output);
2055: end if;
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
2054: hr_utility.trace('Value = '||l_db_output);
2055: end if;
2056: p_balance_value := fnd_number.canonical_to_number(l_db_output);
2057: --
2058: end if;
2081: --
2082: When Others Then
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);
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);
2092: End If;
2093: hr_utility.set_message(801, 'HR_7276_PAY_FAILED_DB_ITEM');
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;
2096: --
2097: end process_balance_statement;
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;
2096: --
2097: end process_balance_statement;
2098: --
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;
2096: --
2097: end process_balance_statement;
2098: --
2099: --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
2154: l_start_ptr number;
2155: --
2156: begin
2157: if g_debug then
2158: hr_utility.set_location ('pay_balance_pkg.run_rr_route', 1);
2159: end if;
2160: --
2161: -- If we are in batch mode then initialise the returns
2162: --
2199: -- Raise an error, as there are contexts that have not been set up in
2200: -- the pl/sql tables that are required by the route.
2201: --
2202: if g_debug then
2203: hr_utility.set_location ('pay_balance_pkg.run_rr_route', 10);
2204: end if;
2205: select context_name
2206: into l_context_name
2207: from ff_contexts
2206: into l_context_name
2207: from ff_contexts
2208: where context_id = c1rec.context_id;
2209: --
2210: hr_utility.set_message(801, 'HR_7271_PAY_CONTEXT_MISSING');
2211: hr_utility.set_message_token ('CONTEXT_NAME', l_context_name);
2212: hr_utility.raise_error;
2213: end if;
2214: end loop; -- end of for c1rec loop
2207: from ff_contexts
2208: where context_id = c1rec.context_id;
2209: --
2210: hr_utility.set_message(801, 'HR_7271_PAY_CONTEXT_MISSING');
2211: hr_utility.set_message_token ('CONTEXT_NAME', l_context_name);
2212: hr_utility.raise_error;
2213: end if;
2214: end loop; -- end of for c1rec loop
2215: --
2208: where context_id = c1rec.context_id;
2209: --
2210: hr_utility.set_message(801, 'HR_7271_PAY_CONTEXT_MISSING');
2211: hr_utility.set_message_token ('CONTEXT_NAME', l_context_name);
2212: hr_utility.raise_error;
2213: end if;
2214: end loop; -- end of for c1rec loop
2215: --
2216: -- The following loop searches through and replaces all where clause fillers
2225: --
2226: -- Print the route text out for debug purposes. Normally keep this line
2227: -- commented out, as for a large route it causes an error in the trace
2228: -- utility.
2229: -- hr_utility.trace (l_from_clause);
2230: --
2231: --
2232: -- Get action Parameter.
2233: --
2233: --
2234: if (cached = FALSE) then
2235: cached := TRUE;
2236: if g_debug then
2237: hr_utility.set_location('pay_balance_pkg.run_rr_route', 20);
2238: end if;
2239: l_ora_db_vers := get_oracle_db_version;
2240: if (nvl(l_ora_db_vers, 0) < 9.0) then
2241: g_low_volume := 'Y';
2390: l_par_num number;
2391: --
2392: BEGIN
2393: if g_debug then
2394: hr_utility.set_location ('pay_balance_pkg.get_run_balance', 1);
2395: hr_utility.trace ('User name: '|| p_user_name);
2396: hr_utility.trace ('bus_grp: ' || to_char (p_business_group_id));
2397: hr_utility.trace ('leg_code: ' || p_legislation_code);
2398: end if;
2391: --
2392: BEGIN
2393: if g_debug then
2394: hr_utility.set_location ('pay_balance_pkg.get_run_balance', 1);
2395: hr_utility.trace ('User name: '|| p_user_name);
2396: hr_utility.trace ('bus_grp: ' || to_char (p_business_group_id));
2397: hr_utility.trace ('leg_code: ' || p_legislation_code);
2398: end if;
2399: --
2392: BEGIN
2393: if g_debug then
2394: hr_utility.set_location ('pay_balance_pkg.get_run_balance', 1);
2395: hr_utility.trace ('User name: '|| p_user_name);
2396: hr_utility.trace ('bus_grp: ' || to_char (p_business_group_id));
2397: hr_utility.trace ('leg_code: ' || p_legislation_code);
2398: end if;
2399: --
2400: -- Get all the data to build up the route
2393: if g_debug then
2394: hr_utility.set_location ('pay_balance_pkg.get_run_balance', 1);
2395: hr_utility.trace ('User name: '|| p_user_name);
2396: hr_utility.trace ('bus_grp: ' || to_char (p_business_group_id));
2397: hr_utility.trace ('leg_code: ' || p_legislation_code);
2398: end if;
2399: --
2400: -- Get all the data to build up the route
2401: -- select definition and data type are hard coded for now
2453: -- Raise an error, as there are contexts that have not been set up in
2454: -- the pl/sql tables that are required by the route.
2455: --
2456: if g_debug then
2457: hr_utility.set_location ('pay_balance_pkg.get_run_balance', 10);
2458: end if;
2459: select context_name
2460: into l_context_name
2461: from ff_contexts
2460: into l_context_name
2461: from ff_contexts
2462: where context_id = c1rec.context_id;
2463: --
2464: hr_utility.set_message(801, 'HR_7271_PAY_CONTEXT_MISSING');
2465: hr_utility.set_message_token ('CONTEXT_NAME', l_context_name);
2466: hr_utility.raise_error;
2467: end if;
2468: end loop; -- end of for c1rec loop
2461: from ff_contexts
2462: where context_id = c1rec.context_id;
2463: --
2464: hr_utility.set_message(801, 'HR_7271_PAY_CONTEXT_MISSING');
2465: hr_utility.set_message_token ('CONTEXT_NAME', l_context_name);
2466: hr_utility.raise_error;
2467: end if;
2468: end loop; -- end of for c1rec loop
2469: --
2462: where context_id = c1rec.context_id;
2463: --
2464: hr_utility.set_message(801, 'HR_7271_PAY_CONTEXT_MISSING');
2465: hr_utility.set_message_token ('CONTEXT_NAME', l_context_name);
2466: hr_utility.raise_error;
2467: end if;
2468: end loop; -- end of for c1rec loop
2469: --
2470: -- The following loop searches through and replaces all where clause fillers
2485: --
2486: -- Print the route text out for debug purposes. Normally keep this line
2487: -- commented out, as for a large route it causes an error in the trace
2488: -- utility.
2489: -- hr_utility.trace (l_text);
2490: --
2491: --
2492: -- now build up the full SQL statement:
2493: --
2504: ' */ NVL(SUM(prb.balance_value),0) FROM ' || l_text;
2505: end if;
2506: --
2507: if g_debug then
2508: hr_utility.set_location('pay_balance_pkg.get_run_balance', 15);
2509: end if;
2510: --
2511: elsif p_route_type = 'RR' then
2512: --
2512: --
2513: -- Should never get here since all RRs are handled by run_rr_route now.
2514: --
2515: if g_debug then
2516: hr_utility.set_location('pay_balance_pkg.get_run_balance', 20);
2517: end if;
2518: --
2519: hr_general.assert_condition(false);
2520: --
2532: -- 6. Get the sql value (providing there are rows returned)
2533: -- 7. Close the dynamic sql cursor
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
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;
2543: dbms_sql.parse(sql_cursor, l_text, dbms_sql.v7); -- step 2
2544: --
2545: -- -- step 3
2547: -- variables, starting with the contexts (B values).
2548: --
2549: for l_cxt_num in 1..route_contexts_cache.sz loop
2550: if g_debug then
2551: hr_utility.set_location ('pay_balance_pkg.get_run_balance', 45);
2552: end if;
2553: l_count := 0;
2554: l_context_found := FALSE;
2555: while (l_count < no_rows_con_tab) loop
2554: l_context_found := FALSE;
2555: while (l_count < no_rows_con_tab) loop
2556: if (con_id_tab(l_count) = route_contexts_cache.cxt_id(l_cxt_num)) then
2557: if g_debug then
2558: hr_utility.trace (con_name_tab (l_count) ||' = '||
2559: con_value_tab (l_count));
2560: end if;
2561: --
2562: -- Found a bind variable
2562: -- Found a bind variable
2563: --
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),
2570: con_name_tab(l_count),
2571: fnd_date.canonical_to_date(ltrim(rtrim(con_value_tab (l_count)))));
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),
2588: -- Raise an error, as there are contexts that have not been set up in
2589: -- the pl/sql tables that are required by the route.
2590: --
2591: if g_debug then
2592: hr_utility.set_location ('pay_balance_pkg.get_run_balance', 60);
2593: end if;
2594: select context_name
2595: into l_context_name
2596: from ff_contexts
2595: into l_context_name
2596: from ff_contexts
2597: where context_id = route_contexts_cache.cxt_id(l_cxt_num);
2598: --
2599: hr_utility.set_message(801, 'HR_7271_PAY_CONTEXT_MISSING');
2600: hr_utility.set_message_token ('CONTEXT_NAME', l_context_name);
2601: hr_utility.raise_error;
2602: end if;
2603: end loop; -- end of for route context loop
2596: from ff_contexts
2597: where context_id = route_contexts_cache.cxt_id(l_cxt_num);
2598: --
2599: hr_utility.set_message(801, 'HR_7271_PAY_CONTEXT_MISSING');
2600: hr_utility.set_message_token ('CONTEXT_NAME', l_context_name);
2601: hr_utility.raise_error;
2602: end if;
2603: end loop; -- end of for route context loop
2604: --
2597: where context_id = route_contexts_cache.cxt_id(l_cxt_num);
2598: --
2599: hr_utility.set_message(801, 'HR_7271_PAY_CONTEXT_MISSING');
2600: hr_utility.set_message_token ('CONTEXT_NAME', l_context_name);
2601: hr_utility.raise_error;
2602: end if;
2603: end loop; -- end of for route context loop
2604: --
2605: -- Now bind the where clause fillers (the 'U' values)
2605: -- Now bind the where clause fillers (the 'U' values)
2606: --
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;
2612: route_parameters_cache.par_val(l_par_num));
2613: end loop;
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
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;
2623: l_rows := dbms_sql.execute_and_fetch (sql_cursor, false); -- step 5
2624: --
2625: if (l_rows = 1) then
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);
2628: end if;
2629: dbms_sql.column_value (sql_cursor, 1, p_db_output); -- step 6
2630: --
2631: if g_debug then
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;
2634: dbms_sql.close_cursor(sql_cursor); -- step 7
2635: --
2636: if g_debug then
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;
2639: --
2640: elsif (l_rows = 0) then
2641: dbms_sql.close_cursor(sql_cursor);
2643: --
2644: -- its ok to not find a row
2645: --
2646: if g_debug then
2647: hr_utility.set_location ('pay_balance_pkg.get_run_balance', 85);
2648: end if;
2649: p_db_output := null;
2650: else
2651: --
2651: --
2652: -- Error, no rows found when the entity has been defined to always
2653: -- find a row.
2654: --
2655: hr_utility.set_message(801, 'HR_7272_PAY_NO_ROWS_RETURNED');
2656: hr_utility.set_message_token ('USER_ENTITY_NAME', p_user_name);
2657: hr_utility.raise_error;
2658: end if;
2659: else
2652: -- Error, no rows found when the entity has been defined to always
2653: -- find a row.
2654: --
2655: hr_utility.set_message(801, 'HR_7272_PAY_NO_ROWS_RETURNED');
2656: hr_utility.set_message_token ('USER_ENTITY_NAME', p_user_name);
2657: hr_utility.raise_error;
2658: end if;
2659: else
2660: --
2653: -- find a row.
2654: --
2655: hr_utility.set_message(801, 'HR_7272_PAY_NO_ROWS_RETURNED');
2656: hr_utility.set_message_token ('USER_ENTITY_NAME', p_user_name);
2657: hr_utility.raise_error;
2658: end if;
2659: else
2660: --
2661: -- More than 1 row have been returned. We must error as DB items can
2661: -- More than 1 row have been returned. We must error as DB items can
2662: -- only return 1 row.
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;
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;
2672: exception
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;
2672: exception
2673: --
2677: --
2678: When Others Then
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);
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);
2688: End If;
2689: hr_utility.set_message(801, 'HR_7276_PAY_FAILED_DB_ITEM');
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;
2692: --
2693: end get_run_balance;
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;
2692: --
2693: end get_run_balance;
2694: ---------------------------------------------------------------------------
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;
2692: --
2693: end get_run_balance;
2694: ---------------------------------------------------------------------------
2695: -- function run_db_item
2789: l_jur_lvl pay_balance_types.jurisdiction_level%type;
2790: --
2791: BEGIN
2792: if g_debug then
2793: hr_utility.set_location('Entering pay_balance_pkg.get_run_bal', 10);
2794: end if;
2795: --
2796: if (p_route_type = 'RR') then
2797: --
2795: --
2796: if (p_route_type = 'RR') then
2797: --
2798: if g_debug then
2799: hr_utility.set_location('Entering pay_balance_pkg.get_run_bal', 15);
2800: end if;
2801: select pdb.balance_type_id,
2802: pdr.route_id,
2803: pdr.balance_type_column,
2841: fetch get_vals into l_user_name, l_business_group_id, l_legislation_code;
2842: close get_vals;
2843: --
2844: if g_debug then
2845: hr_utility.set_location('pay_balance_pkg.get_run_bal', 20);
2846: hr_utility.trace('l_user_entity_name is: '||l_user_name);
2847: end if;
2848: l_balance_val := get_run_balance(l_user_name
2849: ,l_business_group_id
2842: close get_vals;
2843: --
2844: if g_debug then
2845: hr_utility.set_location('pay_balance_pkg.get_run_bal', 20);
2846: hr_utility.trace('l_user_entity_name is: '||l_user_name);
2847: end if;
2848: l_balance_val := get_run_balance(l_user_name
2849: ,l_business_group_id
2850: ,l_legislation_code
2850: ,l_legislation_code
2851: ,p_route_type);
2852: end if;
2853: if g_debug then
2854: hr_utility.trace('Val = '||l_balance_val);
2855: end if;
2856: return l_balance_val;
2857: --
2858: END get_run_balance;
2879: l_defbal_rec t_def_bal_rec;
2880: --
2881: begin
2882: if g_debug then
2883: hr_utility.set_location ('Entering: pay_balance_pkg.get_rr_value', 5);
2884: end if;
2885: --
2886: -- First load the cache
2887: load_defbal_cache(p_defined_balance_id,
2888: l_defbal_rec);
2889: --
2890: if l_defbal_rec.rr_ptr is not null then
2891: if g_debug then
2892: hr_utility.set_location ('Entering: pay_balance_pkg.get_rr_value', 10);
2893: end if;
2894: l_balance_value := get_run_balance(p_defined_balance_id
2895: ,g_dimrou_cache(l_defbal_rec.rr_ptr).priority
2896: ,g_dimrou_cache(l_defbal_rec.rr_ptr).route_type);
2896: ,g_dimrou_cache(l_defbal_rec.rr_ptr).route_type);
2897: else
2898: --
2899: if g_debug then
2900: hr_utility.set_location ('Entering: pay_balance_pkg.get_rr_value', 20);
2901: end if;
2902: if (l_defbal_rec.start_rb_ptr is not null) then
2903: hr_general.assert_condition(false);
2904: else
2902: if (l_defbal_rec.start_rb_ptr is not null) then
2903: hr_general.assert_condition(false);
2904: else
2905: if g_debug then
2906: hr_utility.set_location ('Entering: pay_balance_pkg.get_rr_value', 30);
2907: end if;
2908: l_balance_value := fnd_number.canonical_to_number(run_db_item(p_defined_balance_id));
2909: end if;
2910: end if;
2912: -- Setup the return values
2913: --
2914: p_balance_value := l_balance_value;
2915: if g_debug then
2916: hr_utility.set_location ('Leaving: pay_balance_pkg.get_rr_value', 45);
2917: end if;
2918: --
2919: end get_rr_value;
2920: --
3072: l_position number;
3073: --
3074: begin
3075: if g_debug then
3076: hr_utility.set_location ('Entering: pay_balance_pkg.get_rb_value', 5);
3077: end if;
3078: --
3079: -- First load the cache
3080: load_defbal_cache(p_defined_balance_id,
3095: p_asg_action_id,
3096: l_valid);
3097: --
3098: if g_debug then
3099: hr_utility.set_location ('pay_balance_pkg.get_rb_value', 20);
3100: end if;
3101: --
3102: -- now know that run balance exists and is valid, so call run_db_item to
3103: -- return the balance_value.
3103: -- return the balance_value.
3104: --
3105: if l_valid = 'V' then
3106: if g_debug then
3107: hr_utility.set_location ('pay_balance_pkg.get_rb_value', 25);
3108: end if;
3109: l_balance_value := get_run_balance(p_defined_balance_id
3110: ,g_dimrou_cache(l_position).priority
3111: ,g_dimrou_cache(l_position).route_type);
3117: end loop;
3118: end if;
3119: --
3120: if g_debug then
3121: hr_utility.set_location ('pay_balance_pkg.get_rb_value', 35);
3122: end if;
3123: --
3124: --
3125: -- Setup the return values
3126: --
3127: p_found := l_balval_found;
3128: p_balance_value := l_balance_value;
3129: if g_debug then
3130: hr_utility.set_location ('Leaving: pay_balance_pkg.get_rb_value', 45);
3131: end if;
3132: --
3133: end get_rb_value;
3134: --
3178: l_error_text varchar2(200); -- used for sql error messages
3179: l_value_found number;
3180: begin
3181: if g_debug then
3182: hr_utility.set_location ('pay_balance_pkg.check_bal_expiry_internal', 1);
3183: hr_utility.trace ('Expiry check level = ' || p_expiry_checking_level);
3184: hr_utility.trace ('Expiry checking code = ' || p_expiry_checking_code);
3185: end if;
3186: if (p_expiry_checking_level = 'N') then
3179: l_value_found number;
3180: begin
3181: if g_debug then
3182: hr_utility.set_location ('pay_balance_pkg.check_bal_expiry_internal', 1);
3183: hr_utility.trace ('Expiry check level = ' || p_expiry_checking_level);
3184: hr_utility.trace ('Expiry checking code = ' || p_expiry_checking_code);
3185: end if;
3186: if (p_expiry_checking_level = 'N') then
3187: --
3180: begin
3181: if g_debug then
3182: hr_utility.set_location ('pay_balance_pkg.check_bal_expiry_internal', 1);
3183: hr_utility.trace ('Expiry check level = ' || p_expiry_checking_level);
3184: hr_utility.trace ('Expiry checking code = ' || p_expiry_checking_code);
3185: end if;
3186: if (p_expiry_checking_level = 'N') then
3187: --
3188: -- There is no expiry check code to run, so balance can't expire
3193: --
3194: -- get the payroll_action_id for the balance (ie. owner):
3195: --
3196: if g_debug then
3197: hr_utility.set_location ('pay_balance_pkg.check_bal_expiry_internal', 10);
3198: end if;
3199: select PAY.payroll_action_id,
3200: PAY.effective_date
3201: into l_bal_owner_pay_action,
3207: --
3208: -- get the actual payroll information for this assignment action:
3209: --
3210: if g_debug then
3211: hr_utility.set_location ('pay_balance_pkg.check_bal_expiry_internal', 15);
3212: end if;
3213: select PAY.payroll_action_id,
3214: PAY.effective_date
3215: into l_payroll_action,
3274: -- 5. Get the variable value (providing there are rows returned)
3275: -- 6. Close the dynamic sql cursor
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
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;
3285: dbms_sql.parse(sql_cursor, l_expiry_chk_str, dbms_sql.v7); -- step 2
3286: --
3287: if g_debug then
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;
3290: dbms_sql.bind_variable(sql_cursor, 'l_bal_owner_pay_action', -- step 3:
3291: l_bal_owner_pay_action);
3292: --
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);
3321: end if;
3322: l_rows := dbms_sql.execute (sql_cursor); -- step 4
3323: --
3324: if (l_rows = 1) then
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);
3327: end if;
3328: dbms_sql.variable_value(sql_cursor, 'l_expiry_information', -- step 5
3329: l_expiry_information);
3330: --
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);
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);
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
3341: --
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
3341: --
3342: -- More than 1 row has been returned. We must error as package call can
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
3341: --
3342: -- More than 1 row has been returned. We must error as package call can
3343: -- only return 1 row, so this condition should never occur !
3342: -- More than 1 row has been returned. We must error as package call can
3343: -- only return 1 row, so this condition should never occur !
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;
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
3353: -- with the expiry information in the value 'l_expiry_information'.
3357: -- payroll action.
3358: --
3359: if (p_expiry_checking_level in ('A', 'P', 'E')) then
3360: if g_debug then
3361: hr_utility.set_location ('pay_balance_pkg.check_bal_expiry_internal', 50);
3362: end if;
3363: p_balance_expired := l_expiry_information;
3364: else -- date level
3365: if g_debug then
3362: end if;
3363: p_balance_expired := l_expiry_information;
3364: else -- date level
3365: if g_debug then
3366: hr_utility.set_location ('pay_balance_pkg.check_bal_expiry_internal', 55);
3367: end if;
3368: l_jul_effect_date := to_number (to_char (l_effective_date, 'J'));
3369: --
3370: if (l_expiry_information < l_jul_effect_date) then
3384: t_dim_nm(t_dim_nm.count + 1) := p_dimension_name;
3385: t_expiry(t_expiry.count + 1) := p_balance_expired;
3386: --
3387: if g_debug then
3388: hr_utility.trace('cached own pay action: '||
3389: to_char(t_own_pay_action(t_own_pay_action.count)));
3390: hr_utility.trace('cached usr pay action: '||
3391: to_char(t_usr_pay_action(t_usr_pay_action.count)));
3392: hr_utility.trace('cached dim name: '||t_dim_nm(t_dim_nm.count));
3386: --
3387: if g_debug then
3388: hr_utility.trace('cached own pay action: '||
3389: to_char(t_own_pay_action(t_own_pay_action.count)));
3390: hr_utility.trace('cached usr pay action: '||
3391: to_char(t_usr_pay_action(t_usr_pay_action.count)));
3392: hr_utility.trace('cached dim name: '||t_dim_nm(t_dim_nm.count));
3393: hr_utility.trace('cached bal expired: '||t_expiry(t_expiry.count));
3394: end if;
3388: hr_utility.trace('cached own pay action: '||
3389: to_char(t_own_pay_action(t_own_pay_action.count)));
3390: hr_utility.trace('cached usr pay action: '||
3391: to_char(t_usr_pay_action(t_usr_pay_action.count)));
3392: hr_utility.trace('cached dim name: '||t_dim_nm(t_dim_nm.count));
3393: hr_utility.trace('cached bal expired: '||t_expiry(t_expiry.count));
3394: end if;
3395: --
3396: end if;
3389: to_char(t_own_pay_action(t_own_pay_action.count)));
3390: hr_utility.trace('cached usr pay action: '||
3391: to_char(t_usr_pay_action(t_usr_pay_action.count)));
3392: hr_utility.trace('cached dim name: '||t_dim_nm(t_dim_nm.count));
3393: hr_utility.trace('cached bal expired: '||t_expiry(t_expiry.count));
3394: end if;
3395: --
3396: end if;
3397: --
3409: --
3410: When Others Then
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);
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);
3420: End If;
3421: hr_utility.set_message(801, 'HR_7275_PAY_FAILED_IN_EXPIRY');
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;
3424: end check_bal_expiry_internal;
3425: --
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;
3424: end check_bal_expiry_internal;
3425: --
3426: --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
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;
3424: end check_bal_expiry_internal;
3425: --
3426: --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
3427: -- +
3455: p_balance_expired boolean;
3456: l_exp_code number;
3457: --
3458: begin
3459: g_debug := hr_utility.debug_enabled;
3460: --
3461: l_exp_code := check_bal_expiry_internal(p_bal_owner_asg_action,
3462: p_assignment_action_id,
3463: p_dimension_name,
3611: while (l_count < no_rows_con_tab) loop
3612: if (con_name_tab (l_count) = 'JURISDICTION_CODE') then
3613: --
3614: if g_debug then
3615: hr_utility.set_location ('pay_balance_pkg.get_old_latest_bal_value', 15)
3616: ;
3617: end if;
3618: loc_value_tab (l_count) := substr (loc_value_tab(l_count), 1,
3619: p_defbal_rec.jurisdiction_lvl);
3617: end if;
3618: loc_value_tab (l_count) := substr (loc_value_tab(l_count), 1,
3619: p_defbal_rec.jurisdiction_lvl);
3620: if g_debug then
3621: hr_utility.trace ('substr jur. code = ' || loc_value_tab (l_count));
3622: end if;
3623: exit; -- exit while loop
3624: end if;
3625: l_count := l_count + 1;
3638: END IF;
3639: --
3640: LOOP
3641: if g_debug then
3642: hr_utility.set_location ('pay_balance_pkg.get_old_latest_bal_value', 20);
3643: end if;
3644: --
3645: IF p_defbal_rec.dimension_type = 'A' THEN
3646: FETCH bal_contexts_asg INTO l_latest_balance_id,
3672: l_bal_con_str := null;
3673: l_cursor_executed := FALSE;
3674: for c2rec in bal_context_values (l_latest_balance_id) loop
3675: if g_debug then
3676: hr_utility.set_location ('pay_balance_pkg.get_old_latest_bal_value', 25);
3677: end if;
3678: --
3679: -- Try to find the context:
3680: --
3681: l_fnd_con_and_val := FALSE;
3682: l_cursor_executed := TRUE;
3683: l_count := 0;
3684: if g_debug then
3685: hr_utility.trace ('Trying to match ' || c2rec.context_name);
3686: end if;
3687: --
3688: while (l_count < no_rows_con_tab) loop
3689: if ((con_id_tab (l_count) = c2rec.context_id) AND
3691: --
3692: -- found a matching context name AND value
3693: --
3694: if g_debug then
3695: hr_utility.trace ('match found');
3696: end if;
3697: l_fnd_con_and_val := TRUE;
3698: --
3699: -- Since we have the context name and value at this point, build
3722: -- Failed to find a matching context name and value, so clear
3723: -- flags and exit inner context cursor loop (c2rec)
3724: --
3725: if g_debug then
3726: hr_utility.trace ('match failed');
3727: end if;
3728: l_found_all_contexts := FALSE;
3729: exit; -- exit c2rec
3730: end if;
3740: -- Else carry on to the next latest_balance_id.
3741: --
3742: if ((l_found_all_contexts = TRUE) OR (l_cursor_executed = FALSE)) then
3743: if g_debug then
3744: hr_utility.set_location ('pay_balance_pkg.get_old_latest_bal_value', 35);
3745: end if;
3746: l_balance_found := TRUE;
3747: exit; -- exit c1rec
3748: end if;
3756: --
3757: -- If we could not find a latest balance, then derive from ff_routes
3758: --
3759: if g_debug then
3760: hr_utility.set_location ('pay_balance_pkg.get_old_latest_bal_value', 37);
3761: end if;
3762: if (l_balance_found = TRUE) then
3763: if g_debug then
3764: hr_utility.set_location ('pay_balance_pkg.get_old_latest_bal_value', 38);
3760: hr_utility.set_location ('pay_balance_pkg.get_old_latest_bal_value', 37);
3761: end if;
3762: if (l_balance_found = TRUE) then
3763: if g_debug then
3764: hr_utility.set_location ('pay_balance_pkg.get_old_latest_bal_value', 38);
3765: hr_utility.trace('OWN SEQ '||l_bal_owner_act_seq);
3766: hr_utility.trace('CURR SEQ '||l_bal_asg_act_seq);
3767: end if;
3768: --
3761: end if;
3762: if (l_balance_found = TRUE) then
3763: if g_debug then
3764: hr_utility.set_location ('pay_balance_pkg.get_old_latest_bal_value', 38);
3765: hr_utility.trace('OWN SEQ '||l_bal_owner_act_seq);
3766: hr_utility.trace('CURR SEQ '||l_bal_asg_act_seq);
3767: end if;
3768: --
3769: -- Check that the current assignment_action is the same as the
3762: if (l_balance_found = TRUE) then
3763: if g_debug then
3764: hr_utility.set_location ('pay_balance_pkg.get_old_latest_bal_value', 38);
3765: hr_utility.trace('OWN SEQ '||l_bal_owner_act_seq);
3766: hr_utility.trace('CURR SEQ '||l_bal_asg_act_seq);
3767: end if;
3768: --
3769: -- Check that the current assignment_action is the same as the
3770: -- owner action, if so there is no need for expiry checking.
3792: -- O.K. We cannot use the latest balance value, now check if the
3793: -- expired details can by used.
3794: --
3795: if g_debug then
3796: hr_utility.set_location ('pay_balance_pkg.get_old_latest_bal_value', 140
3797: );
3798: end if;
3799: select count(*)
3800: into l_num_of_runs
3816: -- in the specified period.
3817: --
3818: if l_num_of_runs = 1 then
3819: if g_debug then
3820: hr_utility.set_location ('pay_balance_pkg.get_old_latest_bal_value',
3821: 150);
3822: end if;
3823: l_bal_owner_asg_action := l_bal_expired_action;
3824: l_balance_value := l_bal_expired_value;
3825: l_expiry_needed := TRUE;
3826: else
3827: -- check if can use previous balance value
3828: if g_debug then
3829: hr_utility.set_location ('pay_balance_pkg.get_old_latest_bal_value',
3830: 160);
3831: end if;
3832: select count(*)
3833: into l_num_of_runs
3843: and l_bal_asg_act_seq;
3844: --
3845: if l_num_of_runs = 1 then
3846: if g_debug then
3847: hr_utility.set_location ('pay_balance_pkg.get_old_latest_bal_value'
3848: , 170);
3849: end if;
3850: l_bal_owner_asg_action := l_prev_action;
3851: l_balance_value := l_prev_value;
3863: end if;
3864: --
3865: end if;
3866: if g_debug then
3867: hr_utility.set_location ('pay_balance_pkg.get_old_latest_bal_value', 55);
3868: end if;
3869: --
3870: if (l_expiry_needed = TRUE) then
3871: --
3889: --
3890: -- Yep its a special case.
3891: if (l_balance_expired = BALANCE_EXPIRED) then
3892: if g_debug then
3893: hr_utility.trace ('Balance expired');
3894: end if;
3895: l_balance_value := 0;
3896: l_found := TRUE;
3897: elsif (l_balance_expired = BALANCE_NOT_EXPIRED) then
3895: l_balance_value := 0;
3896: l_found := TRUE;
3897: elsif (l_balance_expired = BALANCE_NOT_EXPIRED) then
3898: if g_debug then
3899: hr_utility.trace ('Balance not expired');
3900: end if;
3901: l_found := TRUE;
3902: else
3903: --
3912:
3913: else
3914: if (l_balance_expired = BALANCE_EXPIRED) then
3915: if g_debug then
3916: hr_utility.trace ('Balance expired');
3917: end if;
3918: l_balance_value := 0;
3919: l_found := TRUE;
3920: else
3918: l_balance_value := 0;
3919: l_found := TRUE;
3920: else
3921: if g_debug then
3922: hr_utility.trace ('Balance not expired');
3923: end if;
3924: l_found := TRUE;
3925: end if; --BALANCE_EXPIRED
3926: end if; --p_defbal_rec.expiry_check_lvl = 'E'
4066: --
4067: l_cnt := l_cnt + 1;
4068: --
4069: if g_debug then
4070: hr_utility.set_location ('pay_balance_pkg.get_new_latest_bal_value', 38);
4071: hr_utility.trace('OWN SEQ '||lbrec.owner_action_seq);
4072: hr_utility.trace('CURR SEQ '||lbrec.current_action_seq);
4073: end if;
4074: --
4067: l_cnt := l_cnt + 1;
4068: --
4069: if g_debug then
4070: hr_utility.set_location ('pay_balance_pkg.get_new_latest_bal_value', 38);
4071: hr_utility.trace('OWN SEQ '||lbrec.owner_action_seq);
4072: hr_utility.trace('CURR SEQ '||lbrec.current_action_seq);
4073: end if;
4074: --
4075: -- Check that the current assignment_action is the same as the
4068: --
4069: if g_debug then
4070: hr_utility.set_location ('pay_balance_pkg.get_new_latest_bal_value', 38);
4071: hr_utility.trace('OWN SEQ '||lbrec.owner_action_seq);
4072: hr_utility.trace('CURR SEQ '||lbrec.current_action_seq);
4073: end if;
4074: --
4075: -- Check that the current assignment_action is the same as the
4076: -- owner action, if so there is no need for expiry checking.
4099: -- O.K. We cannot use the latest balance value, now check if the
4100: -- expired details can by used.
4101: --
4102: if g_debug then
4103: hr_utility.set_location ('pay_balance_pkg.get_new_latest_bal_value', 140
4104: );
4105: end if;
4106: select count(*)
4107: into l_num_of_runs
4123: -- in the specified period.
4124: --
4125: if l_num_of_runs = 1 then
4126: if g_debug then
4127: hr_utility.set_location ('pay_balance_pkg.get_new_latest_bal_value',
4128: 150);
4129: end if;
4130: l_bal_owner_asg_action := lbrec.expired_assignment_action_id;
4131: l_balance_value := lbrec.expired_value;
4133: l_expiry_needed := TRUE;
4134: else
4135: -- check if can use previous balance value
4136: if g_debug then
4137: hr_utility.set_location ('pay_balance_pkg.get_new_latest_bal_value',
4138: 160);
4139: end if;
4140: select count(*)
4141: into l_num_of_runs
4151: and lbrec.current_action_seq;
4152: --
4153: if l_num_of_runs = 1 then
4154: if g_debug then
4155: hr_utility.set_location ('pay_balance_pkg.get_new_latest_bal_value'
4156: , 170);
4157: end if;
4158: l_bal_owner_asg_action := lbrec.prev_assignment_action_id;
4159: l_balance_value := lbrec.prev_balance_value;
4182: l_expiry_date := lbrec.expiry_date;
4183: --
4184: end if;
4185: if g_debug then
4186: hr_utility.set_location ('pay_balance_pkg.get_new_latest_bal_value', 55);
4187: end if;
4188: --
4189: if (l_expiry_needed = TRUE) then
4190: --
4270: --
4271: -- Yep its a special case.
4272: if (l_balance_expired = BALANCE_EXPIRED) then
4273: if g_debug then
4274: hr_utility.trace ('Balance expired');
4275: end if;
4276: l_balance_value := 0;
4277: l_found := TRUE;
4278: elsif (l_balance_expired = BALANCE_NOT_EXPIRED) then
4276: l_balance_value := 0;
4277: l_found := TRUE;
4278: elsif (l_balance_expired = BALANCE_NOT_EXPIRED) then
4279: if g_debug then
4280: hr_utility.trace ('Balance not expired');
4281: end if;
4282: l_found := TRUE;
4283: else
4284: --
4292: end if; --BALANCE_EXPIRED
4293: else
4294: if (l_balance_expired = BALANCE_EXPIRED) then
4295: if g_debug then
4296: hr_utility.trace ('Balance expired');
4297: end if;
4298: l_balance_value := 0;
4299: l_found := TRUE;
4300: else
4298: l_balance_value := 0;
4299: l_found := TRUE;
4300: else
4301: if g_debug then
4302: hr_utility.trace ('Balance not expired');
4303: end if;
4304: l_found := TRUE;
4305: end if; --BALANCE_EXPIRED
4306: end if; --p_defbal_rec.expiry_check_lvl = 'E'
4638: bal_ju_code pay_run_results.jurisdiction_code%type;
4639: con_ju_code pay_run_results.jurisdiction_code%type;
4640: --
4641: begin
4642: g_debug := hr_utility.debug_enabled;
4643: --
4644: l_cache_cnt := 1; -- The next free position
4645: --
4646: -- Create the super set of balances to be calculated.
4666: or (l_defbal_rec.bd_needed and p_context_lst(con_cnt).balance_date is null)
4667: ) then
4668: --
4669: if g_debug then
4670: hr_utility.trace('Not a valid context combination for this balance');
4671: end if;
4672: --
4673: else
4674: --
4783: start_def_bal_ptr := -999;
4784: prev_def_bal_id := -1;
4785: for cnt in 1..l_transform_cache.count loop
4786: --
4787: hr_utility.trace('Time 1 '||to_char(sysdate, 'HH24:MI:SS'));
4788: if l_transform_cache(cnt).defined_balance_id = prev_def_bal_id then
4789: --
4790: l_add_balance := TRUE;
4791: if g_debug then
4788: if l_transform_cache(cnt).defined_balance_id = prev_def_bal_id then
4789: --
4790: l_add_balance := TRUE;
4791: if g_debug then
4792: hr_utility.trace('Start index '||start_def_bal_ptr||' to '||l_batch_bal_cache.count);
4793: end if;
4794: hr_utility.trace('Time 2 '||to_char(sysdate, 'HH24:MI:SS'));
4795: for dup_cnt in start_def_bal_ptr..l_batch_bal_cache.count loop
4796: --
4790: l_add_balance := TRUE;
4791: if g_debug then
4792: hr_utility.trace('Start index '||start_def_bal_ptr||' to '||l_batch_bal_cache.count);
4793: end if;
4794: hr_utility.trace('Time 2 '||to_char(sysdate, 'HH24:MI:SS'));
4795: for dup_cnt in start_def_bal_ptr..l_batch_bal_cache.count loop
4796: --
4797: hr_utility.trace('Time 3 '||to_char(sysdate, 'HH24:MI:SS'));
4798: if g_debug then
4793: end if;
4794: hr_utility.trace('Time 2 '||to_char(sysdate, 'HH24:MI:SS'));
4795: for dup_cnt in start_def_bal_ptr..l_batch_bal_cache.count loop
4796: --
4797: hr_utility.trace('Time 3 '||to_char(sysdate, 'HH24:MI:SS'));
4798: if g_debug then
4799: hr_utility.trace('Comparing...');
4800: hr_utility.trace(l_transform_cache(cnt).defined_balance_id||' '||
4801: l_batch_bal_cache(dup_cnt).defined_balance_id);
4795: for dup_cnt in start_def_bal_ptr..l_batch_bal_cache.count loop
4796: --
4797: hr_utility.trace('Time 3 '||to_char(sysdate, 'HH24:MI:SS'));
4798: if g_debug then
4799: hr_utility.trace('Comparing...');
4800: hr_utility.trace(l_transform_cache(cnt).defined_balance_id||' '||
4801: l_batch_bal_cache(dup_cnt).defined_balance_id);
4802: hr_utility.trace(nvl(l_transform_cache(cnt).tax_unit_id, -999)||' '||
4803: nvl(l_batch_bal_cache(dup_cnt).tax_unit_id, -999));
4796: --
4797: hr_utility.trace('Time 3 '||to_char(sysdate, 'HH24:MI:SS'));
4798: if g_debug then
4799: hr_utility.trace('Comparing...');
4800: hr_utility.trace(l_transform_cache(cnt).defined_balance_id||' '||
4801: l_batch_bal_cache(dup_cnt).defined_balance_id);
4802: hr_utility.trace(nvl(l_transform_cache(cnt).tax_unit_id, -999)||' '||
4803: nvl(l_batch_bal_cache(dup_cnt).tax_unit_id, -999));
4804: hr_utility.trace(nvl(l_transform_cache(cnt).jurisdiction_code, '
4798: if g_debug then
4799: hr_utility.trace('Comparing...');
4800: hr_utility.trace(l_transform_cache(cnt).defined_balance_id||' '||
4801: l_batch_bal_cache(dup_cnt).defined_balance_id);
4802: hr_utility.trace(nvl(l_transform_cache(cnt).tax_unit_id, -999)||' '||
4803: nvl(l_batch_bal_cache(dup_cnt).tax_unit_id, -999));
4804: hr_utility.trace(nvl(l_transform_cache(cnt).jurisdiction_code, '
4805: nvl(l_batch_bal_cache(dup_cnt).jurisdiction_code, '
4806: hr_utility.trace(nvl(l_transform_cache(cnt).source_id, -999)||' '||
4800: hr_utility.trace(l_transform_cache(cnt).defined_balance_id||' '||
4801: l_batch_bal_cache(dup_cnt).defined_balance_id);
4802: hr_utility.trace(nvl(l_transform_cache(cnt).tax_unit_id, -999)||' '||
4803: nvl(l_batch_bal_cache(dup_cnt).tax_unit_id, -999));
4804: hr_utility.trace(nvl(l_transform_cache(cnt).jurisdiction_code, '
4806: hr_utility.trace(nvl(l_transform_cache(cnt).source_id, -999)||' '||
4807: nvl(l_batch_bal_cache(dup_cnt).source_id, -999));
4808: hr_utility.trace(nvl(l_transform_cache(cnt).source_text, '
4802: hr_utility.trace(nvl(l_transform_cache(cnt).tax_unit_id, -999)||' '||
4803: nvl(l_batch_bal_cache(dup_cnt).tax_unit_id, -999));
4804: hr_utility.trace(nvl(l_transform_cache(cnt).jurisdiction_code, '
4805: nvl(l_batch_bal_cache(dup_cnt).jurisdiction_code, '
4806: hr_utility.trace(nvl(l_transform_cache(cnt).source_id, -999)||' '||
4807: nvl(l_batch_bal_cache(dup_cnt).source_id, -999));
4808: hr_utility.trace(nvl(l_transform_cache(cnt).source_text, '
4809: nvl(l_batch_bal_cache(dup_cnt).source_text, '
4810: end if;
4804: hr_utility.trace(nvl(l_transform_cache(cnt).jurisdiction_code, '
4805: nvl(l_batch_bal_cache(dup_cnt).jurisdiction_code, '
4806: hr_utility.trace(nvl(l_transform_cache(cnt).source_id, -999)||' '||
4807: nvl(l_batch_bal_cache(dup_cnt).source_id, -999));
4808: hr_utility.trace(nvl(l_transform_cache(cnt).source_text, '
4810: end if;
4811: --
4812: hr_utility.trace('Time 4 '||to_char(sysdate, 'HH24:MI:SS'));
4808: hr_utility.trace(nvl(l_transform_cache(cnt).source_text, '
4809: nvl(l_batch_bal_cache(dup_cnt).source_text, '
4810: end if;
4811: --
4812: hr_utility.trace('Time 4 '||to_char(sysdate, 'HH24:MI:SS'));
4813: if (l_transform_cache(cnt).defined_balance_id = l_batch_bal_cache(dup_cnt).defined_balance_id
4814: and nvl(l_transform_cache(cnt).tax_unit_id, -999)
4815: = nvl(l_batch_bal_cache(dup_cnt).tax_unit_id, -999)
4816: and nvl(l_transform_cache(cnt).jurisdiction_code, '
4838: end if;
4839: --
4840: end loop;
4841: --
4842: hr_utility.trace('Time 5 '||to_char(sysdate, 'HH24:MI:SS'));
4843: if (l_add_balance = TRUE) then
4844: hr_utility.trace('Time 6 '||to_char(sysdate, 'HH24:MI:SS'));
4845: l_batch_bal_cache(l_cache_cnt) := l_transform_cache(cnt);
4846: l_cache_cnt := l_cache_cnt + 1;
4840: end loop;
4841: --
4842: hr_utility.trace('Time 5 '||to_char(sysdate, 'HH24:MI:SS'));
4843: if (l_add_balance = TRUE) then
4844: hr_utility.trace('Time 6 '||to_char(sysdate, 'HH24:MI:SS'));
4845: l_batch_bal_cache(l_cache_cnt) := l_transform_cache(cnt);
4846: l_cache_cnt := l_cache_cnt + 1;
4847: end if;
4848: hr_utility.trace('Time 7 '||to_char(sysdate, 'HH24:MI:SS'));
4844: hr_utility.trace('Time 6 '||to_char(sysdate, 'HH24:MI:SS'));
4845: l_batch_bal_cache(l_cache_cnt) := l_transform_cache(cnt);
4846: l_cache_cnt := l_cache_cnt + 1;
4847: end if;
4848: hr_utility.trace('Time 7 '||to_char(sysdate, 'HH24:MI:SS'));
4849: --
4850: else
4851: hr_utility.trace('Time 8 '||to_char(sysdate, 'HH24:MI:SS'));
4852: l_batch_bal_cache(l_cache_cnt) := l_transform_cache(cnt);
4847: end if;
4848: hr_utility.trace('Time 7 '||to_char(sysdate, 'HH24:MI:SS'));
4849: --
4850: else
4851: hr_utility.trace('Time 8 '||to_char(sysdate, 'HH24:MI:SS'));
4852: l_batch_bal_cache(l_cache_cnt) := l_transform_cache(cnt);
4853: prev_def_bal_id := l_batch_bal_cache(l_cache_cnt).defined_balance_id;
4854: start_def_bal_ptr := l_cache_cnt;
4855: l_cache_cnt := l_cache_cnt + 1;
4853: prev_def_bal_id := l_batch_bal_cache(l_cache_cnt).defined_balance_id;
4854: start_def_bal_ptr := l_cache_cnt;
4855: l_cache_cnt := l_cache_cnt + 1;
4856: end if;
4857: hr_utility.trace('Time 9 '||to_char(sysdate, 'HH24:MI:SS'));
4858: --
4859: end loop;
4860: --
4861: -- Now get the balances
5014: l_decode_required boolean;
5015: l_jur_lvl pay_balance_types.jurisdiction_level%type;
5016: --
5017: begin
5018: g_debug := hr_utility.debug_enabled;
5019: --
5020: set_context ('ASSIGNMENT_ACTION_ID', p_assignment_action_id);
5021: --
5022: -- Ensure the internal cache is empty.
5129: p_date_earned in date
5130: ) return number is
5131: p_balance_result number;
5132: begin
5133: g_debug := hr_utility.debug_enabled;
5134: --
5135: if p_tax_unit_id is not null then
5136: set_context('TAX_UNIT_ID', p_tax_unit_id);
5137: end if;
5166: p_date_earned in date
5167: ) return number is
5168: p_balance_result number;
5169: begin
5170: g_debug := hr_utility.debug_enabled;
5171: --
5172: if p_tax_unit_id is not null then
5173: set_context('TAX_UNIT_ID', p_tax_unit_id);
5174: end if;
5209: p_date_earned in date
5210: ) return number is
5211: p_balance_result number;
5212: begin
5213: g_debug := hr_utility.debug_enabled;
5214: --
5215: if p_tax_unit_id is not null then
5216: set_context('TAX_UNIT_ID', p_tax_unit_id);
5217: end if;
5246: p_assignment_action_id in number
5247: ) return number is
5248: p_balance_result number;
5249: begin
5250: g_debug := hr_utility.debug_enabled;
5251: p_balance_result := get_value (p_defined_balance_id,
5252: p_assignment_action_id,
5253: false); -- look for a latest balance first
5254: return p_balance_result;
5261: p_always_get_db_item in boolean
5262: ) return number is
5263: p_balance_result number;
5264: begin
5265: g_debug := hr_utility.debug_enabled;
5266: p_balance_result := get_value(p_defined_balance_id => p_defined_balance_id
5267: ,p_assignment_action_id => p_assignment_action_id
5268: ,p_get_rr_route => p_always_get_db_item
5269: ,p_get_rb_route => false
5295: p_balance_result number;
5296: l_get_rr_route boolean;
5297: l_get_rb_route boolean;
5298: begin
5299: g_debug := hr_utility.debug_enabled;
5300: --
5301: -- p_get_rr_route and p_get_rb_route have been set to number rather than
5302: -- boolean, so that can be called in a select statement.
5303: --
5386: --
5387: ------------------------- get_value (action mode) -------------------------
5388: --
5389: begin
5390: g_debug := hr_utility.debug_enabled;
5391: if g_debug then
5392: hr_utility.set_location ('pay_balance_pkg.get_value - ACTION MODE', 1);
5393: hr_utility.trace ('def_bal_id = ' || to_char(p_defined_balance_id));
5394: hr_utility.trace ('asg_action_id = ' || to_char(p_assignment_action_id));
5388: --
5389: begin
5390: g_debug := hr_utility.debug_enabled;
5391: if g_debug then
5392: hr_utility.set_location ('pay_balance_pkg.get_value - ACTION MODE', 1);
5393: hr_utility.trace ('def_bal_id = ' || to_char(p_defined_balance_id));
5394: hr_utility.trace ('asg_action_id = ' || to_char(p_assignment_action_id));
5395: end if;
5396: --
5389: begin
5390: g_debug := hr_utility.debug_enabled;
5391: if g_debug then
5392: hr_utility.set_location ('pay_balance_pkg.get_value - ACTION MODE', 1);
5393: hr_utility.trace ('def_bal_id = ' || to_char(p_defined_balance_id));
5394: hr_utility.trace ('asg_action_id = ' || to_char(p_assignment_action_id));
5395: end if;
5396: --
5397: -- Set up the context of assignment action:
5390: g_debug := hr_utility.debug_enabled;
5391: if g_debug then
5392: hr_utility.set_location ('pay_balance_pkg.get_value - ACTION MODE', 1);
5393: hr_utility.trace ('def_bal_id = ' || to_char(p_defined_balance_id));
5394: hr_utility.trace ('asg_action_id = ' || to_char(p_assignment_action_id));
5395: end if;
5396: --
5397: -- Set up the context of assignment action:
5398: --
5459: p_virtual_date in date
5460: ) return number is
5461: p_balance_result number;
5462: begin
5463: g_debug := hr_utility.debug_enabled;
5464: p_balance_result := get_value_lock (p_defined_balance_id,
5465: p_assignment_id,
5466: p_virtual_date,
5467: false, -- look for a latest balance first
5477: p_always_get_db_item in boolean
5478: ) return number is
5479: p_balance_result number;
5480: begin
5481: g_debug := hr_utility.debug_enabled;
5482: p_balance_result := get_value_lock (p_defined_balance_id,
5483: p_assignment_id,
5484: p_virtual_date,
5485: p_always_get_db_item,
5495: p_asg_lock in varchar2
5496: ) return number is
5497: p_balance_result number;
5498: begin
5499: g_debug := hr_utility.debug_enabled;
5500: p_balance_result := get_value_lock (p_defined_balance_id,
5501: p_assignment_id,
5502: p_virtual_date,
5503: false, -- look for a latest balance first
5523: l_time_period_id per_time_periods.time_period_id%type;
5524: l_asg_lock boolean;
5525: --
5526: begin
5527: g_debug := hr_utility.debug_enabled;
5528: if g_debug then
5529: hr_utility.set_location ('pay_balance_pkg.get_value - DATE MODE', 1);
5530: hr_utility.trace ('def_bal_id = ' || to_char(p_defined_balance_id));
5531: hr_utility.trace ('Assign_id = ' || to_char(p_assignment_id));
5525: --
5526: begin
5527: g_debug := hr_utility.debug_enabled;
5528: if g_debug then
5529: hr_utility.set_location ('pay_balance_pkg.get_value - DATE MODE', 1);
5530: hr_utility.trace ('def_bal_id = ' || to_char(p_defined_balance_id));
5531: hr_utility.trace ('Assign_id = ' || to_char(p_assignment_id));
5532: hr_utility.trace ('V_date = ' || to_char (p_virtual_date));
5533: end if;
5526: begin
5527: g_debug := hr_utility.debug_enabled;
5528: if g_debug then
5529: hr_utility.set_location ('pay_balance_pkg.get_value - DATE MODE', 1);
5530: hr_utility.trace ('def_bal_id = ' || to_char(p_defined_balance_id));
5531: hr_utility.trace ('Assign_id = ' || to_char(p_assignment_id));
5532: hr_utility.trace ('V_date = ' || to_char (p_virtual_date));
5533: end if;
5534: --
5527: g_debug := hr_utility.debug_enabled;
5528: if g_debug then
5529: hr_utility.set_location ('pay_balance_pkg.get_value - DATE MODE', 1);
5530: hr_utility.trace ('def_bal_id = ' || to_char(p_defined_balance_id));
5531: hr_utility.trace ('Assign_id = ' || to_char(p_assignment_id));
5532: hr_utility.trace ('V_date = ' || to_char (p_virtual_date));
5533: end if;
5534: --
5535: -- Set the assignment locking flag.
5528: if g_debug then
5529: hr_utility.set_location ('pay_balance_pkg.get_value - DATE MODE', 1);
5530: hr_utility.trace ('def_bal_id = ' || to_char(p_defined_balance_id));
5531: hr_utility.trace ('Assign_id = ' || to_char(p_assignment_id));
5532: hr_utility.trace ('V_date = ' || to_char (p_virtual_date));
5533: end if;
5534: --
5535: -- Set the assignment locking flag.
5536: --
5543: --
5544: -- get the payroll information
5545: --
5546: if g_debug then
5547: hr_utility.set_location ('pay_balance_pkg.get_value', 10);
5548: end if;
5549: select ASSIGN.payroll_id,
5550: ASSIGN.business_group_id,
5551: PAYROLL.consolidation_set_id
5564: -- If there is a time period id, then get it, else use a null value:
5565: --
5566: begin
5567: if g_debug then
5568: hr_utility.set_location ('pay_balance_pkg.get_value', 12);
5569: end if;
5570: select TIMEP.time_period_id
5571: into l_time_period_id
5572: from per_time_periods TIMEP
5575: and TIMEP.end_date;
5576: exception
5577: when no_data_found then
5578: if g_debug then
5579: hr_utility.set_location ('pay_balance_pkg.get_value', 13);
5580: end if;
5581: l_time_period_id := null;
5582: end;
5583: --
5589: --
5590: -- insert a temporary row into pay_payroll_actions
5591: --
5592: if g_debug then
5593: hr_utility.set_location ('pay_balance_pkg.get_value', 20);
5594: end if;
5595: insert into pay_payroll_actions
5596: (payroll_action_id,
5597: action_type,
5619: --
5620: -- now insert the assignment action:
5621: --
5622: if g_debug then
5623: hr_utility.set_location ('pay_balance_pkg.get_value', 25);
5624: end if;
5625: hrassact.inassact (pactid => l_pay_action_id,
5626: asgid => p_assignment_id,
5627: p_asg_lock => l_asg_lock);
5628: --
5629: -- retrieve the assignment action id:
5630: --
5631: if g_debug then
5632: hr_utility.set_location ('pay_balance_pkg.get_value', 30);
5633: end if;
5634: select assignment_action_id
5635: into l_ass_action_id
5636: from pay_assignment_actions
5636: from pay_assignment_actions
5637: where payroll_action_id = l_pay_action_id;
5638: --
5639: if g_debug then
5640: hr_utility.trace ('Assignment action id = ' || to_char (l_ass_action_id));
5641: end if;
5642: --
5643: -- Now retrieve the balance for this temp. assignment action:
5644: --
5958: l_ivchk varchar2(2);
5959: l_rrv_found number := -1;
5960: --
5961: BEGIN
5962: g_debug := hr_utility.debug_enabled;
5963: if g_debug then
5964: hr_utility.set_location('Entering: pay_balance_pkg.invalidate_run_balances',5);
5965: end if;
5966: --
5960: --
5961: BEGIN
5962: g_debug := hr_utility.debug_enabled;
5963: if g_debug then
5964: hr_utility.set_location('Entering: pay_balance_pkg.invalidate_run_balances',5);
5965: end if;
5966: --
5967: if HRASSACT.CHECK_LATEST_BALANCES = TRUE then
5968: --
5988: update pay_balance_validation
5989: set run_balance_status = 'I'
5990: where defined_balance_id = each_row.defined_balance_id;
5991: if g_debug then
5992: hr_utility.set_location('pay_balance_pkg.invalidate_run_balances', 15);
5993: end if;
5994: end if;
5995: end loop;
5996: if g_debug then
5993: end if;
5994: end if;
5995: end loop;
5996: if g_debug then
5997: hr_utility.set_location('Leaving: pay_balance_pkg.invalidate_run_balances', 20);
5998: end if;
5999: --
6000: end if;
6001: --
6014: where pdb.balance_type_id = p_bal_id
6015: and pdb.save_run_balance = 'Y';
6016: --
6017: BEGIN
6018: g_debug := hr_utility.debug_enabled;
6019: if g_debug then
6020: hr_utility.set_location('Entering: pay_balance_pkg.invalidate_run_balances',5);
6021: end if;
6022: for each_row in get_def_bals(p_balance_type_id) loop
6016: --
6017: BEGIN
6018: g_debug := hr_utility.debug_enabled;
6019: if g_debug then
6020: hr_utility.set_location('Entering: pay_balance_pkg.invalidate_run_balances',5);
6021: end if;
6022: for each_row in get_def_bals(p_balance_type_id) loop
6023: --
6024: update pay_balance_validation
6024: update pay_balance_validation
6025: set run_balance_status = 'I'
6026: where defined_balance_id = each_row.defined_balance_id;
6027: if g_debug then
6028: hr_utility.set_location('pay_balance_pkg.invalidate_run_balances', 15);
6029: end if;
6030: end loop;
6031: if g_debug then
6032: hr_utility.set_location('Leaving: pay_balance_pkg.invalidate_run_balances', 20);
6028: hr_utility.set_location('pay_balance_pkg.invalidate_run_balances', 15);
6029: end if;
6030: end loop;
6031: if g_debug then
6032: hr_utility.set_location('Leaving: pay_balance_pkg.invalidate_run_balances', 20);
6033: end if;
6034: END invalidate_run_balances;
6035: --------------------------------------------------------------------------
6036: -- get_run_result_info
6214: current_ptr number;
6215: begin
6216: --
6217: if g_debug then
6218: hr_utility.set_location('Entering: pay_balance_pkg.search_rb_cache', 5);
6219: end if;
6220: if (p_grp_rb_ptr_list.exists(p_defined_balanceid)) then
6221: --
6222: current_ptr := p_grp_rb_ptr_list(p_defined_balanceid);
6242: then
6243: --
6244: l_found := TRUE;
6245: if g_debug then
6246: hr_utility.set_location('pay_balance_pkg.search_rb_cache', 10);
6247: end if;
6248: --
6249: else
6250: --
6250: --
6251: prev_ptr := current_ptr;
6252: current_ptr := p_int_mlt_thrd_cache(current_ptr).next;
6253: if g_debug then
6254: hr_utility.set_location('pay_balance_pkg.search_rb_cache', 15);
6255: end if;
6256: --
6257: end if;
6258: --
6268: p_current_ptr := current_ptr;
6269: p_previous_ptr := prev_ptr;
6270: --
6271: if g_debug then
6272: hr_utility.set_location('Leaving: pay_balance_pkg.search_rb_cache', 20);
6273: end if;
6274: --
6275: end search_rb_cache;
6276: --
6348: --
6349: if p_amount <> 0 then
6350: --
6351: if g_debug then
6352: hr_utility.set_location('pay_balance_pkg.subtract_from_grp_bal', 25);
6353: end if;
6354: --
6355: if not p_multi_thread then -- i.e. if in single thread mode
6356: --
6356: --
6357: -- update existing row, by subtracting the contributing amount
6358: --
6359: if g_debug then
6360: hr_utility.trace('p_grp_def_bal_id: '||p_grp_def_bal_id);
6361: hr_utility.trace('pactid: '||p_payroll_action_id);
6362: end if;
6363: open get_row_to_update(p_grp_def_bal_id
6364: ,p_payroll_action_id
6357: -- update existing row, by subtracting the contributing amount
6358: --
6359: if g_debug then
6360: hr_utility.trace('p_grp_def_bal_id: '||p_grp_def_bal_id);
6361: hr_utility.trace('pactid: '||p_payroll_action_id);
6362: end if;
6363: open get_row_to_update(p_grp_def_bal_id
6364: ,p_payroll_action_id
6365: ,p_tax_unit_id
6385: --
6386: else
6387: close get_row_to_update;
6388: if g_debug then
6389: hr_utility.set_location('pay_balance_pkg.subtract_from_grp_bal', 40);
6390: hr_utility.trace('p_grp_def_bal_id: '||to_char(p_grp_def_bal_id));
6391: hr_utility.trace('l_grp_run_bal_val: '||to_char(l_grp_run_bal_val));
6392: hr_utility.trace('contrib amt: '||to_char(p_amount));
6393: end if;
6386: else
6387: close get_row_to_update;
6388: if g_debug then
6389: hr_utility.set_location('pay_balance_pkg.subtract_from_grp_bal', 40);
6390: hr_utility.trace('p_grp_def_bal_id: '||to_char(p_grp_def_bal_id));
6391: hr_utility.trace('l_grp_run_bal_val: '||to_char(l_grp_run_bal_val));
6392: hr_utility.trace('contrib amt: '||to_char(p_amount));
6393: end if;
6394: --
6387: close get_row_to_update;
6388: if g_debug then
6389: hr_utility.set_location('pay_balance_pkg.subtract_from_grp_bal', 40);
6390: hr_utility.trace('p_grp_def_bal_id: '||to_char(p_grp_def_bal_id));
6391: hr_utility.trace('l_grp_run_bal_val: '||to_char(l_grp_run_bal_val));
6392: hr_utility.trace('contrib amt: '||to_char(p_amount));
6393: end if;
6394: --
6395: update pay_run_balances
6388: if g_debug then
6389: hr_utility.set_location('pay_balance_pkg.subtract_from_grp_bal', 40);
6390: hr_utility.trace('p_grp_def_bal_id: '||to_char(p_grp_def_bal_id));
6391: hr_utility.trace('l_grp_run_bal_val: '||to_char(l_grp_run_bal_val));
6392: hr_utility.trace('contrib amt: '||to_char(p_amount));
6393: end if;
6394: --
6395: update pay_run_balances
6396: set balance_value = (balance_value - p_amount)
6425: --
6426: if (l_found = TRUE) then
6427: --
6428: if g_debug then
6429: hr_utility.set_location('pay_balance_pkg.subtract_from_grp_bal', 50);
6430: hr_utility.trace('MULTI THREADED UPDATE');
6431: end if;
6432: update pay_run_balances
6433: set balance_value = balance_value - p_amount
6426: if (l_found = TRUE) then
6427: --
6428: if g_debug then
6429: hr_utility.set_location('pay_balance_pkg.subtract_from_grp_bal', 50);
6430: hr_utility.trace('MULTI THREADED UPDATE');
6431: end if;
6432: update pay_run_balances
6433: set balance_value = balance_value - p_amount
6434: where run_balance_id = p_int_mlt_thrd_cache(l_current_ptr).run_balance_id;
6434: where run_balance_id = p_int_mlt_thrd_cache(l_current_ptr).run_balance_id;
6435: --
6436: else -- no row for this balance
6437: if g_debug then
6438: hr_utility.trace('MULTI THREADED INSERT');
6439: hr_utility.set_location('pay_balance_pkg.subtract_from_grp_bal', 55);
6440: end if;
6441: --
6442: --
6435: --
6436: else -- no row for this balance
6437: if g_debug then
6438: hr_utility.trace('MULTI THREADED INSERT');
6439: hr_utility.set_location('pay_balance_pkg.subtract_from_grp_bal', 55);
6440: end if;
6441: --
6442: --
6443: select pay_run_balances_s.nextval
6491: ,p_source_number2
6492: ,p_organization_id
6493: );
6494: if g_debug then
6495: hr_utility.set_location('pay_balance_pkg.subtract_from_grp_bal', 60);
6496: end if;
6497: --
6498: -- cache the row details
6499: --
6524: p_int_mlt_thrd_cache(l_previous_ptr).next := l_cache_ct;
6525: end if;
6526: --
6527: if g_debug then
6528: hr_utility.set_location('pay_balance_pkg.subtract_from_grp_bal', 65);
6529: end if;
6530: --
6531: end if;
6532: --
6532: --
6533: end if;
6534: else -- not > 0
6535: if g_debug then
6536: hr_utility.set_location('pay_balance_pkg.subtract_from_grp_bal', 70);
6537: end if;
6538: end if;
6539: --
6540: end subtract_from_grp_bal;
6620: l_asgbal_cnt number;
6621: l_inp_val_name pay_input_values_f.name%type;
6622: --
6623: BEGIN
6624: g_debug := hr_utility.debug_enabled;
6625: if g_debug then
6626: hr_utility.set_location('Entering: pay_balance_pkg.remove_asg_contribs', 5);
6627: end if;
6628: --
6622: --
6623: BEGIN
6624: g_debug := hr_utility.debug_enabled;
6625: if g_debug then
6626: hr_utility.set_location('Entering: pay_balance_pkg.remove_asg_contribs', 5);
6627: end if;
6628: --
6629: if (p_payroll_action_id <> g_payroll_action) then
6630: --
6840: end if;
6841: --
6842: end if;
6843: if g_debug then
6844: hr_utility.set_location('Leaving: pay_balance_pkg.remove_asg_contribs', 80);
6845: end if;
6846: END remove_asg_contribs;
6847: --
6848: --------------------------------------------------------------------------
6960: begin
6961: --
6962: --
6963: if g_debug then
6964: hr_utility.set_location('pay_balance_pkg.create_run_balance', 30);
6965: hr_utility.trace('Getting Balance'||p_def_bal_id);
6966: end if;
6967: /* First setup the contexts */
6968: if (nvl(p_contexts.tax_unit_id, -999) <> nvl(p_contexts.prv_tax_unit_id, -999)) then
6961: --
6962: --
6963: if g_debug then
6964: hr_utility.set_location('pay_balance_pkg.create_run_balance', 30);
6965: hr_utility.trace('Getting Balance'||p_def_bal_id);
6966: end if;
6967: /* First setup the contexts */
6968: if (nvl(p_contexts.tax_unit_id, -999) <> nvl(p_contexts.prv_tax_unit_id, -999)) then
6969: pay_balance_pkg.set_context('TAX_UNIT_ID', p_contexts.tax_unit_id);
7114: ,p_get_rb_route => false);
7115: --
7116: if (bal_val <> 0) then
7117: if g_debug then
7118: hr_utility.set_location('pay_balance_pkg.create_run_balance', 35);
7119: end if;
7120: ins_run_balance (p_defined_balance_id => p_def_bal_id,
7121: p_eff_date => p_effective_date,
7122: p_bal_val => bal_val,
7139: end if;
7140: end if;
7141: --
7142: if g_debug then
7143: hr_utility.set_location('pay_balance_pkg.create_run_balance', 40);
7144: end if;
7145: --
7146: end create_run_balance;
7147: --
7299: ;
7300:
7301: begin
7302: --
7303: g_debug := hr_utility.debug_enabled;
7304: if g_debug then
7305: hr_utility.set_location('Entering:'||l_proc, 5);
7306: end if;
7307: --
7301: begin
7302: --
7303: g_debug := hr_utility.debug_enabled;
7304: if g_debug then
7305: hr_utility.set_location('Entering:'||l_proc, 5);
7306: end if;
7307: --
7308: -- Derive information for the specified run result.
7309: --
7321:
7322: if NOT (l_legrule_found and l_save_run_bal_flag = 'Y') then
7323:
7324: if g_debug then
7325: hr_utility.set_location(l_proc, 10);
7326: end if;
7327: -- Saving run balance is not supported, exit the process.
7328: return;
7329: end if;
7328: return;
7329: end if;
7330:
7331: if g_debug then
7332: hr_utility.set_location(l_proc, 20);
7333: end if;
7334: --
7335: -- Setup run result level contexts.
7336: --
7357: )
7358: loop
7359:
7360: if g_debug then
7361: hr_utility.set_location(l_proc, 25);
7362: end if;
7363:
7364: -- copy the run result contexts
7365: l_context := l_rr_ctx;
7406: --
7407: if l_valid_contexts then
7408:
7409: if g_debug then
7410: hr_utility.set_location(l_proc, 30);
7411: hr_utility.trace(' Defined Balance ID: '
7412: || l_rec.defined_balance_id);
7413: end if;
7414: --
7407: if l_valid_contexts then
7408:
7409: if g_debug then
7410: hr_utility.set_location(l_proc, 30);
7411: hr_utility.trace(' Defined Balance ID: '
7412: || l_rec.defined_balance_id);
7413: end if;
7414: --
7415: -- Delete run balance records that may conflict with new one.
7459:
7460: end loop;
7461: --
7462: if g_debug then
7463: hr_utility.set_location('Leaving: '||l_proc, 50);
7464: end if;
7465: end create_rr_asg_balances;
7466: --
7467: --------------------------------------------------------------------------
7549: --
7550: begin
7551: --
7552: if g_debug then
7553: hr_utility.set_location('Entering: pay_balance_pkg.create_set_asg_balance', 5);
7554: end if;
7555: --
7556: -- Check whether the SOURCE_ID, SOURCE_TEXT contexts are used.
7557: l_si_needed_chr := 'N';
7623: for cnt in 1..p_defined_balance_lst.count loop
7624: --
7625: if (p_load_type = 'FORCE') then
7626: if g_debug then
7627: hr_utility.set_location('pay_balance_pkg.create_set_asg_balance', 20);
7628: end if;
7629: delete from pay_run_balances
7630: where defined_balance_id = p_defined_balance_lst(cnt).defined_balance_id
7631: and assignment_action_id = p_asgact_id;
7632: elsif (p_load_type = 'TRUSTED') then
7633: null;
7634: else
7635: if g_debug then
7636: hr_utility.set_location('pay_balance_pkg.create_set_asg_balance', 25);
7637: end if;
7638: declare
7639: l_dummy number;
7640: begin
7648: and assignment_action_id = p_asgact_id
7649: and balance_value <> 0);
7650: --
7651: /* Error, there should be no rows in this mode */
7652: hr_utility.set_message(801,'HR_34723_NO_ROWS_NORMAL_MODE');
7653: hr_utility.raise_error;
7654: --
7655: exception
7656: when no_data_found then
7649: and balance_value <> 0);
7650: --
7651: /* Error, there should be no rows in this mode */
7652: hr_utility.set_message(801,'HR_34723_NO_ROWS_NORMAL_MODE');
7653: hr_utility.raise_error;
7654: --
7655: exception
7656: when no_data_found then
7657: null;
7662: -- Generate the context list
7663: --
7664: l_cnt := 1;
7665: if g_debug then
7666: hr_utility.set_location('pay_balance_pkg.create_set_asg_balance', 27);
7667: end if;
7668: for cxt in get_contexts(p_asgact_id,
7669: l_si_needed_chr,
7670: l_st_needed_chr,
7672: l_st2_needed_chr,
7673: l_sn2_needed_chr,
7674: l_org_needed_chr) loop
7675: if g_debug then
7676: hr_utility.set_location('pay_balance_pkg.create_set_asg_balance', 28);
7677: end if;
7678: l_context_lst(l_cnt).tax_unit_id := cxt.tax_unit_id;
7679: l_context_lst(l_cnt).jurisdiction_code := cxt.jurisdiction_code;
7680: l_context_lst(l_cnt).source_id := cxt.source_id;
7692: --
7693: -- Go Get the balance values
7694: --
7695: if g_debug then
7696: hr_utility.set_location('pay_balance_pkg.create_set_asg_balance', 30);
7697: end if;
7698: for aarec in get_aa (p_asgact_id) loop
7699: pay_balance_pkg.get_value (p_asgact_id,
7700: p_defined_balance_lst,
7705: --
7706: -- Insert the results in the run_balance table.
7707: --
7708: if g_debug then
7709: hr_utility.set_location('pay_balance_pkg.create_set_asg_balance', 35);
7710: end if;
7711: for cnt in 1..l_output_list.count loop
7712: if (l_output_list(cnt).balance_value <> 0) then
7713: --
7711: for cnt in 1..l_output_list.count loop
7712: if (l_output_list(cnt).balance_value <> 0) then
7713: --
7714: if g_debug then
7715: hr_utility.set_location('pay_balance_pkg.create_set_asg_balance', 40);
7716: end if;
7717: ins_run_balance (p_defined_balance_id => l_output_list(cnt).defined_balance_id,
7718: p_eff_date => aarec.effective_date,
7719: p_bal_val => l_output_list(cnt).balance_value,
7738: end loop;
7739: end loop;
7740: --
7741: if g_debug then
7742: hr_utility.set_location('Leaving: pay_balance_pkg.create_set_asg_balance', 50);
7743: end if;
7744: end create_set_asg_balance;
7745: --
7746: procedure add_grpbal_to_list(p_def_bal_id in number,
7827: --
7828: begin
7829: --
7830: if g_debug then
7831: hr_utility.set_location('Entering pay_balance_pkg.add_grpbal_to_list',10);
7832: hr_utility.trace('p_load_type: '||p_load_type);
7833: end if;
7834: --
7835: l_jc_needed_chr := 'N';
7828: begin
7829: --
7830: if g_debug then
7831: hr_utility.set_location('Entering pay_balance_pkg.add_grpbal_to_list',10);
7832: hr_utility.trace('p_load_type: '||p_load_type);
7833: end if;
7834: --
7835: l_jc_needed_chr := 'N';
7836: l_si_needed_chr := 'N';
7881: end if;
7882: --
7883: if (p_load_type = 'FORCE') then
7884: if g_debug then
7885: hr_utility.set_location('pay_balance_pkg.add_grpbal_to_list', 20);
7886: end if;
7887: delete from pay_run_balances
7888: where defined_balance_id = p_def_bal_id
7889: and payroll_action_id = p_pactid;
7890: elsif (p_load_type = 'TRUSTED') then
7891: null;
7892: else
7893: if g_debug then
7894: hr_utility.set_location('pay_balance_pkg.add_grpbal_to_list', 25);
7895: end if;
7896: declare
7897: l_dummy number;
7898: begin
7905: and payroll_action_id = p_pactid
7906: and balance_value <> 0);
7907: --
7908: -- Error, there should be no rows in this mode
7909: hr_utility.set_message(801,'HR_34723_NO_ROWS_NORMAL_MODE');
7910: hr_utility.raise_error;
7911: --
7912: exception
7913: when no_data_found then
7906: and balance_value <> 0);
7907: --
7908: -- Error, there should be no rows in this mode
7909: hr_utility.set_message(801,'HR_34723_NO_ROWS_NORMAL_MODE');
7910: hr_utility.raise_error;
7911: --
7912: exception
7913: when no_data_found then
7914: null;
7986: --
7987: end loop;
7988: --
7989: if g_debug then
7990: hr_utility.set_location('Leaving: pay_balance_pkg.add_grpbal_to_list', 40);
7991: end if;
7992: --
7993: end add_grpbal_to_list;
7994: -----------------------------------------------------------------------------
8262: l_eff_date date;
8263: begin
8264: --
8265: if g_debug then
8266: hr_utility.set_location(
8267: 'Entering:pay_balance_pkg.create_all_gre_balances_full',10);
8268: end if;
8269: --
8270: select pbg.business_group_id,
8296: --
8297: if (p_load_type = 'TRUSTED') then
8298: --
8299: if g_debug then
8300: hr_utility.set_location(
8301: 'pay_balance_pkg.create_all_grp_balances_full', 20);
8302: end if;
8303: --
8304: for dbarec in crs_balatt(p_pact_id
8316: elsif (p_load_type = 'FORCE'
8317: and p_def_bal is not null) then
8318: --
8319: if g_debug then
8320: hr_utility.set_location(
8321: 'pay_balance_pkg.create_all_grp_balances_full', 30);
8322: end if;
8323: --
8324: for dbsarec in crs_balatt(p_pact_id
8339: end if;
8340: else -- p_delta is Y thus in Delta mode
8341: --
8342: if g_debug then
8343: hr_utility.set_location(
8344: 'pay_balance_pkg.create_all_grp_balances_full', 35);
8345: end if;
8346: --
8347: for dbdrec in crs_delta_balatt(p_pact_id
8359: end if;
8360: --
8361: else
8362: if g_debug then
8363: hr_utility.set_location(
8364: 'pay_balance_pkg.create_all_grp_balances_full', 40);
8365: end if;
8366: --
8367: for dbrec in crs_asgact(p_pact_id,
8382: -- Bug 4318391.
8383: -- Ensure if any defind balances found.
8384: --
8385: if l_output_list.count = 0 then
8386: hr_utility.set_location('pay_balance_pkg.create_all_grp_balances_full', 50);
8387: --
8388: -- Exit this procedure.
8389: --
8390: return;
8460: end loop;
8461: --
8462: l_cnt := 1;
8463: if g_debug then
8464: hr_utility.set_location(
8465: 'pay_balance_pkg.create_all_grp_balances_full', 60);
8466: end if;
8467: --
8468: for cxt in get_contexts_2(p_pact_id,
8472: l_st2_needed_chr,
8473: l_sn2_needed_chr,
8474: l_org_needed_chr) loop
8475: if g_debug then
8476: hr_utility.set_location(
8477: 'pay_balance_pkg.create_all_grp_balances_full', 70);
8478: end if;
8479: --
8480: l_context_lst(l_cnt).tax_unit_id := cxt.tax_unit_id;
8494: --
8495: -- Go Get the balance values
8496: --
8497: if g_debug then
8498: hr_utility.set_location(
8499: 'pay_balance_pkg.create_all_grp_balances_full', 80);
8500: end if;
8501: --
8502: for aarec in get_aa (p_pact_id) loop
8508: --
8509: -- Insert the results in the run_balance table.
8510: --
8511: if g_debug then
8512: hr_utility.set_location(
8513: 'pay_balance_pkg.create_all_grp_balances_full', 85);
8514: end if;
8515: for cnt in 1..l_output_list.count loop
8516: if (l_output_list(cnt).balance_value <> 0) then
8515: for cnt in 1..l_output_list.count loop
8516: if (l_output_list(cnt).balance_value <> 0) then
8517: --
8518: if g_debug then
8519: hr_utility.set_location(
8520: 'pay_balance_pkg.create_all_grp_balances_full', 90);
8521: end if;
8522: --
8523: ins_run_balance (p_defined_balance_id => l_output_list(cnt).defined_balance_id,
8544: end loop;
8545: end loop;
8546: --
8547: if g_debug then
8548: hr_utility.set_location(
8549: 'Leaving: pay_balance_pkg.create_all_grp_balances_full', 95);
8550: end if;
8551: --
8552: end create_all_grp_balances_full;
8635: --
8636: begin
8637: --
8638: if g_debug then
8639: hr_utility.set_location('Entering pay_balance_pkg.add_asgbal_to_list',10);
8640: hr_utility.trace('p_load_type: '||p_load_type);
8641: end if;
8642: l_jc_needed_chr := 'N';
8643: l_si_needed_chr := 'N';
8636: begin
8637: --
8638: if g_debug then
8639: hr_utility.set_location('Entering pay_balance_pkg.add_asgbal_to_list',10);
8640: hr_utility.trace('p_load_type: '||p_load_type);
8641: end if;
8642: l_jc_needed_chr := 'N';
8643: l_si_needed_chr := 'N';
8644: l_st_needed_chr := 'N';
8688: end if;
8689: --
8690: if (p_load_type = 'FORCE') then
8691: if g_debug then
8692: hr_utility.set_location('pay_balance_pkg.add_asgbal_to_list', 20);
8693: end if;
8694: delete from pay_run_balances
8695: where defined_balance_id = p_def_bal_id
8696: and assignment_action_id = p_asgact_id;
8697: elsif (p_load_type = 'TRUSTED') then
8698: null;
8699: else
8700: if g_debug then
8701: hr_utility.set_location('pay_balance_pkg.add_asgbal_to_list', 25);
8702: end if;
8703: declare
8704: l_dummy number;
8705: begin
8712: and assignment_action_id = p_asgact_id
8713: and balance_value <> 0);
8714: --
8715: -- Error, there should be no rows in this mode
8716: hr_utility.set_message(801,'HR_34723_NO_ROWS_NORMAL_MODE');
8717: hr_utility.raise_error;
8718: --
8719: exception
8720: when no_data_found then
8713: and balance_value <> 0);
8714: --
8715: -- Error, there should be no rows in this mode
8716: hr_utility.set_message(801,'HR_34723_NO_ROWS_NORMAL_MODE');
8717: hr_utility.raise_error;
8718: --
8719: exception
8720: when no_data_found then
8721: null;
8793: --
8794: end loop;
8795: --
8796: if g_debug then
8797: hr_utility.set_location('Leaving: pay_balance_pkg.add_asgbal_to_list', 40);
8798: end if;
8799: --
8800: end add_asgbal_to_list;
8801: --
9102: l_leg_code per_business_groups.legislation_code%type;
9103: begin
9104: --
9105: if g_debug then
9106: hr_utility.set_location(
9107: 'Entering:pay_balance_pkg.create_all_asg_balances_full',10);
9108: end if;
9109: --
9110: select pbg.business_group_id,
9130: -- is not null and p_bal_list is a GEN_BAL
9132: -- add_asgbal_to_list.
9133: --
9134: hr_utility.trace('p_delta is: '||p_delta);
9135: if (p_delta = 'N'
9136: or p_delta is null) then -- if NOT in DELTA MODE
9137: --
9138: if (p_load_type = 'TRUSTED') then
9137: --
9138: if (p_load_type = 'TRUSTED') then
9139: --
9140: if g_debug then
9141: hr_utility.set_location(
9142: 'pay_balance_pkg.create_all_asg_balances_full', 20);
9143: end if;
9144: --
9145: for dbarec in crs_balatt(p_asgact_id
9157: elsif (p_load_type = 'FORCE'
9158: and p_def_bal is not null) then
9159: --
9160: if g_debug then
9161: hr_utility.set_location(
9162: 'pay_balance_pkg.create_all_asg_balances_full', 30);
9163: end if;
9164: --
9165: for dbsarec in crs_balatt(p_asgact_id
9180: end if;
9181: else -- p_delta is Y thus in DELTA mode
9182: --
9183: if g_debug then
9184: hr_utility.set_location(
9185: 'pay_balance_pkg.create_all_asg_balances_full', 35);
9186: end if;
9187: --
9188: --
9210: end if;
9211: --
9212: else
9213: if g_debug then
9214: hr_utility.set_location(
9215: 'pay_balance_pkg.create_all_asg_balances_full', 40);
9216: end if;
9217: --
9218: for dbrec in crs_asgact(p_asgact_id,
9302: --
9303: end loop;
9304: l_cnt := 1;
9305: if g_debug then
9306: hr_utility.set_location(
9307: 'pay_balance_pkg.create_all_asg_balances_full', 50);
9308: end if;
9309: for cxt in get_contexts_2(p_asgact_id,
9310: l_si_needed_chr,
9313: l_st2_needed_chr,
9314: l_sn2_needed_chr,
9315: l_org_needed_chr) loop
9316: if g_debug then
9317: hr_utility.set_location(
9318: 'pay_balance_pkg.create_all_asg_balances_full', 60);
9319: end if;
9320: l_context_lst(l_cnt).tax_unit_id := cxt.tax_unit_id;
9321: l_context_lst(l_cnt).jurisdiction_code := cxt.jurisdiction_code;
9334: --
9335: -- Go Get the balance values
9336: --
9337: if g_debug then
9338: hr_utility.set_location(
9339: 'pay_balance_pkg.create_all_asg_balances_full', 70);
9340: end if;
9341: for aarec in get_aa (p_asgact_id) loop
9342: pay_balance_pkg.get_value_int_batch (p_asgact_id,
9347: --
9348: -- Insert the results in the run_balance table.
9349: --
9350: if g_debug then
9351: hr_utility.set_location(
9352: 'pay_balance_pkg.create_all_asg_balances_full', 80);
9353: end if;
9354: for cnt in 1..l_output_list.count loop
9355: if (l_output_list(cnt).balance_value <> 0) then
9354: for cnt in 1..l_output_list.count loop
9355: if (l_output_list(cnt).balance_value <> 0) then
9356: --
9357: if g_debug then
9358: hr_utility.set_location(
9359: 'pay_balance_pkg.create_all_asg_balances_full', 90);
9360: end if;
9361: ins_run_balance (p_defined_balance_id => l_output_list(cnt).defined_balance_id,
9362: p_eff_date => aarec.effective_date,
9383: end loop;
9384: --
9385: else -- no balances returned from cursors crs_balatt or crs_asgact, so do
9386: -- nothing
9387: hr_utility.set_location('pay_balance_pkg.create_all_asg_balances_full',98);
9388: end if;
9389: --
9390: if g_debug then
9391: hr_utility.set_location(
9387: hr_utility.set_location('pay_balance_pkg.create_all_asg_balances_full',98);
9388: end if;
9389: --
9390: if g_debug then
9391: hr_utility.set_location(
9392: 'Leaving: pay_balance_pkg.create_all_asg_balances_full', 100);
9393: end if;
9394: --
9395: end create_all_asg_balances_full;
9465: l_cnt number;
9466: l_delta boolean;
9467: --
9468: begin
9469: g_debug := hr_utility.debug_enabled;
9470: if g_debug then
9471: hr_utility.set_location('Entering: pay_balance_pkg.create_all_asg_balances', 5);
9472: end if;
9473: --
9467: --
9468: begin
9469: g_debug := hr_utility.debug_enabled;
9470: if g_debug then
9471: hr_utility.set_location('Entering: pay_balance_pkg.create_all_asg_balances', 5);
9472: end if;
9473: --
9474: l_balance_lst.delete;
9475: --
9484: end if;
9485: else
9486: bal_ret_buffer_sz := 100;
9487: end if;
9488: hr_utility.trace('Action Parameter BAL_RETRIEVAL_BUFFER_SIZE = '||bal_ret_buffer_sz);
9489: --
9490: /* First get the legislation rule */
9491: begin
9492: select plr.rule_mode
9508: l_cnt := 1;
9509: if (save_run_bals = 'Y') then
9510: --
9511: if g_debug then
9512: hr_utility.set_location('pay_balance_pkg.create_all_asg_balances', 25);
9513: end if;
9514: --
9515: if (p_bal_list = 'ALL'
9516: or ( p_bal_list = 'INVALID'
9531: for fullrec in crs_invalid(p_asgact_id) loop
9532: --
9533: l_balance_lst(l_cnt).defined_balance_id := fullrec.defined_balance_id;
9534: if g_debug then
9535: hr_utility.trace('Added Def Bal ID '||
9536: l_balance_lst(l_cnt).defined_balance_id);
9537: end if;
9538: --
9539: /* Have we reached the buffer limit */
9563: --
9564: end if;
9565: --
9566: if g_debug then
9567: hr_utility.set_location('Leaving: pay_balance_pkg.create_all_asg_balances', 30);
9568: end if;
9569: end create_all_asg_balances;
9570: --
9571: --------------------------------------------------------------------------
9649: --
9650: begin
9651: --
9652: if g_debug then
9653: hr_utility.set_location('Entering: pay_balance_pkg.create_set_group_balance', 5);
9654: end if;
9655: --
9656: -- Check whether the SOURCE_ID, SOURCE_TEXT contexts are used.
9657: l_si_needed_chr := 'N';
9723: for cnt in 1..p_defined_balance_lst.count loop
9724: --
9725: if (p_load_type = 'FORCE') then
9726: if g_debug then
9727: hr_utility.set_location('pay_balance_pkg.create_set_group_balance', 20);
9728: end if;
9729: delete from pay_run_balances
9730: where defined_balance_id = p_defined_balance_lst(cnt).defined_balance_id
9731: and payroll_action_id = p_pact_id;
9732: elsif (p_load_type = 'TRUSTED') then
9733: null;
9734: else
9735: if g_debug then
9736: hr_utility.set_location('pay_balance_pkg.create_set_group_balance', 25);
9737: end if;
9738: declare
9739: l_dummy number;
9740: begin
9747: and payroll_action_id = p_pact_id
9748: and balance_value <> 0);
9749: --
9750: /* Error, there should be no rows in this mode */
9751: hr_utility.set_message(801,'HR_34723_NO_ROWS_NORMAL_MODE');
9752: hr_utility.raise_error;
9753: --
9754: exception
9755: when no_data_found then
9748: and balance_value <> 0);
9749: --
9750: /* Error, there should be no rows in this mode */
9751: hr_utility.set_message(801,'HR_34723_NO_ROWS_NORMAL_MODE');
9752: hr_utility.raise_error;
9753: --
9754: exception
9755: when no_data_found then
9756: null;
9761: -- Generate the context list
9762: --
9763: l_cnt := 1;
9764: if g_debug then
9765: hr_utility.set_location('pay_balance_pkg.create_set_group_balance', 27);
9766: end if;
9767: for cxt in get_contexts(p_pact_id,
9768: l_si_needed_chr,
9769: l_st_needed_chr,
9771: l_st2_needed_chr,
9772: l_sn2_needed_chr,
9773: l_org_needed_chr) loop
9774: if g_debug then
9775: hr_utility.set_location('pay_balance_pkg.create_set_group_balance', 28);
9776: end if;
9777: l_context_lst(l_cnt).tax_unit_id := cxt.tax_unit_id;
9778: l_context_lst(l_cnt).jurisdiction_code := cxt.jurisdiction_code;
9779: l_context_lst(l_cnt).source_id := cxt.source_id;
9791: --
9792: -- Go Get the balance values
9793: --
9794: if g_debug then
9795: hr_utility.set_location('pay_balance_pkg.create_set_group_balance', 30);
9796: end if;
9797: for aarec in get_aa (p_pact_id) loop
9798: pay_balance_pkg.get_value (aarec.assignment_action_id,
9799: p_defined_balance_lst,
9804: --
9805: -- Insert the results in the run_balance table.
9806: --
9807: if g_debug then
9808: hr_utility.set_location('pay_balance_pkg.create_set_group_balance', 35);
9809: end if;
9810: for cnt in 1..l_output_list.count loop
9811: if g_debug then
9812: hr_utility.trace('Bal Value = '||l_output_list(cnt).balance_value);
9808: hr_utility.set_location('pay_balance_pkg.create_set_group_balance', 35);
9809: end if;
9810: for cnt in 1..l_output_list.count loop
9811: if g_debug then
9812: hr_utility.trace('Bal Value = '||l_output_list(cnt).balance_value);
9813: end if;
9814: if (l_output_list(cnt).balance_value <> 0) then
9815: --
9816: if g_debug then
9813: end if;
9814: if (l_output_list(cnt).balance_value <> 0) then
9815: --
9816: if g_debug then
9817: hr_utility.set_location('pay_balance_pkg.create_set_group_balance', 40);
9818: end if;
9819: ins_run_balance (p_defined_balance_id => l_output_list(cnt).defined_balance_id,
9820: p_eff_date => aarec.effective_date,
9821: p_bal_val => l_output_list(cnt).balance_value,
9840: end loop;
9841: end loop;
9842: --
9843: if g_debug then
9844: hr_utility.set_location('Leaving: pay_balance_pkg.create_set_group_balance', 50);
9845: end if;
9846: end create_set_group_balance;
9847: --------------------------------------------------------------------------
9848: -- procedure create_all_group_balances
9911: l_balance_lst t_balance_value_tab;
9912: l_cnt number;
9913: --
9914: begin
9915: g_debug := hr_utility.debug_enabled;
9916: if g_debug then
9917: hr_utility.set_location('Entering: pay_balance_pkg.create_all_group_balances', 5);
9918: end if;
9919: --
9913: --
9914: begin
9915: g_debug := hr_utility.debug_enabled;
9916: if g_debug then
9917: hr_utility.set_location('Entering: pay_balance_pkg.create_all_group_balances', 5);
9918: end if;
9919: --
9920: l_balance_lst.delete;
9921: --
9930: end if;
9931: else
9932: bal_ret_buffer_sz := 100;
9933: end if;
9934: hr_utility.trace('Action Parameter BAL_RETRIEVAL_BUFFER_SIZE = '||bal_ret_buffer_sz);
9935: --
9936: /* First get the legislation rule */
9937: begin
9938: select plr.rule_mode
9951: --
9952: if (save_run_bals = 'Y') then
9953: --
9954: if g_debug then
9955: hr_utility.set_location('pay_balance_pkg.create_all_group_balances', 25);
9956: end if;
9957: l_cnt := 1;
9958: --
9959: if (p_bal_list = 'ALL'
9975: for fullrec in crs_invalid(p_pact_id) loop
9976: --
9977: l_balance_lst(l_cnt).defined_balance_id := fullrec.defined_balance_id;
9978: if g_debug then
9979: hr_utility.trace('Added Def Bal ID '||
9980: l_balance_lst(l_cnt).defined_balance_id);
9981: end if;
9982: --
9983: /* Have we reached the buffer limit */
10006: --
10007: end if;
10008: --
10009: if g_debug then
10010: hr_utility.set_location('Leaving: pay_balance_pkg.create_all_group_balances', 30);
10011: end if;
10012: end create_all_group_balances;
10013: --
10014: --------------------------------------------------------------------------
10090: l_bal_load_date pay_balance_validation.balance_load_date%type;
10091: l_rr_exists number;
10092: --
10093: BEGIN
10094: hr_utility.set_location('Entering: pay_balance_pkg.initialise_run_balance', 10);
10095: --
10096: -- we need to determine a legislation code; for core defined balances it will
10097: -- be null.
10098: --
10099: if p_legislation_code is null then
10100: if p_business_group_id is null then
10101: -- core row
10102: l_leg_code := '';
10103: hr_utility.set_location('pay_balance_pkg.initialise_run_balance', 20);
10104: else -- bg not nulli, so user row
10105: hr_utility.set_location('pay_balance_pkg.initialise_run_balance', 30);
10106: --
10107: open get_leg_code(p_business_group_id);
10101: -- core row
10102: l_leg_code := '';
10103: hr_utility.set_location('pay_balance_pkg.initialise_run_balance', 20);
10104: else -- bg not nulli, so user row
10105: hr_utility.set_location('pay_balance_pkg.initialise_run_balance', 30);
10106: --
10107: open get_leg_code(p_business_group_id);
10108: fetch get_leg_code into l_leg_code;
10109: if get_leg_code%notfound then
10108: fetch get_leg_code into l_leg_code;
10109: if get_leg_code%notfound then
10110: close get_leg_code;
10111: -- should raise error, but should never be raised so ok
10112: hr_utility.set_location('pay_balance_pkg.initialise_run_balance', 40);
10113: end if;
10114: end if;
10115: else -- leg row
10116: l_leg_code := p_legislation_code;
10113: end if;
10114: end if;
10115: else -- leg row
10116: l_leg_code := p_legislation_code;
10117: hr_utility.set_location('pay_balance_pkg.initialise_run_balance', 50);
10118: end if;
10119: --
10120: open get_dim_level(p_baldim_id);
10121: fetch get_dim_level into l_dim_level;
10129: fetch check_run_results into l_rr_exists;
10130: close check_run_results;
10131: --
10132: if l_dim_level = 'ASG' or l_dim_level = 'GRP' then
10133: hr_utility.set_location('pay_balance_pkg.initialise_run_balance', 70);
10134: --
10135: for each_bg in enabled_bg(p_business_group_id, l_leg_code, l_dim_level)
10136: loop
10137: --
10142: else
10143: l_bal_load_date := fnd_date.canonical_to_date('0001/01/01');
10144: end if;
10145: --
10146: hr_utility.set_location('pay_balance_pkg.initialise_run_balance', 80);
10147: hr_utility.trace('def_bal: '||to_char(p_defbal_id));
10148: hr_utility.trace('bg: '||to_char(each_bg.business_group_id));
10149: hr_utility.trace('bal_load_date: '||to_char(l_bal_load_date,'DD-MON-YYYY'));
10150: --
10143: l_bal_load_date := fnd_date.canonical_to_date('0001/01/01');
10144: end if;
10145: --
10146: hr_utility.set_location('pay_balance_pkg.initialise_run_balance', 80);
10147: hr_utility.trace('def_bal: '||to_char(p_defbal_id));
10148: hr_utility.trace('bg: '||to_char(each_bg.business_group_id));
10149: hr_utility.trace('bal_load_date: '||to_char(l_bal_load_date,'DD-MON-YYYY'));
10150: --
10151: insert into pay_balance_validation
10144: end if;
10145: --
10146: hr_utility.set_location('pay_balance_pkg.initialise_run_balance', 80);
10147: hr_utility.trace('def_bal: '||to_char(p_defbal_id));
10148: hr_utility.trace('bg: '||to_char(each_bg.business_group_id));
10149: hr_utility.trace('bal_load_date: '||to_char(l_bal_load_date,'DD-MON-YYYY'));
10150: --
10151: insert into pay_balance_validation
10152: (BALANCE_VALIDATION_ID
10145: --
10146: hr_utility.set_location('pay_balance_pkg.initialise_run_balance', 80);
10147: hr_utility.trace('def_bal: '||to_char(p_defbal_id));
10148: hr_utility.trace('bg: '||to_char(each_bg.business_group_id));
10149: hr_utility.trace('bal_load_date: '||to_char(l_bal_load_date,'DD-MON-YYYY'));
10150: --
10151: insert into pay_balance_validation
10152: (BALANCE_VALIDATION_ID
10153: ,DEFINED_BALANCE_ID
10164: from pay_balance_validation
10165: where defined_balance_id = p_defbal_id
10166: and business_group_id = each_bg.business_group_id);
10167: --
10168: hr_utility.set_location('pay_balance_pkg.initialise_run_balance', 90);
10169: --
10170: end loop;
10171: hr_utility.set_location('pay_balance_pkg.initialise_run_balance', 100);
10172: --
10167: --
10168: hr_utility.set_location('pay_balance_pkg.initialise_run_balance', 90);
10169: --
10170: end loop;
10171: hr_utility.set_location('pay_balance_pkg.initialise_run_balance', 100);
10172: --
10173: else -- l_dim_level is null
10174: --
10175: -- this is not a valid run balance defined balance, so do nothing
10173: else -- l_dim_level is null
10174: --
10175: -- this is not a valid run balance defined balance, so do nothing
10176: --
10177: hr_utility.set_location('pay_balance_pkg.initialise_run_balance', 110);
10178: end if;
10179: hr_utility.set_location('Leaving: pay_balance_pkg.initialise_run_balance', 120);
10180: end initialise_run_balance;
10181: --------------------------------------------------------------------------
10175: -- this is not a valid run balance defined balance, so do nothing
10176: --
10177: hr_utility.set_location('pay_balance_pkg.initialise_run_balance', 110);
10178: end if;
10179: hr_utility.set_location('Leaving: pay_balance_pkg.initialise_run_balance', 120);
10180: end initialise_run_balance;
10181: --------------------------------------------------------------------------
10182: -- procedure set_check_latest_balances
10183: -- This procedure sets the HRASSACT CHECK_LATEST_BALANCES global
10186: procedure set_check_latest_balances
10187: is
10188: --
10189: begin
10190: g_debug := hr_utility.debug_enabled;
10191: if g_debug then
10192: hr_utility.set_location('Entering: pay_balance_pkg.set_check_latest_balances',5);
10193: end if;
10194: --
10188: --
10189: begin
10190: g_debug := hr_utility.debug_enabled;
10191: if g_debug then
10192: hr_utility.set_location('Entering: pay_balance_pkg.set_check_latest_balances',5);
10193: end if;
10194: --
10195: HRASSACT.CHECK_LATEST_BALANCES := TRUE;
10196: --
10194: --
10195: HRASSACT.CHECK_LATEST_BALANCES := TRUE;
10196: --
10197: if g_debug then
10198: hr_utility.set_location('Exiting: pay_balance_pkg.set_check_latest_balances',5);
10199: end if;
10200: --
10201: end set_check_latest_balances;
10202: --------------------------------------------------------------------------
10207: procedure unset_check_latest_balances
10208: is
10209: --
10210: begin
10211: g_debug := hr_utility.debug_enabled;
10212: if g_debug then
10213: hr_utility.set_location('Entering: pay_balance_pkg.unset_check_latest_balances',5);
10214: end if;
10215: --
10209: --
10210: begin
10211: g_debug := hr_utility.debug_enabled;
10212: if g_debug then
10213: hr_utility.set_location('Entering: pay_balance_pkg.unset_check_latest_balances',5);
10214: end if;
10215: --
10216: HRASSACT.CHECK_LATEST_BALANCES := FALSE;
10217: --
10215: --
10216: HRASSACT.CHECK_LATEST_BALANCES := FALSE;
10217: --
10218: if g_debug then
10219: hr_utility.set_location('Exiting: pay_balance_pkg.unset_check_latest_balances',5);
10220: end if;
10221: --
10222: end unset_check_latest_balances;
10223: --------------------------------------------------------------------------
10236: --
10237: begin
10238: --
10239: if g_debug then
10240: hr_utility.set_location('enter:pay_bal_pkg.create_asg_balance',10);
10241: end if;
10242: create_all_asg_balances_full(p_asgact_id => p_asgact_id
10243: ,p_load_type => p_load_type
10244: ,p_def_bal => p_def_bal_id
10247: ,p_delta => p_delta
10248: );
10249: --
10250: if g_debug then
10251: hr_utility.set_location('leave:pay_bal_pkg.create_asg_balance',20);
10252: end if;
10253: end create_asg_balance;
10254: --
10255: --------------------------------------------------------------------------
10265: --
10266: begin
10267: --
10268: if g_debug then
10269: hr_utility.set_location('Entering:pay_bal_pkg.create_group_balance',10);
10270: end if;
10271: --
10272: create_all_grp_balances_full(p_pact_id => p_pact_id
10273: ,p_load_type => p_load_type
10277: ,p_delta => p_delta
10278: );
10279: --
10280: if g_debug then
10281: hr_utility.set_location('Leaving:pay_bal_pkg.create_group_balance',20);
10282: end if;
10283: end create_group_balance;
10284: --
10285: procedure maintain_balances_for_action(p_asg_action in number
10350:
10351:
10352: begin
10353: if g_debug then
10354: hr_utility.set_location('hrassact.reversal',50);
10355: end if;
10356: --
10357: select paa.assignment_id,
10358: ppa.effective_date,
10376: p_delta => null);
10377: --
10378: --
10379: if g_debug then
10380: hr_utility.set_location('hrassact.reversal',55);
10381: end if;
10382: --
10383: -- 2nd the group level run balances
10384: --
10382: --
10383: -- 2nd the group level run balances
10384: --
10385: if g_debug then
10386: hr_utility.set_location('hrassact.reversal',60);
10387: end if;
10388: --
10389: pay_balance_pkg.create_all_group_balances(l_pactid,
10390: 'ALL',
10444: --
10445: -- delete latest balances
10446: --
10447: if g_debug then
10448: hr_utility.set_location('hrassact.reversal', 70);
10449: end if;
10450:
10451: --
10452: -- NB could enhance to
10459: --
10460: -- Support maintenance of latest balances
10461: --
10462: if g_debug then
10463: hr_utility.set_location('hrassact.reversal', 80);
10464: end if;
10465: --
10466: hr_utility.trace('leg_code: '||leg_code);
10467: pay_core_utils.get_leg_context_iv_name('SOURCE_ID'
10462: if g_debug then
10463: hr_utility.set_location('hrassact.reversal', 80);
10464: end if;
10465: --
10466: hr_utility.trace('leg_code: '||leg_code);
10467: pay_core_utils.get_leg_context_iv_name('SOURCE_ID'
10468: ,leg_code
10469: ,l_src_iv
10470: ,l_iv_found);
10471: if (not l_iv_found) then
10472: l_src_iv := null;
10473: else
10474: l_si_needed := 'Y';
10475: hr_utility.trace('l_src_iv: '||l_src_iv);
10476: end if;
10477: --
10478: pay_core_utils.get_leg_context_iv_name('SOURCE_NUMBER'
10479: ,leg_code
10482: if (not l_num_found) then
10483: l_src_num := null;
10484: else
10485: l_sn_needed := 'Y';
10486: hr_utility.trace('l_src_num: '||l_src_num);
10487: end if;
10488: --
10489: pay_core_utils.get_leg_context_iv_name('SOURCE_NUMBER2'
10490: ,leg_code
10493: if (not l_num_found) then
10494: l_src_num2 := null;
10495: else
10496: l_sn2_needed := 'Y';
10497: hr_utility.trace('l_src_num2: '||l_src_num2);
10498: end if;
10499: pay_core_utils.get_leg_context_iv_name('ORGANIZATION_ID'
10500: ,leg_code
10501: ,l_org_id_iv
10503: if (not l_num_found) then
10504: l_org_id_iv := null;
10505: else
10506: l_org_needed := 'Y';
10507: hr_utility.trace('l_org_id_iv: '||l_org_id_iv);
10508: end if;
10509:
10510: pay_core_utils.get_leg_context_iv_name('SOURCE_TEXT',
10511: leg_code,
10656:
10657: end if;
10658: --
10659: if g_debug then
10660: hr_utility.set_location('Leaving: hrassact.reversal', 100);
10661: end if;
10662:
10663: end maintain_balances_for_action;
10664: --