[Home] [Help]
138: );
139: --
140: type t_def_bal_tab is table of t_def_bal_rec index by binary_integer;
141: --
142: g_debug boolean := hr_utility.debug_enabled;
143: g_defbal_cache t_def_bal_tab;
144: --
145: --
146: -- tables for dimension route cache
385: l_proc VARCHAR2(72);
386: l_version VARCHAR2(30);
387: l_compatibility VARCHAR2(30);
388: BEGIN
389: g_debug := hr_utility.debug_enabled;
390: IF g_debug THEN
391: l_proc := 'pay_balance_pkg.get_oracle_db_version';
392: hr_utility.set_location('Entering:'||l_proc, 5);
393: END IF;
388: BEGIN
389: g_debug := hr_utility.debug_enabled;
390: IF g_debug THEN
391: l_proc := 'pay_balance_pkg.get_oracle_db_version';
392: hr_utility.set_location('Entering:'||l_proc, 5);
393: END IF;
394: -- check to see if the g_oracle_version already exists
395: IF g_oracle_version IS NULL THEN
396: -- get the current ORACLE version and compatibility values
412: g_oracle_version :=
413: TO_NUMBER(SUBSTRB(l_version,1,INSTRB(l_version,'.',1,2)-1),'99.99');
414: END IF;
415: IF g_debug THEN
416: hr_utility.set_location('Leaving:'||l_proc, 10);
417: END IF;
418: -- return the value
419: RETURN(g_oracle_version);
420: EXCEPTION
422: -- an unexpected error was raised and is most probably caused by
423: -- the TO_NUMBER conversion. Because of this, return NULL
424: -- indicating that the Oracle Version number could NOT be assertained
425: IF g_debug THEN
426: hr_utility.set_location('Leaving:'||l_proc, 15);
427: END IF;
428: RETURN(NULL);
429: END get_oracle_db_version;
430: --------------------------------------------------------------------------
511: sql_cursor integer;
512: l_rows integer;
513: l_ora_db_vers number; -- db version number
514: begin
515: g_debug := hr_utility.debug_enabled;
516: --
517: l_return_date := to_date('0001/01/01 00:00:00', 'YYYY/MM/DD HH24:MI:SS');
518: --
519: if (p_period_type = 'YEAR') then
643: else
644: p_start_date := l_return_date;
645: end if;
646: if g_debug then
647: hr_utility.trace('Leaving pay_balance_pkg.get_period_type_start p_start_date:'||p_start_date);
648: end if;
649:
650: --
651: end get_period_type_start;
677: l_statem varchar2(2000); -- used with dynamic pl/sql
678: sql_cursor integer;
679: l_rows integer;
680: begin
681: g_debug := hr_utility.debug_enabled;
682: --
683: l_return_date := null;
684: --
685: if (p_period_type = 'YEAR') then
821: --
822: if not g_defbal_cache.exists(p_defined_balance_id) then
823: --
824: if g_debug then
825: hr_utility.set_location ('pay_balance_pkg.load_defbal_cache', 10);
826: end if;
827: select DIM.dimension_type,
828: DIM.expiry_checking_code,
829: DIM.expiry_checking_level,
968: end;
969: --
970: end if;
971: if g_debug then
972: hr_utility.set_location ('pay_balance_pkg.load_defbal_cache', 15);
973: end if;
974: --
975: p_defbal_rec := g_defbal_cache(p_defined_balance_id);
976: --
1037: v_con_value varchar2(60);
1038: v_context_found boolean;
1039:
1040: BEGIN
1041: g_debug := hr_utility.debug_enabled;
1042: if g_debug then
1043: hr_utility.set_location ('pybaluex.get_context_internal',1);
1044: hr_utility.trace(no_rows_con_tab);
1045: end if;
1039:
1040: BEGIN
1041: g_debug := hr_utility.debug_enabled;
1042: if g_debug then
1043: hr_utility.set_location ('pybaluex.get_context_internal',1);
1044: hr_utility.trace(no_rows_con_tab);
1045: end if;
1046: v_context_found := FALSE;
1047:
1040: BEGIN
1041: g_debug := hr_utility.debug_enabled;
1042: if g_debug then
1043: hr_utility.set_location ('pybaluex.get_context_internal',1);
1044: hr_utility.trace(no_rows_con_tab);
1045: end if;
1046: v_context_found := FALSE;
1047:
1048: for i in 0..no_rows_con_tab-1 LOOP
1052: if con_name_tab(i) = p_context_name then
1053: v_con_value:=con_value_tab(i); -- set return variable
1054: v_context_found := TRUE; -- set found flag
1055: if g_debug then
1056: hr_utility.trace('i= '||i);
1057: hr_utility.trace('name = '||con_name_tab(i));
1058: hr_utility.trace('value = '||con_value_tab(i));
1059: end if;
1060: EXIT; -- drop out of loop
1053: v_con_value:=con_value_tab(i); -- set return variable
1054: v_context_found := TRUE; -- set found flag
1055: if g_debug then
1056: hr_utility.trace('i= '||i);
1057: hr_utility.trace('name = '||con_name_tab(i));
1058: hr_utility.trace('value = '||con_value_tab(i));
1059: end if;
1060: EXIT; -- drop out of loop
1061: end if;
1054: v_context_found := TRUE; -- set found flag
1055: if g_debug then
1056: hr_utility.trace('i= '||i);
1057: hr_utility.trace('name = '||con_name_tab(i));
1058: hr_utility.trace('value = '||con_value_tab(i));
1059: end if;
1060: EXIT; -- drop out of loop
1061: end if;
1062: end loop;
1067: return NULL;
1068: end if;
1069: --
1070: if g_debug then
1071: hr_utility.set_location ('pybaluex.get_context_internal',3);
1072: end if;
1073: --
1074: end get_context_internal;
1075: --
1079: v_con_value varchar2(60);
1080: v_context_found boolean;
1081:
1082: BEGIN
1083: g_debug := hr_utility.debug_enabled;
1084: if g_debug then
1085: hr_utility.set_location ('pybaluex.get_context',1);
1086: hr_utility.trace(no_rows_con_tab);
1087: end if;
1081:
1082: BEGIN
1083: g_debug := hr_utility.debug_enabled;
1084: if g_debug then
1085: hr_utility.set_location ('pybaluex.get_context',1);
1086: hr_utility.trace(no_rows_con_tab);
1087: end if;
1088:
1089: v_con_value := get_context_internal(p_context_name);
1082: BEGIN
1083: g_debug := hr_utility.debug_enabled;
1084: if g_debug then
1085: hr_utility.set_location ('pybaluex.get_context',1);
1086: hr_utility.trace(no_rows_con_tab);
1087: end if;
1088:
1089: v_con_value := get_context_internal(p_context_name);
1090:
1094: null;
1095: end if;
1096: --
1097: if g_debug then
1098: hr_utility.set_location ('pybaluex.get_context',3);
1099: end if;
1100: --
1101: end get_context;
1102: --
1130: l_context_type ff_contexts.data_type%type;
1131: l_count binary_integer;
1132: l_context_found boolean;
1133: begin
1134: g_debug := hr_utility.debug_enabled;
1135: if g_debug then
1136: hr_utility.set_location ('pay_balance_pkg.set_context', 10);
1137: hr_utility.trace('p_context_name :' || p_context_name);
1138: hr_utility.trace('p_context_value :' || p_context_value);
1132: l_context_found boolean;
1133: begin
1134: g_debug := hr_utility.debug_enabled;
1135: if g_debug then
1136: hr_utility.set_location ('pay_balance_pkg.set_context', 10);
1137: hr_utility.trace('p_context_name :' || p_context_name);
1138: hr_utility.trace('p_context_value :' || p_context_value);
1139: end if;
1140: l_context_found := FALSE;
1133: begin
1134: g_debug := hr_utility.debug_enabled;
1135: if g_debug then
1136: hr_utility.set_location ('pay_balance_pkg.set_context', 10);
1137: hr_utility.trace('p_context_name :' || p_context_name);
1138: hr_utility.trace('p_context_value :' || p_context_value);
1139: end if;
1140: l_context_found := FALSE;
1141: l_context_name := upper(p_context_name);
1134: g_debug := hr_utility.debug_enabled;
1135: if g_debug then
1136: hr_utility.set_location ('pay_balance_pkg.set_context', 10);
1137: hr_utility.trace('p_context_name :' || p_context_name);
1138: hr_utility.trace('p_context_value :' || p_context_value);
1139: end if;
1140: l_context_found := FALSE;
1141: l_context_name := upper(p_context_name);
1142: --
1160: --
1161: -- its a new context, insert into tables
1162: --
1163: if g_debug then
1164: hr_utility.set_location ('pay_balance_pkg.set_context', 20);
1165: end if;
1166: select context_id,
1167: data_type
1168: into l_context_id,
1181: -- print out the current state of the tables for debug purposes:
1182: --
1183: l_count := 0;
1184: --if g_debug then
1185: -- hr_utility.trace
1186: -- ('i type context id context name context value');
1187: -- hr_utility.trace
1188: -- ('- ---- ---------- ------------ -------------');
1189: -- while (l_count < no_rows_con_tab) loop
1183: l_count := 0;
1184: --if g_debug then
1185: -- hr_utility.trace
1186: -- ('i type context id context name context value');
1187: -- hr_utility.trace
1188: -- ('- ---- ---------- ------------ -------------');
1189: -- while (l_count < no_rows_con_tab) loop
1190: -- hr_utility.trace (rpad(to_char(l_count), 3) ||
1191: -- rpad(con_type_tab(l_count), 6) ||
1186: -- ('i type context id context name context value');
1187: -- hr_utility.trace
1188: -- ('- ---- ---------- ------------ -------------');
1189: -- while (l_count < no_rows_con_tab) loop
1190: -- hr_utility.trace (rpad(to_char(l_count), 3) ||
1191: -- rpad(con_type_tab(l_count), 6) ||
1192: -- rpad(con_id_tab(l_count), 12) ||
1193: -- rpad(con_name_tab(l_count), 23) ||
1194: -- con_value_tab(l_count));
1375: --
1376: ------------------------------- run_db_item -------------------------------
1377: --
1378: begin
1379: g_debug := hr_utility.debug_enabled;
1380: if g_debug then
1381: hr_utility.set_location ('pay_balance_pkg.run_db_item', 1);
1382: hr_utility.trace ('DB_item = ' || p_database_name);
1383: hr_utility.trace ('bus_grp = ' || to_char (p_bus_group_id));
1377: --
1378: begin
1379: g_debug := hr_utility.debug_enabled;
1380: if g_debug then
1381: hr_utility.set_location ('pay_balance_pkg.run_db_item', 1);
1382: hr_utility.trace ('DB_item = ' || p_database_name);
1383: hr_utility.trace ('bus_grp = ' || to_char (p_bus_group_id));
1384: hr_utility.trace ('leg_code = ' || p_legislation_code);
1385: end if;
1378: begin
1379: g_debug := hr_utility.debug_enabled;
1380: if g_debug then
1381: hr_utility.set_location ('pay_balance_pkg.run_db_item', 1);
1382: hr_utility.trace ('DB_item = ' || p_database_name);
1383: hr_utility.trace ('bus_grp = ' || to_char (p_bus_group_id));
1384: hr_utility.trace ('leg_code = ' || p_legislation_code);
1385: end if;
1386: --
1379: g_debug := hr_utility.debug_enabled;
1380: if g_debug then
1381: hr_utility.set_location ('pay_balance_pkg.run_db_item', 1);
1382: hr_utility.trace ('DB_item = ' || p_database_name);
1383: hr_utility.trace ('bus_grp = ' || to_char (p_bus_group_id));
1384: hr_utility.trace ('leg_code = ' || p_legislation_code);
1385: end if;
1386: --
1387: -- Get all the data to build up the route
1380: if g_debug then
1381: hr_utility.set_location ('pay_balance_pkg.run_db_item', 1);
1382: hr_utility.trace ('DB_item = ' || p_database_name);
1383: hr_utility.trace ('bus_grp = ' || to_char (p_bus_group_id));
1384: hr_utility.trace ('leg_code = ' || p_legislation_code);
1385: end if;
1386: --
1387: -- Get all the data to build up the route
1388: --
1446: -- Raise an error, as there are contexts that have not been set up in
1447: -- the pl/sql tables that are required by the route.
1448: --
1449: if g_debug then
1450: hr_utility.set_location ('pay_balance_pkg.run_db_item', 10);
1451: end if;
1452: select context_name
1453: into l_context_name
1454: from ff_contexts
1453: into l_context_name
1454: from ff_contexts
1455: where context_id = c1rec.context_id;
1456: --
1457: hr_utility.set_message(801, 'HR_7271_PAY_CONTEXT_MISSING');
1458: hr_utility.set_message_token ('CONTEXT_NAME', l_context_name);
1459: hr_utility.raise_error;
1460: end if;
1461: end loop; -- end of for c1rec loop
1454: from ff_contexts
1455: where context_id = c1rec.context_id;
1456: --
1457: hr_utility.set_message(801, 'HR_7271_PAY_CONTEXT_MISSING');
1458: hr_utility.set_message_token ('CONTEXT_NAME', l_context_name);
1459: hr_utility.raise_error;
1460: end if;
1461: end loop; -- end of for c1rec loop
1462: --
1455: where context_id = c1rec.context_id;
1456: --
1457: hr_utility.set_message(801, 'HR_7271_PAY_CONTEXT_MISSING');
1458: hr_utility.set_message_token ('CONTEXT_NAME', l_context_name);
1459: hr_utility.raise_error;
1460: end if;
1461: end loop; -- end of for c1rec loop
1462: --
1463: -- The following loop searches through and replaces all where clause fillers
1478: --
1479: -- Print the route text out for debug purposes. Normally keep this line
1480: -- commented out, as for a large route it causes an error in the trace
1481: -- utility.
1482: -- hr_utility.trace (l_text);
1483: --
1484: --
1485: -- now build up the full SQL statement:
1486: --
1559: -- 6. Get the sql value (providing there are rows returned)
1560: -- 7. Close the dynamic sql cursor
1561: --
1562: if g_debug then
1563: hr_utility.set_location ('pay_balance_pkg.run_db_item', 15);
1564: end if;
1565: sql_cursor := dbms_sql.open_cursor; -- step 1
1566: --
1567: if g_debug then
1564: end if;
1565: sql_cursor := dbms_sql.open_cursor; -- step 1
1566: --
1567: if g_debug then
1568: hr_utility.set_location ('pay_balance_pkg.run_db_item', 20);
1569: end if;
1570: dbms_sql.parse(sql_cursor, l_text, dbms_sql.v7); -- step 2
1571: --
1572: -- -- step 3
1578: l_context_found := FALSE;
1579: while (l_count < no_rows_con_tab) loop
1580: if (con_id_tab(l_count) = route_contexts_cache.cxt_id(l_cxt_num)) then
1581: if g_debug then
1582: hr_utility.trace (con_name_tab (l_count) ||' = '||
1583: con_value_tab (l_count));
1584: end if;
1585: --
1586: -- Found a bind variable
1602: -- Raise an error, as there are contexts that have not been set up in
1603: -- the pl/sql tables that are required by the route.
1604: --
1605: if g_debug then
1606: hr_utility.set_location ('pay_balance_pkg.run_db_item', 101);
1607: end if;
1608: select context_name
1609: into l_context_name
1610: from ff_contexts
1609: into l_context_name
1610: from ff_contexts
1611: where context_id = route_contexts_cache.cxt_id(l_cxt_num);
1612: --
1613: hr_utility.set_message(801, 'HR_7271_PAY_CONTEXT_MISSING');
1614: hr_utility.set_message_token ('CONTEXT_NAME', l_context_name);
1615: hr_utility.raise_error;
1616: end if;
1617: end loop; -- end of for route contexts loop
1610: from ff_contexts
1611: where context_id = route_contexts_cache.cxt_id(l_cxt_num);
1612: --
1613: hr_utility.set_message(801, 'HR_7271_PAY_CONTEXT_MISSING');
1614: hr_utility.set_message_token ('CONTEXT_NAME', l_context_name);
1615: hr_utility.raise_error;
1616: end if;
1617: end loop; -- end of for route contexts loop
1618: --
1611: where context_id = route_contexts_cache.cxt_id(l_cxt_num);
1612: --
1613: hr_utility.set_message(801, 'HR_7271_PAY_CONTEXT_MISSING');
1614: hr_utility.set_message_token ('CONTEXT_NAME', l_context_name);
1615: hr_utility.raise_error;
1616: end if;
1617: end loop; -- end of for route contexts loop
1618: --
1619: -- Now bind the where clause fillers (the 'U' values)
1623: route_parameters_cache.par_val(l_par_num));
1624: end loop;
1625: --
1626: if g_debug then
1627: hr_utility.set_location ('pay_balance_pkg.run_db_item', 25);
1628: end if;
1629: dbms_sql.define_column (sql_cursor, 1, p_db_output, 240); -- step 4
1630: --
1631: if g_debug then
1628: end if;
1629: dbms_sql.define_column (sql_cursor, 1, p_db_output, 240); -- step 4
1630: --
1631: if g_debug then
1632: hr_utility.set_location ('pay_balance_pkg.run_db_item', 30);
1633: end if;
1634: l_rows := dbms_sql.execute_and_fetch (sql_cursor, false); -- step 5
1635: --
1636: if (l_rows = 1) then
1634: l_rows := dbms_sql.execute_and_fetch (sql_cursor, false); -- step 5
1635: --
1636: if (l_rows = 1) then
1637: if g_debug then
1638: hr_utility.set_location ('pay_balance_pkg.run_db_item', 35);
1639: end if;
1640: dbms_sql.column_value (sql_cursor, 1, p_db_output); -- step 6
1641: --
1642: if g_debug then
1639: end if;
1640: dbms_sql.column_value (sql_cursor, 1, p_db_output); -- step 6
1641: --
1642: if g_debug then
1643: hr_utility.set_location ('pay_balance_pkg.run_db_item', 40);
1644: end if;
1645: dbms_sql.close_cursor(sql_cursor); -- step 7
1646: --
1647: if g_debug then
1644: end if;
1645: dbms_sql.close_cursor(sql_cursor); -- step 7
1646: --
1647: if g_debug then
1648: hr_utility.trace ('DB value = ' || p_db_output);
1649: end if;
1650: --
1651: elsif (l_rows = 0) then
1652: dbms_sql.close_cursor(sql_cursor);
1654: --
1655: -- its ok to not find a row
1656: --
1657: if g_debug then
1658: hr_utility.set_location ('pay_balance_pkg.run_db_item', 45);
1659: end if;
1660: p_db_output := null;
1661: else
1662: --
1662: --
1663: -- Error, no rows found when the entity has been defined to always
1664: -- find a row.
1665: --
1666: hr_utility.set_message(801, 'HR_7272_PAY_NO_ROWS_RETURNED');
1667: hr_utility.set_message_token ('DATABASE_NAME', p_database_name);
1668: hr_utility.raise_error;
1669: end if;
1670: else
1663: -- Error, no rows found when the entity has been defined to always
1664: -- find a row.
1665: --
1666: hr_utility.set_message(801, 'HR_7272_PAY_NO_ROWS_RETURNED');
1667: hr_utility.set_message_token ('DATABASE_NAME', p_database_name);
1668: hr_utility.raise_error;
1669: end if;
1670: else
1671: --
1664: -- find a row.
1665: --
1666: hr_utility.set_message(801, 'HR_7272_PAY_NO_ROWS_RETURNED');
1667: hr_utility.set_message_token ('DATABASE_NAME', p_database_name);
1668: hr_utility.raise_error;
1669: end if;
1670: else
1671: --
1672: -- More than 1 row have been returned. We must error as DB items can
1672: -- More than 1 row have been returned. We must error as DB items can
1673: -- only return 1 row.
1674: --
1675: dbms_sql.close_cursor(sql_cursor);
1676: hr_utility.set_message(801, 'HR_7273_PAY_MORE_THAN_1_ROW');
1677: hr_utility.raise_error;
1678: end if;
1679: return p_db_output;
1680: exception
1673: -- only return 1 row.
1674: --
1675: dbms_sql.close_cursor(sql_cursor);
1676: hr_utility.set_message(801, 'HR_7273_PAY_MORE_THAN_1_ROW');
1677: hr_utility.raise_error;
1678: end if;
1679: return p_db_output;
1680: exception
1681: --
1685: --
1686: When Others Then
1687: l_error_text := sqlerrm;
1688: if g_debug then
1689: Hr_Utility.Set_Location('run_db_item', 100);
1690: end if;
1691: If (dbms_sql.is_open(sql_cursor)) then
1692: if g_debug then
1693: Hr_Utility.Set_Location('run_db_item', 105);
1689: Hr_Utility.Set_Location('run_db_item', 100);
1690: end if;
1691: If (dbms_sql.is_open(sql_cursor)) then
1692: if g_debug then
1693: Hr_Utility.Set_Location('run_db_item', 105);
1694: end if;
1695: dbms_sql.close_cursor(sql_cursor);
1696: End If;
1697: hr_utility.set_message(801, 'HR_7276_PAY_FAILED_DB_ITEM');
1693: Hr_Utility.Set_Location('run_db_item', 105);
1694: end if;
1695: dbms_sql.close_cursor(sql_cursor);
1696: End If;
1697: hr_utility.set_message(801, 'HR_7276_PAY_FAILED_DB_ITEM');
1698: hr_utility.set_message_token ('ERROR_MESSAGE', l_error_text);
1699: hr_utility.raise_error;
1700: end run_db_item;
1701: --
1694: end if;
1695: dbms_sql.close_cursor(sql_cursor);
1696: End If;
1697: hr_utility.set_message(801, 'HR_7276_PAY_FAILED_DB_ITEM');
1698: hr_utility.set_message_token ('ERROR_MESSAGE', l_error_text);
1699: hr_utility.raise_error;
1700: end run_db_item;
1701: --
1702: --
1695: dbms_sql.close_cursor(sql_cursor);
1696: End If;
1697: hr_utility.set_message(801, 'HR_7276_PAY_FAILED_DB_ITEM');
1698: hr_utility.set_message_token ('ERROR_MESSAGE', l_error_text);
1699: hr_utility.raise_error;
1700: end run_db_item;
1701: --
1702: --
1703: --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1773: l_select_clause || ')';
1774: end if;
1775: --
1776: -- if g_debug then
1777: -- hr_utility.trace(l_select_clause);
1778: -- end if;
1779: --
1780: --
1781: -- Now setup the balance type joining details.
1789: begin
1790: --
1791: l_select_component := ', '||p_balance_type_column;
1792: if g_debug then
1793: hr_utility.trace(l_select_component);
1794: end if;
1795: --
1796: l_select_clause := l_select_clause||l_select_component;
1797: l_from_clause := l_from_clause||' and '||p_balance_type_column||' in (';
1796: l_select_clause := l_select_clause||l_select_component;
1797: l_from_clause := l_from_clause||' and '||p_balance_type_column||' in (';
1798: if g_debug then
1799: if length(l_from_clause) <= MAX_TRACE_SIZE then
1800: hr_utility.trace(l_from_clause);
1801: end if;
1802: end if;
1803: --
1804: -- Now put the full statement in the from clause
1841: (MAX_DYN_SQL_SIZE - 160)) then
1842: --
1843: if g_debug then
1844: if l_comp_length <= MAX_TRACE_SIZE then
1845: hr_utility.trace(l_from_component);
1846: end if;
1847: end if;
1848: l_from_clause := l_from_clause||l_from_component;
1849: --
1863: l_from_component := ' ) group by '||p_balance_type_column;
1864:
1865: if g_debug then
1866: if length(l_from_component) <= MAX_TRACE_SIZE then
1867: hr_utility.trace(l_from_component);
1868: end if;
1869: end if;
1870: l_from_clause := l_from_clause||l_from_component;
1871: --
1875: if (p_balance_type_column is null) then
1876: --
1877: if g_debug then
1878: if length(l_from_clause) <= MAX_TRACE_SIZE then
1879: hr_utility.trace(l_from_clause);
1880: end if;
1881: end if;
1882: --
1883: -- Now put the full statement in the from clause
1889: --
1890: --
1891: if g_debug then
1892: if length(l_from_clause) <= MAX_TRACE_SIZE then
1893: hr_utility.trace(l_from_clause);
1894: end if;
1895: end if;
1896: --
1897: -- Now put the full statement in the from clause
1899: --
1900: l_from_component := ' and '||p_balance_type_column||' = :U1';
1901: if g_debug then
1902: if length(l_from_component) <= MAX_TRACE_SIZE then
1903: hr_utility.trace(l_from_component);
1904: end if;
1905: end if;
1906: l_from_clause := l_from_clause||l_from_component;
1907: --
1908: if (p_decode_required = TRUE) then
1909: l_from_component := ' + decode(TARGET.input_value_id, 0, 0, 0)';
1910: if g_debug then
1911: if length(l_from_component) <= MAX_TRACE_SIZE then
1912: hr_utility.trace(l_from_component);
1913: end if;
1914: end if;
1915: l_from_clause := l_from_clause||l_from_component;
1916: end if;
1991: l_route_context_usage_tab t_route_context_usage_tab;
1992: begin
1993: -- bug 13692929 if only context is assignment_action_id and only placeholder is balance_type_id use NDS
1994: if g_debug then
1995: hr_utility.set_location ('pay_balance_pkg.process_balance_statement', 10);
1996: end if;
1997: l_route_context_usage_tab.delete;
1998: cnt := 1;
1999: l_simplebind :=TRUE;
2004: l_bt_pos := instr(p_statement,':U');
2005: end if;
2006:
2007: for c1rec in ro_context (p_route_id) loop
2008: --hr_utility.trace('context_id:'||c1rec.context_id||' sequence_no:'||c1rec.sequence_no||
2009: -- ' context_name:'||c1rec.context_name||' cnt:'||cnt);
2010: l_route_context_usage_tab(cnt).context_id := c1rec.context_id;
2011: l_route_context_usage_tab(cnt).sequence_no := c1rec.sequence_no;
2012: l_route_context_usage_tab(cnt).context_name := c1rec.context_name;
2047:
2048: --For Bug 14666688 : end
2049:
2050: if g_debug then
2051: hr_utility.trace('NDS rr fetch. p_baltype_id:'||p_balance_type_id||
2052: ' asg_action_id:'||l_assact_id ||
2053: ' p_balance_value:'||p_balance_value);
2054: end if;
2055:
2055:
2056:
2057: else --{ complex balance -process using dbms_sql
2058: if g_debug then
2059: hr_utility.set_location ('pay_balance_pkg.process_balance_statement. using dbms_sql', 15);
2060: end if;
2061:
2062: --
2063: -- Now execute the SQL statement using dynamic pl/sql:
2072: -- 6. Get the sql value (providing there are rows returned)
2073: -- 7. Close the dynamic sql cursor
2074: --
2075: if g_debug then
2076: hr_utility.set_location ('pay_balance_pkg.process_balance_statement', 35);
2077: end if;
2078: sql_cursor := dbms_sql.open_cursor; -- step 1
2079: --
2080: if g_debug then
2077: end if;
2078: sql_cursor := dbms_sql.open_cursor; -- step 1
2079: --
2080: if g_debug then
2081: hr_utility.set_location ('pay_balance_pkg.process_balance_statement', 40);
2082: end if;
2083: dbms_sql.parse(sql_cursor, p_statement, dbms_sql.v7); -- step 2
2084: --
2085: -- -- step 3
2088: --
2089: -- for c1rec in ro_context (p_route_id) loop
2090: for a in l_route_context_usage_tab.FIRST .. l_route_context_usage_tab.LAST loop
2091: if g_debug then
2092: hr_utility.set_location ('pay_balance_pkg.process_balance_statement', 45);
2093: end if;
2094: l_count := 0;
2095: l_context_found := FALSE;
2096: while (l_count < no_rows_con_tab) loop
2095: l_context_found := FALSE;
2096: while (l_count < no_rows_con_tab) loop
2097: if (con_id_tab(l_count) = l_route_context_usage_tab(a).context_id) then
2098: --if g_debug then
2099: -- hr_utility.trace (con_name_tab (l_count) ||' = '||
2100: -- con_value_tab (l_count));
2101: --end if;
2102: --
2103: -- Found a bind variable
2103: -- Found a bind variable
2104: --
2105: if (con_type_tab (l_count) = 'D') then
2106: if g_debug then
2107: hr_utility.set_location ('pay_balance_pkg.process_balance_statement', 50);
2108: end if;
2109: dbms_sql.bind_variable
2110: (sql_cursor,
2111: con_name_tab(l_count),
2111: con_name_tab(l_count),
2112: fnd_date.canonical_to_date(ltrim(rtrim(con_value_tab (l_count)))));
2113: else
2114: if g_debug then
2115: hr_utility.set_location ('pay_balance_pkg.process_balance_statement', 55);
2116: end if;
2117: dbms_sql.bind_variable
2118: (sql_cursor,
2119: con_name_tab(l_count),
2129: -- Raise an error, as there are contexts that have not been set up in
2130: -- the pl/sql tables that are required by the route.
2131: --
2132: if g_debug then
2133: hr_utility.set_location ('pay_balance_pkg.process_balance_statement', 60);
2134: end if;
2135: --
2136: hr_utility.set_message(801, 'HR_7271_PAY_CONTEXT_MISSING');
2137: hr_utility.set_message_token ('CONTEXT_NAME', l_route_context_usage_tab(a).context_name);
2132: if g_debug then
2133: hr_utility.set_location ('pay_balance_pkg.process_balance_statement', 60);
2134: end if;
2135: --
2136: hr_utility.set_message(801, 'HR_7271_PAY_CONTEXT_MISSING');
2137: hr_utility.set_message_token ('CONTEXT_NAME', l_route_context_usage_tab(a).context_name);
2138: hr_utility.raise_error;
2139: end if;
2140: end loop; -- end of for c1rec loop
2133: hr_utility.set_location ('pay_balance_pkg.process_balance_statement', 60);
2134: end if;
2135: --
2136: hr_utility.set_message(801, 'HR_7271_PAY_CONTEXT_MISSING');
2137: hr_utility.set_message_token ('CONTEXT_NAME', l_route_context_usage_tab(a).context_name);
2138: hr_utility.raise_error;
2139: end if;
2140: end loop; -- end of for c1rec loop
2141: --
2134: end if;
2135: --
2136: hr_utility.set_message(801, 'HR_7271_PAY_CONTEXT_MISSING');
2137: hr_utility.set_message_token ('CONTEXT_NAME', l_route_context_usage_tab(a).context_name);
2138: hr_utility.raise_error;
2139: end if;
2140: end loop; -- end of for c1rec loop
2141: --
2142: -- Now bind the where clause fillers (the 'U' values)
2142: -- Now bind the where clause fillers (the 'U' values)
2143: --
2144: if (p_batch_mode = FALSE) then
2145: if g_debug then
2146: hr_utility.set_location ('pay_balance_pkg.process_balance_statement', 62);
2147: hr_utility.trace('p_balance_type_id = '||p_balance_type_id);
2148: end if;
2149: dbms_sql.bind_variable (sql_cursor, 'U1',
2150: p_balance_type_id);
2143: --
2144: if (p_batch_mode = FALSE) then
2145: if g_debug then
2146: hr_utility.set_location ('pay_balance_pkg.process_balance_statement', 62);
2147: hr_utility.trace('p_balance_type_id = '||p_balance_type_id);
2148: end if;
2149: dbms_sql.bind_variable (sql_cursor, 'U1',
2150: p_balance_type_id);
2151: end if;
2156: l_simplebind := FALSE;
2157: end if;
2158: --
2159: if g_debug then
2160: hr_utility.set_location ('pay_balance_pkg.process_balance_statement', 65);
2161: end if;
2162: dbms_sql.define_column (sql_cursor, 1, l_db_output, 60); -- step 4
2163: if (p_batch_mode = TRUE) then
2164: dbms_sql.define_column (sql_cursor, 2, l_balance_type_id);
2164: dbms_sql.define_column (sql_cursor, 2, l_balance_type_id);
2165: end if;
2166: --
2167: if g_debug then
2168: hr_utility.set_location ('pay_balance_pkg.process_balance_statement', 70);
2169: end if;
2170: ignore := dbms_sql.execute(sql_cursor);
2171: --
2172: l_retrieve := TRUE;
2175: --
2176: l_rows := dbms_sql.fetch_rows(sql_cursor);
2177: --
2178: if g_debug then
2179: hr_utility.set_location ('pay_balance_pkg.process_balance_statement', 80);
2180: end if;
2181: --
2182: if (l_rows > 0) then
2183: --
2181: --
2182: if (l_rows > 0) then
2183: --
2184: if g_debug then
2185: hr_utility.set_location ('pay_balance_pkg.process_balance_statement', 85);
2186: end if;
2187: dbms_sql.column_value (sql_cursor, 1, l_db_output);
2188: if (p_batch_mode = TRUE) then
2189: dbms_sql.column_value (sql_cursor, 2, l_balance_type_id);
2194: if (p_balance_list(cnt).balance_type_id = l_balance_type_id) then
2195: p_balance_list(cnt).balance_value :=
2196: fnd_number.canonical_to_number(l_db_output);
2197: if g_debug then
2198: hr_utility.trace('****Bal = '||l_balance_type_id||' Value = '||
2199: l_db_output);
2200: end if;
2201: end if;
2202: end loop;
2205: --
2206: -- Have we already got a value
2207: --
2208: if g_debug then
2209: hr_utility.set_location ('pay_balance_pkg.process_balance_statement', 87);
2210: end if;
2211: if (l_value_retrieved = TRUE) then
2212: if g_debug then
2213: hr_utility.set_location ('pay_balance_pkg.process_balance_statement', 90);
2209: hr_utility.set_location ('pay_balance_pkg.process_balance_statement', 87);
2210: end if;
2211: if (l_value_retrieved = TRUE) then
2212: if g_debug then
2213: hr_utility.set_location ('pay_balance_pkg.process_balance_statement', 90);
2214: end if;
2215: dbms_sql.close_cursor(sql_cursor);
2216: hr_utility.set_message(801, 'HR_7273_PAY_MORE_THAN_1_ROW');
2217: hr_utility.raise_error;
2212: if g_debug then
2213: hr_utility.set_location ('pay_balance_pkg.process_balance_statement', 90);
2214: end if;
2215: dbms_sql.close_cursor(sql_cursor);
2216: hr_utility.set_message(801, 'HR_7273_PAY_MORE_THAN_1_ROW');
2217: hr_utility.raise_error;
2218: end if;
2219: if g_debug then
2220: hr_utility.trace('process_balance_statement Value = '||l_db_output);
2213: hr_utility.set_location ('pay_balance_pkg.process_balance_statement', 90);
2214: end if;
2215: dbms_sql.close_cursor(sql_cursor);
2216: hr_utility.set_message(801, 'HR_7273_PAY_MORE_THAN_1_ROW');
2217: hr_utility.raise_error;
2218: end if;
2219: if g_debug then
2220: hr_utility.trace('process_balance_statement Value = '||l_db_output);
2221: end if;
2216: hr_utility.set_message(801, 'HR_7273_PAY_MORE_THAN_1_ROW');
2217: hr_utility.raise_error;
2218: end if;
2219: if g_debug then
2220: hr_utility.trace('process_balance_statement Value = '||l_db_output);
2221: end if;
2222: p_balance_value := fnd_number.canonical_to_number(l_db_output);
2223: --
2224: end if;
2248: --
2249: When Others Then
2250: l_error_text := sqlerrm;
2251: if g_debug then
2252: Hr_Utility.Set_Location('pay_balance_pkg.process_balance_statement', 100);
2253: end if;
2254: If (dbms_sql.is_open(sql_cursor)) then
2255: if g_debug then
2256: Hr_Utility.Set_Location('pay_balance_pkg.process_balance_statement', 105);
2252: Hr_Utility.Set_Location('pay_balance_pkg.process_balance_statement', 100);
2253: end if;
2254: If (dbms_sql.is_open(sql_cursor)) then
2255: if g_debug then
2256: Hr_Utility.Set_Location('pay_balance_pkg.process_balance_statement', 105);
2257: end if;
2258: dbms_sql.close_cursor(sql_cursor);
2259: End If;
2260: hr_utility.set_message(801, 'HR_7276_PAY_FAILED_DB_ITEM');
2256: Hr_Utility.Set_Location('pay_balance_pkg.process_balance_statement', 105);
2257: end if;
2258: dbms_sql.close_cursor(sql_cursor);
2259: End If;
2260: hr_utility.set_message(801, 'HR_7276_PAY_FAILED_DB_ITEM');
2261: hr_utility.set_message_token ('ERROR_MESSAGE', l_error_text);
2262: hr_utility.raise_error;
2263: --
2264: end process_balance_statement;
2257: end if;
2258: dbms_sql.close_cursor(sql_cursor);
2259: End If;
2260: hr_utility.set_message(801, 'HR_7276_PAY_FAILED_DB_ITEM');
2261: hr_utility.set_message_token ('ERROR_MESSAGE', l_error_text);
2262: hr_utility.raise_error;
2263: --
2264: end process_balance_statement;
2265: --
2258: dbms_sql.close_cursor(sql_cursor);
2259: End If;
2260: hr_utility.set_message(801, 'HR_7276_PAY_FAILED_DB_ITEM');
2261: hr_utility.set_message_token ('ERROR_MESSAGE', l_error_text);
2262: hr_utility.raise_error;
2263: --
2264: end process_balance_statement;
2265: --
2266: --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
2321: l_start_ptr number;
2322: --
2323: begin
2324: if g_debug then
2325: hr_utility.set_location ('pay_balance_pkg.run_rr_route', 1);
2326: end if;
2327: --
2328: -- If we are in batch mode then initialise the returns
2329: --
2366: -- Raise an error, as there are contexts that have not been set up in
2367: -- the pl/sql tables that are required by the route.
2368: --
2369: if g_debug then
2370: hr_utility.set_location ('pay_balance_pkg.run_rr_route', 10);
2371: end if;
2372: select context_name
2373: into l_context_name
2374: from ff_contexts
2373: into l_context_name
2374: from ff_contexts
2375: where context_id = c1rec.context_id;
2376: --
2377: hr_utility.set_message(801, 'HR_7271_PAY_CONTEXT_MISSING');
2378: hr_utility.set_message_token ('CONTEXT_NAME', l_context_name);
2379: hr_utility.raise_error;
2380: end if;
2381: end loop; -- end of for c1rec loop
2374: from ff_contexts
2375: where context_id = c1rec.context_id;
2376: --
2377: hr_utility.set_message(801, 'HR_7271_PAY_CONTEXT_MISSING');
2378: hr_utility.set_message_token ('CONTEXT_NAME', l_context_name);
2379: hr_utility.raise_error;
2380: end if;
2381: end loop; -- end of for c1rec loop
2382: --
2375: where context_id = c1rec.context_id;
2376: --
2377: hr_utility.set_message(801, 'HR_7271_PAY_CONTEXT_MISSING');
2378: hr_utility.set_message_token ('CONTEXT_NAME', l_context_name);
2379: hr_utility.raise_error;
2380: end if;
2381: end loop; -- end of for c1rec loop
2382: --
2383: -- The following loop searches through and replaces all where clause fillers
2392: --
2393: -- Print the route text out for debug purposes. Normally keep this line
2394: -- commented out, as for a large route it causes an error in the trace
2395: -- utility.
2396: -- hr_utility.trace (l_from_clause);
2397: --
2398: --
2399: -- Get action Parameter.
2400: --
2400: --
2401: if (cached = FALSE) then
2402: cached := TRUE;
2403: if g_debug then
2404: hr_utility.set_location('pay_balance_pkg.run_rr_route', 20);
2405: end if;
2406: l_ora_db_vers := get_oracle_db_version;
2407: if (nvl(l_ora_db_vers, 0) < 9.0) then
2408: g_low_volume := 'Y';
2573:
2574: --
2575: BEGIN
2576: if g_debug then
2577: hr_utility.set_location ('pay_balance_pkg.get_run_balance', 1);
2578: hr_utility.trace ('User name: '|| p_user_name);
2579: hr_utility.trace ('bus_grp: ' || to_char (p_business_group_id));
2580: hr_utility.trace ('leg_code: ' || p_legislation_code);
2581: hr_utility.trace ('p_route_type: ' || p_route_type);
2574: --
2575: BEGIN
2576: if g_debug then
2577: hr_utility.set_location ('pay_balance_pkg.get_run_balance', 1);
2578: hr_utility.trace ('User name: '|| p_user_name);
2579: hr_utility.trace ('bus_grp: ' || to_char (p_business_group_id));
2580: hr_utility.trace ('leg_code: ' || p_legislation_code);
2581: hr_utility.trace ('p_route_type: ' || p_route_type);
2582: end if;
2575: BEGIN
2576: if g_debug then
2577: hr_utility.set_location ('pay_balance_pkg.get_run_balance', 1);
2578: hr_utility.trace ('User name: '|| p_user_name);
2579: hr_utility.trace ('bus_grp: ' || to_char (p_business_group_id));
2580: hr_utility.trace ('leg_code: ' || p_legislation_code);
2581: hr_utility.trace ('p_route_type: ' || p_route_type);
2582: end if;
2583: --
2576: if g_debug then
2577: hr_utility.set_location ('pay_balance_pkg.get_run_balance', 1);
2578: hr_utility.trace ('User name: '|| p_user_name);
2579: hr_utility.trace ('bus_grp: ' || to_char (p_business_group_id));
2580: hr_utility.trace ('leg_code: ' || p_legislation_code);
2581: hr_utility.trace ('p_route_type: ' || p_route_type);
2582: end if;
2583: --
2584: -- Get all the data to build up the route
2577: hr_utility.set_location ('pay_balance_pkg.get_run_balance', 1);
2578: hr_utility.trace ('User name: '|| p_user_name);
2579: hr_utility.trace ('bus_grp: ' || to_char (p_business_group_id));
2580: hr_utility.trace ('leg_code: ' || p_legislation_code);
2581: hr_utility.trace ('p_route_type: ' || p_route_type);
2582: end if;
2583: --
2584: -- Get all the data to build up the route
2585: -- select definition and data type are hard coded for now
2650: -- Raise an error, as there are contexts that have not been set up in
2651: -- the pl/sql tables that are required by the route.
2652: --
2653: if g_debug then
2654: hr_utility.set_location ('pay_balance_pkg.get_run_balance', 10);
2655: end if;
2656: select context_name
2657: into l_context_name
2658: from ff_contexts
2657: into l_context_name
2658: from ff_contexts
2659: where context_id = c1rec.context_id;
2660: --
2661: hr_utility.set_message(801, 'HR_7271_PAY_CONTEXT_MISSING');
2662: hr_utility.set_message_token ('CONTEXT_NAME', l_context_name);
2663: hr_utility.raise_error;
2664: end if;
2665: end loop; -- end of for c1rec loop
2658: from ff_contexts
2659: where context_id = c1rec.context_id;
2660: --
2661: hr_utility.set_message(801, 'HR_7271_PAY_CONTEXT_MISSING');
2662: hr_utility.set_message_token ('CONTEXT_NAME', l_context_name);
2663: hr_utility.raise_error;
2664: end if;
2665: end loop; -- end of for c1rec loop
2666: --
2659: where context_id = c1rec.context_id;
2660: --
2661: hr_utility.set_message(801, 'HR_7271_PAY_CONTEXT_MISSING');
2662: hr_utility.set_message_token ('CONTEXT_NAME', l_context_name);
2663: hr_utility.raise_error;
2664: end if;
2665: end loop; -- end of for c1rec loop
2666: --
2667: -- The following loop searches through and replaces all where clause fillers
2691: --
2692: -- Print the route text out for debug purposes. Normally keep this line
2693: -- commented out, as for a large route it causes an error in the trace
2694: -- utility.
2695: -- hr_utility.trace (l_text);
2696: --
2697: --
2698: -- now build up the full SQL statement:
2699: --
2710: ' */ NVL(SUM(prb.balance_value),0) FROM ' || l_text;
2711: end if;
2712: --
2713: if g_debug then
2714: hr_utility.set_location('pay_balance_pkg.get_run_balance', 15);
2715: end if;
2716: --
2717: elsif p_route_type = 'RR' then
2718: --
2718: --
2719: -- Should never get here since all RRs are handled by run_rr_route now.
2720: --
2721: if g_debug then
2722: hr_utility.set_location('pay_balance_pkg.get_run_balance', 20);
2723: end if;
2724: --
2725: hr_general.assert_condition(false);
2726: --
2730: -- are mapped in the route and call route sql with the correct sequence of binds
2731: if l_simplebind then --{ simplebind
2732: if l_assact_pos > l_bt_pos and l_assact_pos > l_bd_pos then --{ assact_3rd
2733: if g_debug then
2734: hr_utility.set_location ('pay_balance_pkg.get_run_balance', 22);
2735: end if;
2736:
2737: l_var3 := l_assact_id;
2738: if l_bt_pos < l_bd_pos then
2736:
2737: l_var3 := l_assact_id;
2738: if l_bt_pos < l_bd_pos then
2739: if g_debug then
2740: hr_utility.set_location ('pay_balance_pkg.get_run_balance', 24);
2741: end if;
2742: l_var1 := l_bt_val;
2743: l_var2 := l_bd_val;
2744: else
2747: end if;
2748: -- case statements for all combinations of bind variable order
2749: else if l_assact_pos < l_bt_pos and l_assact_pos < l_bd_pos then --{ assact_1st
2750: if g_debug then
2751: hr_utility.set_location ('pay_balance_pkg.get_run_balance', 26);
2752: end if;
2753:
2754: l_var1 := l_assact_id;
2755: if l_bt_pos < l_bd_pos then
2753:
2754: l_var1 := l_assact_id;
2755: if l_bt_pos < l_bd_pos then
2756: if g_debug then
2757: hr_utility.set_location ('pay_balance_pkg.get_run_balance', 28);
2758: end if;
2759: l_var2 := l_bt_val;
2760: l_var3 := l_bd_val;
2761: else
2763: l_var2 := l_bd_val;
2764: end if;
2765: else if l_assact_pos < l_bt_pos and l_assact_pos > l_bd_pos then --{ assact_2nd
2766: if g_debug then
2767: hr_utility.set_location ('pay_balance_pkg.get_run_balance', 30);
2768: end if;
2769:
2770: l_var2 := l_assact_id;
2771: if l_bt_pos < l_bd_pos then
2769:
2770: l_var2 := l_assact_id;
2771: if l_bt_pos < l_bd_pos then
2772: if g_debug then
2773: hr_utility.set_location ('pay_balance_pkg.get_run_balance', 32);
2774: end if;
2775: l_var1 := l_bt_val;
2776: l_var3 := l_bd_val;
2777: else
2786:
2787: if l_simplebind then --{ evaluate using nds
2788: execute immediate l_text into p_db_output using l_var1, l_var2, l_var3;
2789: if g_debug then
2790: hr_utility.trace('NDS rb fetch. l_var1:'||l_var1||
2791: ' l_var2:'||l_var2 ||' l_var3:'||l_var3||
2792: ' p_balance_value:'||p_db_output);
2793: end if;
2794:
2807: -- 6. Get the sql value (providing there are rows returned)
2808: -- 7. Close the dynamic sql cursor
2809: --
2810: if g_debug then
2811: hr_utility.set_location ('pay_balance_pkg.get_run_balance using dbms_sql', 35);
2812: end if;
2813: sql_cursor := dbms_sql.open_cursor; -- step 1
2814: --
2815: if g_debug then
2812: end if;
2813: sql_cursor := dbms_sql.open_cursor; -- step 1
2814: --
2815: if g_debug then
2816: hr_utility.set_location ('pay_balance_pkg.get_run_balance', 40);
2817: end if;
2818: dbms_sql.parse(sql_cursor, l_text, dbms_sql.v7); -- step 2
2819: --
2820: -- -- step 3
2822: -- variables, starting with the contexts (B values).
2823: --
2824: for l_cxt_num in 1..route_contexts_cache.sz loop
2825: if g_debug then
2826: hr_utility.set_location ('pay_balance_pkg.get_run_balance', 45);
2827: end if;
2828: l_count := 0;
2829: l_context_found := FALSE;
2830: while (l_count < no_rows_con_tab) loop
2829: l_context_found := FALSE;
2830: while (l_count < no_rows_con_tab) loop
2831: if (con_id_tab(l_count) = route_contexts_cache.cxt_id(l_cxt_num)) then
2832: if g_debug then
2833: hr_utility.trace (con_name_tab (l_count) ||' = '||
2834: con_value_tab (l_count));
2835: end if;
2836: --
2837: -- Found a bind variable
2837: -- Found a bind variable
2838: --
2839: if (con_type_tab (l_count) = 'D') then
2840: if g_debug then
2841: hr_utility.set_location ('pay_balance_pkg.get_run_balance', 50);
2842: end if;
2843: dbms_sql.bind_variable
2844: (sql_cursor,
2845: con_name_tab(l_count),
2845: con_name_tab(l_count),
2846: fnd_date.canonical_to_date(ltrim(rtrim(con_value_tab (l_count)))));
2847: else
2848: if g_debug then
2849: hr_utility.set_location ('pay_balance_pkg.get_run_balance', 55);
2850: end if;
2851: dbms_sql.bind_variable
2852: (sql_cursor,
2853: con_name_tab(l_count),
2863: -- Raise an error, as there are contexts that have not been set up in
2864: -- the pl/sql tables that are required by the route.
2865: --
2866: if g_debug then
2867: hr_utility.set_location ('pay_balance_pkg.get_run_balance', 60);
2868: end if;
2869: select context_name
2870: into l_context_name
2871: from ff_contexts
2870: into l_context_name
2871: from ff_contexts
2872: where context_id = route_contexts_cache.cxt_id(l_cxt_num);
2873: --
2874: hr_utility.set_message(801, 'HR_7271_PAY_CONTEXT_MISSING');
2875: hr_utility.set_message_token ('CONTEXT_NAME', l_context_name);
2876: hr_utility.raise_error;
2877: end if;
2878: end loop; -- end of for route context loop
2871: from ff_contexts
2872: where context_id = route_contexts_cache.cxt_id(l_cxt_num);
2873: --
2874: hr_utility.set_message(801, 'HR_7271_PAY_CONTEXT_MISSING');
2875: hr_utility.set_message_token ('CONTEXT_NAME', l_context_name);
2876: hr_utility.raise_error;
2877: end if;
2878: end loop; -- end of for route context loop
2879: --
2872: where context_id = route_contexts_cache.cxt_id(l_cxt_num);
2873: --
2874: hr_utility.set_message(801, 'HR_7271_PAY_CONTEXT_MISSING');
2875: hr_utility.set_message_token ('CONTEXT_NAME', l_context_name);
2876: hr_utility.raise_error;
2877: end if;
2878: end loop; -- end of for route context loop
2879: --
2880: -- Now bind the where clause fillers (the 'U' values)
2880: -- Now bind the where clause fillers (the 'U' values)
2881: --
2882: for l_par_num in 1..route_parameters_cache.sz loop
2883: if g_debug then
2884: hr_utility.set_location ('pay_balance_pkg.get_run_balance', 62);
2885: end if;
2886: dbms_sql.bind_variable (sql_cursor, 'U'||to_char(route_parameters_cache.seq_no(l_par_num)),
2887: route_parameters_cache.par_val(l_par_num));
2888: end loop;
2887: route_parameters_cache.par_val(l_par_num));
2888: end loop;
2889: --
2890: if g_debug then
2891: hr_utility.set_location ('pay_balance_pkg.get_run_balance', 65);
2892: end if;
2893: dbms_sql.define_column (sql_cursor, 1, p_db_output, 80); -- step 4
2894: --
2895: if g_debug then
2892: end if;
2893: dbms_sql.define_column (sql_cursor, 1, p_db_output, 80); -- step 4
2894: --
2895: if g_debug then
2896: hr_utility.set_location ('pay_balance_pkg.get_run_balance', 70);
2897: end if;
2898: l_rows := dbms_sql.execute_and_fetch (sql_cursor, false); -- step 5
2899: --
2900: if (l_rows = 1) then
2898: l_rows := dbms_sql.execute_and_fetch (sql_cursor, false); -- step 5
2899: --
2900: if (l_rows = 1) then
2901: if g_debug then
2902: hr_utility.set_location ('pay_balance_pkg.get_run_balance', 75);
2903: end if;
2904: dbms_sql.column_value (sql_cursor, 1, p_db_output); -- step 6
2905: --
2906: if g_debug then
2903: end if;
2904: dbms_sql.column_value (sql_cursor, 1, p_db_output); -- step 6
2905: --
2906: if g_debug then
2907: hr_utility.set_location ('pay_balance_pkg.get_run_balance', 80);
2908: end if;
2909: dbms_sql.close_cursor(sql_cursor); -- step 7
2910: --
2911: if g_debug then
2908: end if;
2909: dbms_sql.close_cursor(sql_cursor); -- step 7
2910: --
2911: if g_debug then
2912: hr_utility.trace ('DB value = ' || p_db_output);
2913: end if;
2914: --
2915: elsif (l_rows = 0) then
2916: dbms_sql.close_cursor(sql_cursor);
2918: --
2919: -- its ok to not find a row
2920: --
2921: if g_debug then
2922: hr_utility.set_location ('pay_balance_pkg.get_run_balance', 85);
2923: end if;
2924: p_db_output := null;
2925: else
2926: --
2926: --
2927: -- Error, no rows found when the entity has been defined to always
2928: -- find a row.
2929: --
2930: hr_utility.set_message(801, 'HR_7272_PAY_NO_ROWS_RETURNED');
2931: hr_utility.set_message_token ('USER_ENTITY_NAME', p_user_name);
2932: hr_utility.raise_error;
2933: end if;
2934: else
2927: -- Error, no rows found when the entity has been defined to always
2928: -- find a row.
2929: --
2930: hr_utility.set_message(801, 'HR_7272_PAY_NO_ROWS_RETURNED');
2931: hr_utility.set_message_token ('USER_ENTITY_NAME', p_user_name);
2932: hr_utility.raise_error;
2933: end if;
2934: else
2935: --
2928: -- find a row.
2929: --
2930: hr_utility.set_message(801, 'HR_7272_PAY_NO_ROWS_RETURNED');
2931: hr_utility.set_message_token ('USER_ENTITY_NAME', p_user_name);
2932: hr_utility.raise_error;
2933: end if;
2934: else
2935: --
2936: -- More than 1 row have been returned. We must error as DB items can
2936: -- More than 1 row have been returned. We must error as DB items can
2937: -- only return 1 row.
2938: --
2939: if g_debug then
2940: hr_utility.set_location ('pay_balance_pkg.get_run_balance', 95);
2941: end if;
2942: dbms_sql.close_cursor(sql_cursor);
2943: hr_utility.set_message(801, 'HR_7273_PAY_MORE_THAN_1_ROW');
2944: hr_utility.raise_error;
2939: if g_debug then
2940: hr_utility.set_location ('pay_balance_pkg.get_run_balance', 95);
2941: end if;
2942: dbms_sql.close_cursor(sql_cursor);
2943: hr_utility.set_message(801, 'HR_7273_PAY_MORE_THAN_1_ROW');
2944: hr_utility.raise_error;
2945: end if;
2946: end if; --} end NDS
2947: return p_db_output;
2940: hr_utility.set_location ('pay_balance_pkg.get_run_balance', 95);
2941: end if;
2942: dbms_sql.close_cursor(sql_cursor);
2943: hr_utility.set_message(801, 'HR_7273_PAY_MORE_THAN_1_ROW');
2944: hr_utility.raise_error;
2945: end if;
2946: end if; --} end NDS
2947: return p_db_output;
2948: exception
2953: --
2954: When Others Then
2955: l_error_text := sqlerrm;
2956: if g_debug then
2957: Hr_Utility.Set_Location('pay_balance_pkg.get_run_balance', 100);
2958: end if;
2959: If (dbms_sql.is_open(sql_cursor)) then
2960: if g_debug then
2961: Hr_Utility.Set_Location('pay_balance_pkg.get_run_balance', 105);
2957: Hr_Utility.Set_Location('pay_balance_pkg.get_run_balance', 100);
2958: end if;
2959: If (dbms_sql.is_open(sql_cursor)) then
2960: if g_debug then
2961: Hr_Utility.Set_Location('pay_balance_pkg.get_run_balance', 105);
2962: end if;
2963: dbms_sql.close_cursor(sql_cursor);
2964: End If;
2965: hr_utility.set_message(801, 'HR_7276_PAY_FAILED_DB_ITEM');
2961: Hr_Utility.Set_Location('pay_balance_pkg.get_run_balance', 105);
2962: end if;
2963: dbms_sql.close_cursor(sql_cursor);
2964: End If;
2965: hr_utility.set_message(801, 'HR_7276_PAY_FAILED_DB_ITEM');
2966: hr_utility.set_message_token ('ERROR_MESSAGE', l_error_text);
2967: hr_utility.raise_error;
2968: --
2969: end get_run_balance;
2962: end if;
2963: dbms_sql.close_cursor(sql_cursor);
2964: End If;
2965: hr_utility.set_message(801, 'HR_7276_PAY_FAILED_DB_ITEM');
2966: hr_utility.set_message_token ('ERROR_MESSAGE', l_error_text);
2967: hr_utility.raise_error;
2968: --
2969: end get_run_balance;
2970: ---------------------------------------------------------------------------
2963: dbms_sql.close_cursor(sql_cursor);
2964: End If;
2965: hr_utility.set_message(801, 'HR_7276_PAY_FAILED_DB_ITEM');
2966: hr_utility.set_message_token ('ERROR_MESSAGE', l_error_text);
2967: hr_utility.raise_error;
2968: --
2969: end get_run_balance;
2970: ---------------------------------------------------------------------------
2971: -- function run_db_item
3065: l_jur_lvl pay_balance_types.jurisdiction_level%type;
3066: --
3067: BEGIN
3068: if g_debug then
3069: hr_utility.set_location('Entering pay_balance_pkg.get_run_bal', 10);
3070: end if;
3071: --
3072: if (p_route_type = 'RR') then
3073: --
3071: --
3072: if (p_route_type = 'RR') then
3073: --
3074: if g_debug then
3075: hr_utility.set_location('Entering pay_balance_pkg.get_run_bal', 15);
3076: end if;
3077: select pdb.balance_type_id,
3078: pdr.route_id,
3079: pdr.balance_type_column,
3117: fetch get_vals into l_user_name, l_business_group_id, l_legislation_code;
3118: close get_vals;
3119: --
3120: if g_debug then
3121: hr_utility.set_location('pay_balance_pkg.get_run_bal', 20);
3122: hr_utility.trace('l_user_entity_name is: '||l_user_name);
3123: end if;
3124: l_balance_val := get_run_balance(l_user_name
3125: ,l_business_group_id
3118: close get_vals;
3119: --
3120: if g_debug then
3121: hr_utility.set_location('pay_balance_pkg.get_run_bal', 20);
3122: hr_utility.trace('l_user_entity_name is: '||l_user_name);
3123: end if;
3124: l_balance_val := get_run_balance(l_user_name
3125: ,l_business_group_id
3126: ,l_legislation_code
3126: ,l_legislation_code
3127: ,p_route_type);
3128: end if;
3129: if g_debug then
3130: hr_utility.trace('leaving get_run_balance '||substr(l_user_name,-1 * least(length(l_user_name),20))||
3131: ' p_route_type:'||p_route_type||' Val:'||l_balance_val);
3132: end if;
3133: return l_balance_val;
3134: --
3156: l_defbal_rec t_def_bal_rec;
3157: --
3158: begin
3159: if g_debug then
3160: hr_utility.set_location ('Entering: pay_balance_pkg.get_rr_value', 5);
3161: end if;
3162: --
3163: -- First load the cache
3164: load_defbal_cache(p_defined_balance_id,
3165: l_defbal_rec);
3166: --
3167: if l_defbal_rec.rr_ptr is not null then
3168: if g_debug then
3169: hr_utility.set_location ('Entering: pay_balance_pkg.get_rr_value', 10);
3170: end if;
3171: l_balance_value := get_run_balance(p_defined_balance_id
3172: ,g_dimrou_cache(l_defbal_rec.rr_ptr).priority
3173: ,g_dimrou_cache(l_defbal_rec.rr_ptr).route_type);
3173: ,g_dimrou_cache(l_defbal_rec.rr_ptr).route_type);
3174: else
3175: --
3176: if g_debug then
3177: hr_utility.set_location ('Entering: pay_balance_pkg.get_rr_value', 20);
3178: end if;
3179: if (l_defbal_rec.start_rb_ptr is not null) then
3180: hr_general.assert_condition(false);
3181: else
3179: if (l_defbal_rec.start_rb_ptr is not null) then
3180: hr_general.assert_condition(false);
3181: else
3182: if g_debug then
3183: hr_utility.set_location ('Entering: pay_balance_pkg.get_rr_value', 30);
3184: end if;
3185: l_balance_value := fnd_number.canonical_to_number(run_db_item(p_defined_balance_id));
3186: end if;
3187: end if;
3189: -- Setup the return values
3190: --
3191: p_balance_value := l_balance_value;
3192: if g_debug then
3193: hr_utility.set_location ('Leaving: pay_balance_pkg.get_rr_value', 45);
3194: end if;
3195: --
3196: end get_rr_value;
3197: --
3349: l_position number;
3350: --
3351: begin
3352: if g_debug then
3353: hr_utility.set_location ('Entering: pay_balance_pkg.get_rb_value', 5);
3354: end if;
3355: --
3356: -- First load the cache
3357: load_defbal_cache(p_defined_balance_id,
3372: p_asg_action_id,
3373: l_valid);
3374: --
3375: if g_debug then
3376: hr_utility.set_location ('pay_balance_pkg.get_rb_value', 20);
3377: end if;
3378: --
3379: -- now know that run balance exists and is valid, so call run_db_item to
3380: -- return the balance_value.
3380: -- return the balance_value.
3381: --
3382: if l_valid = 'V' then
3383: if g_debug then
3384: hr_utility.set_location ('pay_balance_pkg.get_rb_value', 25);
3385: end if;
3386: l_balance_value := get_run_balance(p_defined_balance_id
3387: ,g_dimrou_cache(l_position).priority
3388: ,g_dimrou_cache(l_position).route_type);
3394: end loop;
3395: end if;
3396: --
3397: if g_debug then
3398: hr_utility.set_location ('pay_balance_pkg.get_rb_value l_balance_value:'||l_balance_value, 35);
3399: end if;
3400: --
3401: --
3402: -- Setup the return values
3403: --
3404: p_found := l_balval_found;
3405: p_balance_value := l_balance_value;
3406: if g_debug then
3407: hr_utility.set_location ('Leaving: pay_balance_pkg.get_rb_value', 45);
3408: end if;
3409: --
3410: end get_rb_value;
3411: --
3456: l_value_found number;
3457: l_ora_db_vers number; -- db version number
3458: begin
3459: if g_debug then
3460: hr_utility.set_location ('pay_balance_pkg.check_bal_expiry_internal', 1);
3461: hr_utility.trace ('Expiry check level = ' || p_expiry_checking_level);
3462: hr_utility.trace ('Expiry checking code = ' || p_expiry_checking_code);
3463: end if;
3464: if (p_expiry_checking_level = 'N') then
3457: l_ora_db_vers number; -- db version number
3458: begin
3459: if g_debug then
3460: hr_utility.set_location ('pay_balance_pkg.check_bal_expiry_internal', 1);
3461: hr_utility.trace ('Expiry check level = ' || p_expiry_checking_level);
3462: hr_utility.trace ('Expiry checking code = ' || p_expiry_checking_code);
3463: end if;
3464: if (p_expiry_checking_level = 'N') then
3465: --
3458: begin
3459: if g_debug then
3460: hr_utility.set_location ('pay_balance_pkg.check_bal_expiry_internal', 1);
3461: hr_utility.trace ('Expiry check level = ' || p_expiry_checking_level);
3462: hr_utility.trace ('Expiry checking code = ' || p_expiry_checking_code);
3463: end if;
3464: if (p_expiry_checking_level = 'N') then
3465: --
3466: -- There is no expiry check code to run, so balance can't expire
3471: --
3472: -- get the payroll_action_id for the balance (ie. owner):
3473: --
3474: if g_debug then
3475: hr_utility.set_location ('pay_balance_pkg.check_bal_expiry_internal', 10);
3476: end if;
3477: select PAY.payroll_action_id,
3478: PAY.effective_date
3479: into l_bal_owner_pay_action,
3485: --
3486: -- get the actual payroll information for this assignment action:
3487: --
3488: if g_debug then
3489: hr_utility.set_location ('pay_balance_pkg.check_bal_expiry_internal', 15);
3490: end if;
3491: select PAY.payroll_action_id,
3492: PAY.effective_date
3493: into l_payroll_action,
3552: -- 5. Get the variable value (providing there are rows returned)
3553: -- 6. Close the dynamic sql cursor
3554: --
3555: if g_debug then
3556: hr_utility.set_location ('pay_balance_pkg.check_bal_expiry_internal', 20);
3557: end if;
3558: -- bug 11849842 use execute immediate to reduce parsing
3559: l_ora_db_vers := get_oracle_db_version;
3560: if (nvl(l_ora_db_vers, 0) >= 10.0) then
3576: else
3577: sql_cursor := dbms_sql.open_cursor; -- step 1
3578: --
3579: if g_debug then
3580: hr_utility.set_location ('pay_balance_pkg.check_bal_expiry_internal', 25);
3581: end if;
3582: dbms_sql.parse(sql_cursor, l_expiry_chk_str, dbms_sql.v7); -- step 2
3583: --
3584: if g_debug then
3581: end if;
3582: dbms_sql.parse(sql_cursor, l_expiry_chk_str, dbms_sql.v7); -- step 2
3583: --
3584: if g_debug then
3585: hr_utility.set_location ('pay_balance_pkg.check_bal_expiry_internal', 30);
3586: end if;
3587: dbms_sql.bind_variable(sql_cursor, 'l_bal_owner_pay_action', -- step 3:
3588: l_bal_owner_pay_action);
3589: --
3613: dbms_sql.bind_variable(sql_cursor, 'l_expiry_information',
3614: l_expiry_information);
3615: --
3616: if g_debug then
3617: hr_utility.set_location ('pay_balance_pkg.check_bal_expiry_internal', 35);
3618: end if;
3619: l_rows := dbms_sql.execute (sql_cursor); -- step 4
3620: --
3621: if (l_rows = 1) then
3619: l_rows := dbms_sql.execute (sql_cursor); -- step 4
3620: --
3621: if (l_rows = 1) then
3622: if g_debug then
3623: hr_utility.set_location ('pay_balance_pkg.check_bal_expiry_internal', 40);
3624: end if;
3625: dbms_sql.variable_value(sql_cursor, 'l_expiry_information', -- step 5
3626: l_expiry_information);
3627: --
3625: dbms_sql.variable_value(sql_cursor, 'l_expiry_information', -- step 5
3626: l_expiry_information);
3627: --
3628: if g_debug then
3629: hr_utility.set_location ('pay_balance_pkg.check_bal_expiry_internal', 45);
3630: end if;
3631: dbms_sql.close_cursor(sql_cursor); -- step 6
3632: elsif (l_rows = 0) then
3633: dbms_sql.close_cursor(sql_cursor);
3630: end if;
3631: dbms_sql.close_cursor(sql_cursor); -- step 6
3632: elsif (l_rows = 0) then
3633: dbms_sql.close_cursor(sql_cursor);
3634: hr_utility.set_message(801, 'HR_7274_PAY_NO_EXPIRY_CODE');
3635: hr_utility.set_message_token ('EXPIRY_CODE', p_expiry_checking_code);
3636: hr_utility.raise_error;
3637: else
3638: --
3631: dbms_sql.close_cursor(sql_cursor); -- step 6
3632: elsif (l_rows = 0) then
3633: dbms_sql.close_cursor(sql_cursor);
3634: hr_utility.set_message(801, 'HR_7274_PAY_NO_EXPIRY_CODE');
3635: hr_utility.set_message_token ('EXPIRY_CODE', p_expiry_checking_code);
3636: hr_utility.raise_error;
3637: else
3638: --
3639: -- More than 1 row has been returned. We must error as package call can
3632: elsif (l_rows = 0) then
3633: dbms_sql.close_cursor(sql_cursor);
3634: hr_utility.set_message(801, 'HR_7274_PAY_NO_EXPIRY_CODE');
3635: hr_utility.set_message_token ('EXPIRY_CODE', p_expiry_checking_code);
3636: hr_utility.raise_error;
3637: else
3638: --
3639: -- More than 1 row has been returned. We must error as package call can
3640: -- only return 1 row, so this condition should never occur !
3639: -- More than 1 row has been returned. We must error as package call can
3640: -- only return 1 row, so this condition should never occur !
3641: --
3642: if g_debug then
3643: hr_utility.set_location ('pay_balance_pkg.check_bal_expiry_internal', 111);
3644: end if;
3645: dbms_sql.close_cursor(sql_cursor);
3646: hr_utility.raise_error;
3647: end if;
3642: if g_debug then
3643: hr_utility.set_location ('pay_balance_pkg.check_bal_expiry_internal', 111);
3644: end if;
3645: dbms_sql.close_cursor(sql_cursor);
3646: hr_utility.raise_error;
3647: end if;
3648: end if; -- end of ximm
3649: --
3650: -- At this point we have executed the dynamic pl/sql, 1 row was returned
3655: -- payroll action.
3656: --
3657: if (p_expiry_checking_level in ('A', 'P', 'E')) then
3658: if g_debug then
3659: hr_utility.set_location ('pay_balance_pkg.check_bal_expiry_internal', 50);
3660: end if;
3661: p_balance_expired := l_expiry_information;
3662: else -- date level
3663: if g_debug then
3660: end if;
3661: p_balance_expired := l_expiry_information;
3662: else -- date level
3663: if g_debug then
3664: hr_utility.set_location ('pay_balance_pkg.check_bal_expiry_internal', 55);
3665: end if;
3666: l_jul_effect_date := to_number (to_char (l_effective_date, 'J'));
3667: --
3668: if (l_expiry_information < l_jul_effect_date) then
3682: t_dim_nm(t_dim_nm.count + 1) := p_dimension_name;
3683: t_expiry(t_expiry.count + 1) := p_balance_expired;
3684: --
3685: if g_debug then
3686: hr_utility.trace('cached own pay action: '||
3687: to_char(t_own_pay_action(t_own_pay_action.count)));
3688: hr_utility.trace('cached usr pay action: '||
3689: to_char(t_usr_pay_action(t_usr_pay_action.count)));
3690: hr_utility.trace('cached dim name: '||t_dim_nm(t_dim_nm.count));
3684: --
3685: if g_debug then
3686: hr_utility.trace('cached own pay action: '||
3687: to_char(t_own_pay_action(t_own_pay_action.count)));
3688: hr_utility.trace('cached usr pay action: '||
3689: to_char(t_usr_pay_action(t_usr_pay_action.count)));
3690: hr_utility.trace('cached dim name: '||t_dim_nm(t_dim_nm.count));
3691: hr_utility.trace('cached bal expired: '||t_expiry(t_expiry.count));
3692: end if;
3686: hr_utility.trace('cached own pay action: '||
3687: to_char(t_own_pay_action(t_own_pay_action.count)));
3688: hr_utility.trace('cached usr pay action: '||
3689: to_char(t_usr_pay_action(t_usr_pay_action.count)));
3690: hr_utility.trace('cached dim name: '||t_dim_nm(t_dim_nm.count));
3691: hr_utility.trace('cached bal expired: '||t_expiry(t_expiry.count));
3692: end if;
3693: --
3694: end if;
3687: to_char(t_own_pay_action(t_own_pay_action.count)));
3688: hr_utility.trace('cached usr pay action: '||
3689: to_char(t_usr_pay_action(t_usr_pay_action.count)));
3690: hr_utility.trace('cached dim name: '||t_dim_nm(t_dim_nm.count));
3691: hr_utility.trace('cached bal expired: '||t_expiry(t_expiry.count));
3692: end if;
3693: --
3694: end if;
3695: --
3707: --
3708: When Others Then
3709: l_error_text := sqlerrm;
3710: if g_debug then
3711: Hr_Utility.Set_Location('check_bal_expiry_internal', 115);
3712: end if;
3713: If (dbms_sql.is_open(sql_cursor)) then
3714: if g_debug then
3715: Hr_Utility.Set_Location('check_bal_expiry_internal', 120);
3711: Hr_Utility.Set_Location('check_bal_expiry_internal', 115);
3712: end if;
3713: If (dbms_sql.is_open(sql_cursor)) then
3714: if g_debug then
3715: Hr_Utility.Set_Location('check_bal_expiry_internal', 120);
3716: end if;
3717: dbms_sql.close_cursor(sql_cursor);
3718: End If;
3719: hr_utility.set_message(801, 'HR_7275_PAY_FAILED_IN_EXPIRY');
3715: Hr_Utility.Set_Location('check_bal_expiry_internal', 120);
3716: end if;
3717: dbms_sql.close_cursor(sql_cursor);
3718: End If;
3719: hr_utility.set_message(801, 'HR_7275_PAY_FAILED_IN_EXPIRY');
3720: hr_utility.set_message_token ('ERROR_MESSAGE', l_error_text);
3721: hr_utility.raise_error;
3722: end check_bal_expiry_internal;
3723: --
3716: end if;
3717: dbms_sql.close_cursor(sql_cursor);
3718: End If;
3719: hr_utility.set_message(801, 'HR_7275_PAY_FAILED_IN_EXPIRY');
3720: hr_utility.set_message_token ('ERROR_MESSAGE', l_error_text);
3721: hr_utility.raise_error;
3722: end check_bal_expiry_internal;
3723: --
3724: --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
3717: dbms_sql.close_cursor(sql_cursor);
3718: End If;
3719: hr_utility.set_message(801, 'HR_7275_PAY_FAILED_IN_EXPIRY');
3720: hr_utility.set_message_token ('ERROR_MESSAGE', l_error_text);
3721: hr_utility.raise_error;
3722: end check_bal_expiry_internal;
3723: --
3724: --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
3725: -- +
3753: p_balance_expired boolean;
3754: l_exp_code number;
3755: --
3756: begin
3757: g_debug := hr_utility.debug_enabled;
3758: --
3759: l_exp_code := check_bal_expiry_internal(p_bal_owner_asg_action,
3760: p_assignment_action_id,
3761: p_dimension_name,
3909: while (l_count < no_rows_con_tab) loop
3910: if (con_name_tab (l_count) = 'JURISDICTION_CODE') then
3911: --
3912: if g_debug then
3913: hr_utility.set_location ('pay_balance_pkg.get_old_latest_bal_value', 15)
3914: ;
3915: end if;
3916: loc_value_tab (l_count) := substr (loc_value_tab(l_count), 1,
3917: p_defbal_rec.jurisdiction_lvl);
3915: end if;
3916: loc_value_tab (l_count) := substr (loc_value_tab(l_count), 1,
3917: p_defbal_rec.jurisdiction_lvl);
3918: if g_debug then
3919: hr_utility.trace ('substr jur. code = ' || loc_value_tab (l_count));
3920: end if;
3921: exit; -- exit while loop
3922: end if;
3923: l_count := l_count + 1;
3936: END IF;
3937: --
3938: LOOP
3939: if g_debug then
3940: hr_utility.set_location ('pay_balance_pkg.get_old_latest_bal_value', 20);
3941: end if;
3942: --
3943: IF p_defbal_rec.dimension_type = 'A' THEN
3944: FETCH bal_contexts_asg INTO l_latest_balance_id,
3970: l_bal_con_str := null;
3971: l_cursor_executed := FALSE;
3972: for c2rec in bal_context_values (l_latest_balance_id) loop
3973: if g_debug then
3974: hr_utility.set_location ('pay_balance_pkg.get_old_latest_bal_value', 25);
3975: end if;
3976: --
3977: -- Try to find the context:
3978: --
3979: l_fnd_con_and_val := FALSE;
3980: l_cursor_executed := TRUE;
3981: l_count := 0;
3982: if g_debug then
3983: hr_utility.trace ('Trying to match ' || c2rec.context_name);
3984: end if;
3985: --
3986: while (l_count < no_rows_con_tab) loop
3987: if ((con_id_tab (l_count) = c2rec.context_id) AND
3989: --
3990: -- found a matching context name AND value
3991: --
3992: if g_debug then
3993: hr_utility.trace ('match found');
3994: end if;
3995: l_fnd_con_and_val := TRUE;
3996: --
3997: -- Since we have the context name and value at this point, build
4020: -- Failed to find a matching context name and value, so clear
4021: -- flags and exit inner context cursor loop (c2rec)
4022: --
4023: if g_debug then
4024: hr_utility.trace ('match failed');
4025: end if;
4026: l_found_all_contexts := FALSE;
4027: exit; -- exit c2rec
4028: end if;
4038: -- Else carry on to the next latest_balance_id.
4039: --
4040: if ((l_found_all_contexts = TRUE) OR (l_cursor_executed = FALSE)) then
4041: if g_debug then
4042: hr_utility.set_location ('pay_balance_pkg.get_old_latest_bal_value', 35);
4043: end if;
4044: l_balance_found := TRUE;
4045: exit; -- exit c1rec
4046: end if;
4054: --
4055: -- If we could not find a latest balance, then derive from ff_routes
4056: --
4057: if g_debug then
4058: hr_utility.set_location ('pay_balance_pkg.get_old_latest_bal_value', 37);
4059: end if;
4060: if (l_balance_found = TRUE) then
4061: if g_debug then
4062: hr_utility.set_location ('pay_balance_pkg.get_old_latest_bal_value', 38);
4058: hr_utility.set_location ('pay_balance_pkg.get_old_latest_bal_value', 37);
4059: end if;
4060: if (l_balance_found = TRUE) then
4061: if g_debug then
4062: hr_utility.set_location ('pay_balance_pkg.get_old_latest_bal_value', 38);
4063: hr_utility.trace('OWN SEQ '||l_bal_owner_act_seq);
4064: hr_utility.trace('CURR SEQ '||l_bal_asg_act_seq);
4065: end if;
4066: --
4059: end if;
4060: if (l_balance_found = TRUE) then
4061: if g_debug then
4062: hr_utility.set_location ('pay_balance_pkg.get_old_latest_bal_value', 38);
4063: hr_utility.trace('OWN SEQ '||l_bal_owner_act_seq);
4064: hr_utility.trace('CURR SEQ '||l_bal_asg_act_seq);
4065: end if;
4066: --
4067: -- Check that the current assignment_action is the same as the
4060: if (l_balance_found = TRUE) then
4061: if g_debug then
4062: hr_utility.set_location ('pay_balance_pkg.get_old_latest_bal_value', 38);
4063: hr_utility.trace('OWN SEQ '||l_bal_owner_act_seq);
4064: hr_utility.trace('CURR SEQ '||l_bal_asg_act_seq);
4065: end if;
4066: --
4067: -- Check that the current assignment_action is the same as the
4068: -- owner action, if so there is no need for expiry checking.
4090: -- O.K. We cannot use the latest balance value, now check if the
4091: -- expired details can by used.
4092: --
4093: if g_debug then
4094: hr_utility.set_location ('pay_balance_pkg.get_old_latest_bal_value', 140
4095: );
4096: end if;
4097: select count(*)
4098: into l_num_of_runs
4114: -- in the specified period.
4115: --
4116: if l_num_of_runs = 1 then
4117: if g_debug then
4118: hr_utility.set_location ('pay_balance_pkg.get_old_latest_bal_value',
4119: 150);
4120: end if;
4121: l_bal_owner_asg_action := l_bal_expired_action;
4122: l_balance_value := l_bal_expired_value;
4123: l_expiry_needed := TRUE;
4124: else
4125: -- check if can use previous balance value
4126: if g_debug then
4127: hr_utility.set_location ('pay_balance_pkg.get_old_latest_bal_value',
4128: 160);
4129: end if;
4130: select count(*)
4131: into l_num_of_runs
4141: and l_bal_asg_act_seq;
4142: --
4143: if l_num_of_runs = 1 then
4144: if g_debug then
4145: hr_utility.set_location ('pay_balance_pkg.get_old_latest_bal_value'
4146: , 170);
4147: end if;
4148: l_bal_owner_asg_action := l_prev_action;
4149: l_balance_value := l_prev_value;
4161: end if;
4162: --
4163: end if;
4164: if g_debug then
4165: hr_utility.set_location ('pay_balance_pkg.get_old_latest_bal_value', 55);
4166: end if;
4167: --
4168: if (l_expiry_needed = TRUE) then
4169: --
4187: --
4188: -- Yep its a special case.
4189: if (l_balance_expired = BALANCE_EXPIRED) then
4190: if g_debug then
4191: hr_utility.trace ('Balance expired');
4192: end if;
4193: l_balance_value := 0;
4194: l_found := TRUE;
4195: elsif (l_balance_expired = BALANCE_NOT_EXPIRED) then
4193: l_balance_value := 0;
4194: l_found := TRUE;
4195: elsif (l_balance_expired = BALANCE_NOT_EXPIRED) then
4196: if g_debug then
4197: hr_utility.trace ('Balance not expired');
4198: end if;
4199: l_found := TRUE;
4200: else
4201: --
4210:
4211: else
4212: if (l_balance_expired = BALANCE_EXPIRED) then
4213: if g_debug then
4214: hr_utility.trace ('Balance expired');
4215: end if;
4216: l_balance_value := 0;
4217: l_found := TRUE;
4218: else
4216: l_balance_value := 0;
4217: l_found := TRUE;
4218: else
4219: if g_debug then
4220: hr_utility.trace ('Balance not expired');
4221: end if;
4222: l_found := TRUE;
4223: end if; --BALANCE_EXPIRED
4224: end if; --p_defbal_rec.expiry_check_lvl = 'E'
4363: --
4364: l_cnt := l_cnt + 1;
4365: --
4366: if g_debug then
4367: hr_utility.set_location ('pay_balance_pkg.get_new_latest_bal_value', 38);
4368: hr_utility.trace('OWN SEQ '||lbrec.owner_action_seq);
4369: hr_utility.trace('CURR SEQ '||lbrec.current_action_seq);
4370: end if;
4371: --
4364: l_cnt := l_cnt + 1;
4365: --
4366: if g_debug then
4367: hr_utility.set_location ('pay_balance_pkg.get_new_latest_bal_value', 38);
4368: hr_utility.trace('OWN SEQ '||lbrec.owner_action_seq);
4369: hr_utility.trace('CURR SEQ '||lbrec.current_action_seq);
4370: end if;
4371: --
4372: -- Check that the current assignment_action is the same as the
4365: --
4366: if g_debug then
4367: hr_utility.set_location ('pay_balance_pkg.get_new_latest_bal_value', 38);
4368: hr_utility.trace('OWN SEQ '||lbrec.owner_action_seq);
4369: hr_utility.trace('CURR SEQ '||lbrec.current_action_seq);
4370: end if;
4371: --
4372: -- Check that the current assignment_action is the same as the
4373: -- owner action, if so there is no need for expiry checking.
4396: -- O.K. We cannot use the latest balance value, now check if the
4397: -- expired details can by used.
4398: --
4399: if g_debug then
4400: hr_utility.set_location ('pay_balance_pkg.get_new_latest_bal_value', 140
4401: );
4402: end if;
4403: select count(*)
4404: into l_num_of_runs
4420: -- in the specified period.
4421: --
4422: if l_num_of_runs = 1 then
4423: if g_debug then
4424: hr_utility.set_location ('pay_balance_pkg.get_new_latest_bal_value',
4425: 150);
4426: end if;
4427: l_bal_owner_asg_action := lbrec.expired_assignment_action_id;
4428: l_balance_value := lbrec.expired_value;
4430: l_expiry_needed := TRUE;
4431: else
4432: -- check if can use previous balance value
4433: if g_debug then
4434: hr_utility.set_location ('pay_balance_pkg.get_new_latest_bal_value',
4435: 160);
4436: end if;
4437: select count(*)
4438: into l_num_of_runs
4448: and lbrec.current_action_seq;
4449: --
4450: if l_num_of_runs = 1 then
4451: if g_debug then
4452: hr_utility.set_location ('pay_balance_pkg.get_new_latest_bal_value'
4453: , 170);
4454: end if;
4455: l_bal_owner_asg_action := lbrec.prev_assignment_action_id;
4456: l_balance_value := lbrec.prev_balance_value;
4479: l_expiry_date := lbrec.expiry_date;
4480: --
4481: end if;
4482: if g_debug then
4483: hr_utility.set_location ('pay_balance_pkg.get_new_latest_bal_value', 55);
4484: end if;
4485: --
4486: if (l_expiry_needed = TRUE) then
4487: --
4567: --
4568: -- Yep its a special case.
4569: if (l_balance_expired = BALANCE_EXPIRED) then
4570: if g_debug then
4571: hr_utility.trace ('Balance expired');
4572: end if;
4573: l_balance_value := 0;
4574: l_found := TRUE;
4575: elsif (l_balance_expired = BALANCE_NOT_EXPIRED) then
4573: l_balance_value := 0;
4574: l_found := TRUE;
4575: elsif (l_balance_expired = BALANCE_NOT_EXPIRED) then
4576: if g_debug then
4577: hr_utility.trace ('Balance not expired');
4578: end if;
4579: l_found := TRUE;
4580: else
4581: --
4589: end if; --BALANCE_EXPIRED
4590: else
4591: if (l_balance_expired = BALANCE_EXPIRED) then
4592: if g_debug then
4593: hr_utility.trace ('Balance expired');
4594: end if;
4595: l_balance_value := 0;
4596: l_found := TRUE;
4597: else
4595: l_balance_value := 0;
4596: l_found := TRUE;
4597: else
4598: if g_debug then
4599: hr_utility.trace ('Balance not expired');
4600: end if;
4601: l_found := TRUE;
4602: end if; --BALANCE_EXPIRED
4603: end if; --p_defbal_rec.expiry_check_lvl = 'E'
4906: --
4907: -- Put the returned values into the cache table.
4908: for ret_cnt in 1..l_retrieval_list.count loop
4909: if g_debug then
4910: hr_utility.trace(' after get_value. ret_cnt:'||ret_cnt||' balance_value:'||
4911: l_retrieval_list(ret_cnt).balance_value);
4912: end if;
4913: p_output_table(pos_indx(ret_cnt)).balance_value :=
4914: l_retrieval_list(ret_cnt).balance_value;
4940: bal_ju_code pay_run_results.jurisdiction_code%type;
4941: con_ju_code pay_run_results.jurisdiction_code%type;
4942: --
4943: begin
4944: g_debug := hr_utility.debug_enabled;
4945: --
4946: l_cache_cnt := 1; -- The next free position
4947: --
4948: -- Create the super set of balances to be calculated.
4968: or (l_defbal_rec.bd_needed and p_context_lst(con_cnt).balance_date is null)
4969: ) then
4970: --
4971: if g_debug then
4972: hr_utility.trace('Not a valid context combination for this balance');
4973: end if;
4974: --
4975: else
4976: --
5085: start_def_bal_ptr := -999;
5086: prev_def_bal_id := -1;
5087: for cnt in 1..l_transform_cache.count loop
5088: --
5089: hr_utility.trace('Time 1 '||to_char(sysdate, 'HH24:MI:SS'));
5090: if l_transform_cache(cnt).defined_balance_id = prev_def_bal_id then
5091: --
5092: l_add_balance := TRUE;
5093: if g_debug then
5090: if l_transform_cache(cnt).defined_balance_id = prev_def_bal_id then
5091: --
5092: l_add_balance := TRUE;
5093: if g_debug then
5094: hr_utility.trace('Start index '||start_def_bal_ptr||' to '||l_batch_bal_cache.count);
5095: end if;
5096: hr_utility.trace('Time 2 '||to_char(sysdate, 'HH24:MI:SS'));
5097: for dup_cnt in start_def_bal_ptr..l_batch_bal_cache.count loop
5098: --
5092: l_add_balance := TRUE;
5093: if g_debug then
5094: hr_utility.trace('Start index '||start_def_bal_ptr||' to '||l_batch_bal_cache.count);
5095: end if;
5096: hr_utility.trace('Time 2 '||to_char(sysdate, 'HH24:MI:SS'));
5097: for dup_cnt in start_def_bal_ptr..l_batch_bal_cache.count loop
5098: --
5099: hr_utility.trace('Time 3 '||to_char(sysdate, 'HH24:MI:SS'));
5100: if g_debug then
5095: end if;
5096: hr_utility.trace('Time 2 '||to_char(sysdate, 'HH24:MI:SS'));
5097: for dup_cnt in start_def_bal_ptr..l_batch_bal_cache.count loop
5098: --
5099: hr_utility.trace('Time 3 '||to_char(sysdate, 'HH24:MI:SS'));
5100: if g_debug then
5101: hr_utility.trace('Comparing...');
5102: hr_utility.trace(l_transform_cache(cnt).defined_balance_id||' '||
5103: l_batch_bal_cache(dup_cnt).defined_balance_id);
5097: for dup_cnt in start_def_bal_ptr..l_batch_bal_cache.count loop
5098: --
5099: hr_utility.trace('Time 3 '||to_char(sysdate, 'HH24:MI:SS'));
5100: if g_debug then
5101: hr_utility.trace('Comparing...');
5102: hr_utility.trace(l_transform_cache(cnt).defined_balance_id||' '||
5103: l_batch_bal_cache(dup_cnt).defined_balance_id);
5104: hr_utility.trace(nvl(l_transform_cache(cnt).tax_unit_id, -999)||' '||
5105: nvl(l_batch_bal_cache(dup_cnt).tax_unit_id, -999));
5098: --
5099: hr_utility.trace('Time 3 '||to_char(sysdate, 'HH24:MI:SS'));
5100: if g_debug then
5101: hr_utility.trace('Comparing...');
5102: hr_utility.trace(l_transform_cache(cnt).defined_balance_id||' '||
5103: l_batch_bal_cache(dup_cnt).defined_balance_id);
5104: hr_utility.trace(nvl(l_transform_cache(cnt).tax_unit_id, -999)||' '||
5105: nvl(l_batch_bal_cache(dup_cnt).tax_unit_id, -999));
5106: hr_utility.trace(nvl(l_transform_cache(cnt).jurisdiction_code, '
5100: if g_debug then
5101: hr_utility.trace('Comparing...');
5102: hr_utility.trace(l_transform_cache(cnt).defined_balance_id||' '||
5103: l_batch_bal_cache(dup_cnt).defined_balance_id);
5104: hr_utility.trace(nvl(l_transform_cache(cnt).tax_unit_id, -999)||' '||
5105: nvl(l_batch_bal_cache(dup_cnt).tax_unit_id, -999));
5106: hr_utility.trace(nvl(l_transform_cache(cnt).jurisdiction_code, '
5107: nvl(l_batch_bal_cache(dup_cnt).jurisdiction_code, '
5108: hr_utility.trace(nvl(l_transform_cache(cnt).source_id, -999)||' '||
5102: hr_utility.trace(l_transform_cache(cnt).defined_balance_id||' '||
5103: l_batch_bal_cache(dup_cnt).defined_balance_id);
5104: hr_utility.trace(nvl(l_transform_cache(cnt).tax_unit_id, -999)||' '||
5105: nvl(l_batch_bal_cache(dup_cnt).tax_unit_id, -999));
5106: hr_utility.trace(nvl(l_transform_cache(cnt).jurisdiction_code, '
5108: hr_utility.trace(nvl(l_transform_cache(cnt).source_id, -999)||' '||
5109: nvl(l_batch_bal_cache(dup_cnt).source_id, -999));
5110: hr_utility.trace(nvl(l_transform_cache(cnt).source_text, '
5104: hr_utility.trace(nvl(l_transform_cache(cnt).tax_unit_id, -999)||' '||
5105: nvl(l_batch_bal_cache(dup_cnt).tax_unit_id, -999));
5106: hr_utility.trace(nvl(l_transform_cache(cnt).jurisdiction_code, '
5107: nvl(l_batch_bal_cache(dup_cnt).jurisdiction_code, '
5108: hr_utility.trace(nvl(l_transform_cache(cnt).source_id, -999)||' '||
5109: nvl(l_batch_bal_cache(dup_cnt).source_id, -999));
5110: hr_utility.trace(nvl(l_transform_cache(cnt).source_text, '
5111: nvl(l_batch_bal_cache(dup_cnt).source_text, '
5112: end if;
5106: hr_utility.trace(nvl(l_transform_cache(cnt).jurisdiction_code, '
5107: nvl(l_batch_bal_cache(dup_cnt).jurisdiction_code, '
5108: hr_utility.trace(nvl(l_transform_cache(cnt).source_id, -999)||' '||
5109: nvl(l_batch_bal_cache(dup_cnt).source_id, -999));
5110: hr_utility.trace(nvl(l_transform_cache(cnt).source_text, '
5112: end if;
5113: --
5114: hr_utility.trace('Time 4 '||to_char(sysdate, 'HH24:MI:SS'));
5110: hr_utility.trace(nvl(l_transform_cache(cnt).source_text, '
5111: nvl(l_batch_bal_cache(dup_cnt).source_text, '
5112: end if;
5113: --
5114: hr_utility.trace('Time 4 '||to_char(sysdate, 'HH24:MI:SS'));
5115: if (l_transform_cache(cnt).defined_balance_id = l_batch_bal_cache(dup_cnt).defined_balance_id
5116: and nvl(l_transform_cache(cnt).tax_unit_id, -999)
5117: = nvl(l_batch_bal_cache(dup_cnt).tax_unit_id, -999)
5118: and nvl(l_transform_cache(cnt).jurisdiction_code, '
5140: end if;
5141: --
5142: end loop;
5143: --
5144: hr_utility.trace('Time 5 '||to_char(sysdate, 'HH24:MI:SS'));
5145: if (l_add_balance = TRUE) then
5146: hr_utility.trace('Time 6 '||to_char(sysdate, 'HH24:MI:SS'));
5147: l_batch_bal_cache(l_cache_cnt) := l_transform_cache(cnt);
5148: l_cache_cnt := l_cache_cnt + 1;
5142: end loop;
5143: --
5144: hr_utility.trace('Time 5 '||to_char(sysdate, 'HH24:MI:SS'));
5145: if (l_add_balance = TRUE) then
5146: hr_utility.trace('Time 6 '||to_char(sysdate, 'HH24:MI:SS'));
5147: l_batch_bal_cache(l_cache_cnt) := l_transform_cache(cnt);
5148: l_cache_cnt := l_cache_cnt + 1;
5149: end if;
5150: hr_utility.trace('Time 7 '||to_char(sysdate, 'HH24:MI:SS'));
5146: hr_utility.trace('Time 6 '||to_char(sysdate, 'HH24:MI:SS'));
5147: l_batch_bal_cache(l_cache_cnt) := l_transform_cache(cnt);
5148: l_cache_cnt := l_cache_cnt + 1;
5149: end if;
5150: hr_utility.trace('Time 7 '||to_char(sysdate, 'HH24:MI:SS'));
5151: --
5152: else
5153: hr_utility.trace('Time 8 '||to_char(sysdate, 'HH24:MI:SS'));
5154: l_batch_bal_cache(l_cache_cnt) := l_transform_cache(cnt);
5149: end if;
5150: hr_utility.trace('Time 7 '||to_char(sysdate, 'HH24:MI:SS'));
5151: --
5152: else
5153: hr_utility.trace('Time 8 '||to_char(sysdate, 'HH24:MI:SS'));
5154: l_batch_bal_cache(l_cache_cnt) := l_transform_cache(cnt);
5155: prev_def_bal_id := l_batch_bal_cache(l_cache_cnt).defined_balance_id;
5156: start_def_bal_ptr := l_cache_cnt;
5157: l_cache_cnt := l_cache_cnt + 1;
5155: prev_def_bal_id := l_batch_bal_cache(l_cache_cnt).defined_balance_id;
5156: start_def_bal_ptr := l_cache_cnt;
5157: l_cache_cnt := l_cache_cnt + 1;
5158: end if;
5159: hr_utility.trace('Time 9 '||to_char(sysdate, 'HH24:MI:SS'));
5160: --
5161: end loop;
5162: --
5163: -- Now get the balances
5316: l_decode_required boolean;
5317: l_jur_lvl pay_balance_types.jurisdiction_level%type;
5318: --
5319: begin
5320: g_debug := hr_utility.debug_enabled;
5321: --
5322: set_context ('ASSIGNMENT_ACTION_ID', p_assignment_action_id);
5323: --
5324: -- Ensure the internal cache is empty.
5431: p_date_earned in date
5432: ) return number is
5433: p_balance_result number;
5434: begin
5435: g_debug := hr_utility.debug_enabled;
5436: --
5437: if p_tax_unit_id is not null then
5438: set_context('TAX_UNIT_ID', p_tax_unit_id);
5439: end if;
5468: p_date_earned in date
5469: ) return number is
5470: p_balance_result number;
5471: begin
5472: g_debug := hr_utility.debug_enabled;
5473: --
5474: if p_tax_unit_id is not null then
5475: set_context('TAX_UNIT_ID', p_tax_unit_id);
5476: end if;
5511: p_date_earned in date
5512: ) return number is
5513: p_balance_result number;
5514: begin
5515: g_debug := hr_utility.debug_enabled;
5516: --
5517: if p_tax_unit_id is not null then
5518: set_context('TAX_UNIT_ID', p_tax_unit_id);
5519: end if;
5548: p_assignment_action_id in number
5549: ) return number is
5550: p_balance_result number;
5551: begin
5552: g_debug := hr_utility.debug_enabled;
5553: p_balance_result := get_value (p_defined_balance_id,
5554: p_assignment_action_id,
5555: false); -- look for a latest balance first
5556: return p_balance_result;
5563: p_always_get_db_item in boolean
5564: ) return number is
5565: p_balance_result number;
5566: begin
5567: g_debug := hr_utility.debug_enabled;
5568: p_balance_result := get_value(p_defined_balance_id => p_defined_balance_id
5569: ,p_assignment_action_id => p_assignment_action_id
5570: ,p_get_rr_route => p_always_get_db_item
5571: ,p_get_rb_route => false
5597: p_balance_result number;
5598: l_get_rr_route boolean;
5599: l_get_rb_route boolean;
5600: begin
5601: g_debug := hr_utility.debug_enabled;
5602: --
5603: -- p_get_rr_route and p_get_rb_route have been set to number rather than
5604: -- boolean, so that can be called in a select statement.
5605: --
5688: --
5689: ------------------------- get_value (action mode) -------------------------
5690: --
5691: begin
5692: g_debug := hr_utility.debug_enabled;
5693: if g_debug then
5694: hr_utility.set_location ('pay_balance_pkg.get_value - ACTION MODE', 1);
5695: hr_utility.trace ('def_bal_id = ' || to_char(p_defined_balance_id));
5696: hr_utility.trace ('asg_action_id = ' || to_char(p_assignment_action_id));
5690: --
5691: begin
5692: g_debug := hr_utility.debug_enabled;
5693: if g_debug then
5694: hr_utility.set_location ('pay_balance_pkg.get_value - ACTION MODE', 1);
5695: hr_utility.trace ('def_bal_id = ' || to_char(p_defined_balance_id));
5696: hr_utility.trace ('asg_action_id = ' || to_char(p_assignment_action_id));
5697: end if;
5698: --
5691: begin
5692: g_debug := hr_utility.debug_enabled;
5693: if g_debug then
5694: hr_utility.set_location ('pay_balance_pkg.get_value - ACTION MODE', 1);
5695: hr_utility.trace ('def_bal_id = ' || to_char(p_defined_balance_id));
5696: hr_utility.trace ('asg_action_id = ' || to_char(p_assignment_action_id));
5697: end if;
5698: --
5699: -- Set up the context of assignment action:
5692: g_debug := hr_utility.debug_enabled;
5693: if g_debug then
5694: hr_utility.set_location ('pay_balance_pkg.get_value - ACTION MODE', 1);
5695: hr_utility.trace ('def_bal_id = ' || to_char(p_defined_balance_id));
5696: hr_utility.trace ('asg_action_id = ' || to_char(p_assignment_action_id));
5697: end if;
5698: --
5699: -- Set up the context of assignment action:
5700: --
5761: p_virtual_date in date
5762: ) return number is
5763: p_balance_result number;
5764: begin
5765: g_debug := hr_utility.debug_enabled;
5766: p_balance_result := get_value_lock (p_defined_balance_id,
5767: p_assignment_id,
5768: p_virtual_date,
5769: false, -- look for a latest balance first
5779: p_always_get_db_item in boolean
5780: ) return number is
5781: p_balance_result number;
5782: begin
5783: g_debug := hr_utility.debug_enabled;
5784: p_balance_result := get_value_lock (p_defined_balance_id,
5785: p_assignment_id,
5786: p_virtual_date,
5787: p_always_get_db_item,
5797: p_asg_lock in varchar2
5798: ) return number is
5799: p_balance_result number;
5800: begin
5801: g_debug := hr_utility.debug_enabled;
5802: p_balance_result := get_value_lock (p_defined_balance_id,
5803: p_assignment_id,
5804: p_virtual_date,
5805: false, -- look for a latest balance first
5825: l_time_period_id per_time_periods.time_period_id%type;
5826: l_asg_lock boolean;
5827: --
5828: begin
5829: g_debug := hr_utility.debug_enabled;
5830: if g_debug then
5831: hr_utility.set_location ('pay_balance_pkg.get_value - DATE MODE', 1);
5832: hr_utility.trace ('def_bal_id = ' || to_char(p_defined_balance_id));
5833: hr_utility.trace ('Assign_id = ' || to_char(p_assignment_id));
5827: --
5828: begin
5829: g_debug := hr_utility.debug_enabled;
5830: if g_debug then
5831: hr_utility.set_location ('pay_balance_pkg.get_value - DATE MODE', 1);
5832: hr_utility.trace ('def_bal_id = ' || to_char(p_defined_balance_id));
5833: hr_utility.trace ('Assign_id = ' || to_char(p_assignment_id));
5834: hr_utility.trace ('V_date = ' || to_char (p_virtual_date));
5835: end if;
5828: begin
5829: g_debug := hr_utility.debug_enabled;
5830: if g_debug then
5831: hr_utility.set_location ('pay_balance_pkg.get_value - DATE MODE', 1);
5832: hr_utility.trace ('def_bal_id = ' || to_char(p_defined_balance_id));
5833: hr_utility.trace ('Assign_id = ' || to_char(p_assignment_id));
5834: hr_utility.trace ('V_date = ' || to_char (p_virtual_date));
5835: end if;
5836: --
5829: g_debug := hr_utility.debug_enabled;
5830: if g_debug then
5831: hr_utility.set_location ('pay_balance_pkg.get_value - DATE MODE', 1);
5832: hr_utility.trace ('def_bal_id = ' || to_char(p_defined_balance_id));
5833: hr_utility.trace ('Assign_id = ' || to_char(p_assignment_id));
5834: hr_utility.trace ('V_date = ' || to_char (p_virtual_date));
5835: end if;
5836: --
5837: -- Set the assignment locking flag.
5830: if g_debug then
5831: hr_utility.set_location ('pay_balance_pkg.get_value - DATE MODE', 1);
5832: hr_utility.trace ('def_bal_id = ' || to_char(p_defined_balance_id));
5833: hr_utility.trace ('Assign_id = ' || to_char(p_assignment_id));
5834: hr_utility.trace ('V_date = ' || to_char (p_virtual_date));
5835: end if;
5836: --
5837: -- Set the assignment locking flag.
5838: --
5845: --
5846: -- get the payroll information
5847: --
5848: if g_debug then
5849: hr_utility.set_location ('pay_balance_pkg.get_value', 10);
5850: end if;
5851: select ASSIGN.payroll_id,
5852: ASSIGN.business_group_id,
5853: PAYROLL.consolidation_set_id
5866: -- If there is a time period id, then get it, else use a null value:
5867: --
5868: begin
5869: if g_debug then
5870: hr_utility.set_location ('pay_balance_pkg.get_value', 12);
5871: end if;
5872: select TIMEP.time_period_id
5873: into l_time_period_id
5874: from per_time_periods TIMEP
5877: and TIMEP.end_date;
5878: exception
5879: when no_data_found then
5880: if g_debug then
5881: hr_utility.set_location ('pay_balance_pkg.get_value', 13);
5882: end if;
5883: l_time_period_id := null;
5884: end;
5885: --
5891: --
5892: -- insert a temporary row into pay_payroll_actions
5893: --
5894: if g_debug then
5895: hr_utility.set_location ('pay_balance_pkg.get_value', 20);
5896: end if;
5897: insert into pay_payroll_actions
5898: (payroll_action_id,
5899: action_type,
5921: --
5922: -- now insert the assignment action:
5923: --
5924: if g_debug then
5925: hr_utility.set_location ('pay_balance_pkg.get_value', 25);
5926: end if;
5927: hrassact.inassact (pactid => l_pay_action_id,
5928: asgid => p_assignment_id,
5929: p_asg_lock => l_asg_lock);
5930: --
5931: -- retrieve the assignment action id:
5932: --
5933: if g_debug then
5934: hr_utility.set_location ('pay_balance_pkg.get_value', 30);
5935: end if;
5936: select assignment_action_id
5937: into l_ass_action_id
5938: from pay_assignment_actions
5938: from pay_assignment_actions
5939: where payroll_action_id = l_pay_action_id;
5940: --
5941: if g_debug then
5942: hr_utility.trace ('Assignment action id = ' || to_char (l_ass_action_id));
5943: end if;
5944: --
5945: -- Now retrieve the balance for this temp. assignment action:
5946: --
6366: l_ora_db_vers number;
6367: l_element_type_id pay_input_values_f.element_type_id%type;
6368: --
6369: BEGIN
6370: g_debug := hr_utility.debug_enabled;
6371: if g_debug then
6372: hr_utility.set_location('Entering: pay_balance_pkg.invalidate_run_balances',5);
6373: end if;
6374: --
6368: --
6369: BEGIN
6370: g_debug := hr_utility.debug_enabled;
6371: if g_debug then
6372: hr_utility.set_location('Entering: pay_balance_pkg.invalidate_run_balances',5);
6373: end if;
6374: --
6375: l_ora_db_vers := get_oracle_db_version;
6376: if HRASSACT.CHECK_LATEST_BALANCES = TRUE then --{ check balance true
6398: select defined_balance_id from pay_defined_balances db
6399: where balance_type_id = p_balance_type_id);
6400:
6401: if g_debug then
6402: hr_utility.set_location('Entering: pay_balance_pkg.invalidate_run_balances SQL%ROWCOUNT:'||SQL%ROWCOUNT,20);
6403: end if;
6404: -- Bug 16048613, calling cursors with 'no_unnest' hints if db version is less than 11g
6405: if(nvl(l_ora_db_vers,0) < 11.0) then
6406: for each_row in get_def_bals(p_balance_type_id, p_input_value_id ,l_element_type_id, p_invalid_date) loop
6408: update pay_balance_validation
6409: set run_balance_status = 'I'
6410: where defined_balance_id = each_row.defined_balance_id;
6411: if g_debug then
6412: hr_utility.set_location('pay_balance_pkg.invalidate_run_balances asg_level', 15);
6413: end if;
6414: end loop;
6415: -- repeat for any group level dimensions
6416: for grp_row in get_def_bals_grp(p_balance_type_id, p_input_value_id ,l_element_type_id, p_invalid_date) loop
6418: update pay_balance_validation
6419: set run_balance_status = 'I'
6420: where defined_balance_id = grp_row.defined_balance_id;
6421: if g_debug then
6422: hr_utility.set_location('pay_balance_pkg.invalidate_run_balances grp_level', 17);
6423: end if;
6424: end loop;
6425: else
6426: for each_row in get_def_bals_g(p_balance_type_id, p_input_value_id ,l_element_type_id, p_invalid_date) loop
6428: update pay_balance_validation
6429: set run_balance_status = 'I'
6430: where defined_balance_id = each_row.defined_balance_id;
6431: if g_debug then
6432: hr_utility.set_location('pay_balance_pkg.invalidate_run_balances asg_level', 18);
6433: end if;
6434: end loop;
6435: -- repeat for any group level dimensions
6436: for grp_row in get_def_bals_grp_g(p_balance_type_id, p_input_value_id ,l_element_type_id, p_invalid_date) loop
6438: update pay_balance_validation
6439: set run_balance_status = 'I'
6440: where defined_balance_id = grp_row.defined_balance_id;
6441: if g_debug then
6442: hr_utility.set_location('pay_balance_pkg.invalidate_run_balances grp_level', 19);
6443: end if;
6444: end loop;
6445: end if;
6446: if g_debug then
6443: end if;
6444: end loop;
6445: end if;
6446: if g_debug then
6447: hr_utility.set_location('Leaving: pay_balance_pkg.invalidate_run_balances', 20);
6448: end if;
6449:
6450: end if; --} end results exist
6451: --
6466: where pdb.balance_type_id = p_bal_id
6467: and pdb.save_run_balance = 'Y';
6468: --
6469: BEGIN
6470: g_debug := hr_utility.debug_enabled;
6471: if g_debug then
6472: hr_utility.set_location('Entering: pay_balance_pkg.invalidate_run_balances',5);
6473: end if;
6474: for each_row in get_def_bals(p_balance_type_id) loop
6468: --
6469: BEGIN
6470: g_debug := hr_utility.debug_enabled;
6471: if g_debug then
6472: hr_utility.set_location('Entering: pay_balance_pkg.invalidate_run_balances',5);
6473: end if;
6474: for each_row in get_def_bals(p_balance_type_id) loop
6475: --
6476: update pay_balance_validation
6476: update pay_balance_validation
6477: set run_balance_status = 'I'
6478: where defined_balance_id = each_row.defined_balance_id;
6479: if g_debug then
6480: hr_utility.set_location('pay_balance_pkg.invalidate_run_balances', 15);
6481: end if;
6482: end loop;
6483: if g_debug then
6484: hr_utility.set_location('Leaving: pay_balance_pkg.invalidate_run_balances', 20);
6480: hr_utility.set_location('pay_balance_pkg.invalidate_run_balances', 15);
6481: end if;
6482: end loop;
6483: if g_debug then
6484: hr_utility.set_location('Leaving: pay_balance_pkg.invalidate_run_balances', 20);
6485: end if;
6486: END invalidate_run_balances;
6487: --------------------------------------------------------------------------
6488: -- get_run_result_info
6608:
6609: --
6610: begin
6611: --
6612: -- hr_utility.trace('entering find_context. p_context_name:'||p_context_name);
6613:
6614: get_run_result_info(p_context_id, l_rr_rec);
6615: --
6616: pay_core_utils.get_leg_context_iv_name(p_context_name,
6708: current_ptr number;
6709: begin
6710: --
6711: if g_debug then
6712: hr_utility.set_location('Entering: pay_balance_pkg.search_rb_cache', 5);
6713: end if;
6714: if (p_grp_rb_ptr_list.exists(p_defined_balanceid)) then
6715: --
6716: current_ptr := p_grp_rb_ptr_list(p_defined_balanceid);
6736: then
6737: --
6738: l_found := TRUE;
6739: if g_debug then
6740: hr_utility.set_location('pay_balance_pkg.search_rb_cache', 10);
6741: end if;
6742: --
6743: else
6744: --
6744: --
6745: prev_ptr := current_ptr;
6746: current_ptr := p_int_mlt_thrd_cache(current_ptr).next;
6747: if g_debug then
6748: hr_utility.set_location('pay_balance_pkg.search_rb_cache', 15);
6749: end if;
6750: --
6751: end if;
6752: --
6762: p_current_ptr := current_ptr;
6763: p_previous_ptr := prev_ptr;
6764: --
6765: if g_debug then
6766: hr_utility.set_location('Leaving: pay_balance_pkg.search_rb_cache', 20);
6767: end if;
6768: --
6769: end search_rb_cache;
6770: --
6842: --
6843: if p_amount <> 0 then
6844: --
6845: if g_debug then
6846: hr_utility.set_location('pay_balance_pkg.subtract_from_grp_bal', 25);
6847: end if;
6848: --
6849: if not p_multi_thread then -- i.e. if in single thread mode
6850: --
6850: --
6851: -- update existing row, by subtracting the contributing amount
6852: --
6853: if g_debug then
6854: hr_utility.trace('p_grp_def_bal_id: '||p_grp_def_bal_id);
6855: hr_utility.trace('pactid: '||p_payroll_action_id);
6856: end if;
6857: open get_row_to_update(p_grp_def_bal_id
6858: ,p_payroll_action_id
6851: -- update existing row, by subtracting the contributing amount
6852: --
6853: if g_debug then
6854: hr_utility.trace('p_grp_def_bal_id: '||p_grp_def_bal_id);
6855: hr_utility.trace('pactid: '||p_payroll_action_id);
6856: end if;
6857: open get_row_to_update(p_grp_def_bal_id
6858: ,p_payroll_action_id
6859: ,p_tax_unit_id
6879: --
6880: else
6881: close get_row_to_update;
6882: if g_debug then
6883: hr_utility.set_location('pay_balance_pkg.subtract_from_grp_bal', 40);
6884: hr_utility.trace('p_grp_def_bal_id: '||to_char(p_grp_def_bal_id));
6885: hr_utility.trace('l_grp_run_bal_val: '||to_char(l_grp_run_bal_val));
6886: hr_utility.trace('contrib amt: '||to_char(p_amount));
6887: end if;
6880: else
6881: close get_row_to_update;
6882: if g_debug then
6883: hr_utility.set_location('pay_balance_pkg.subtract_from_grp_bal', 40);
6884: hr_utility.trace('p_grp_def_bal_id: '||to_char(p_grp_def_bal_id));
6885: hr_utility.trace('l_grp_run_bal_val: '||to_char(l_grp_run_bal_val));
6886: hr_utility.trace('contrib amt: '||to_char(p_amount));
6887: end if;
6888: --
6881: close get_row_to_update;
6882: if g_debug then
6883: hr_utility.set_location('pay_balance_pkg.subtract_from_grp_bal', 40);
6884: hr_utility.trace('p_grp_def_bal_id: '||to_char(p_grp_def_bal_id));
6885: hr_utility.trace('l_grp_run_bal_val: '||to_char(l_grp_run_bal_val));
6886: hr_utility.trace('contrib amt: '||to_char(p_amount));
6887: end if;
6888: --
6889: update pay_run_balances
6882: if g_debug then
6883: hr_utility.set_location('pay_balance_pkg.subtract_from_grp_bal', 40);
6884: hr_utility.trace('p_grp_def_bal_id: '||to_char(p_grp_def_bal_id));
6885: hr_utility.trace('l_grp_run_bal_val: '||to_char(l_grp_run_bal_val));
6886: hr_utility.trace('contrib amt: '||to_char(p_amount));
6887: end if;
6888: --
6889: update pay_run_balances
6890: set balance_value = (balance_value - p_amount)
6919: --
6920: if (l_found = TRUE) then
6921: --
6922: if g_debug then
6923: hr_utility.set_location('pay_balance_pkg.subtract_from_grp_bal', 50);
6924: hr_utility.trace('MULTI THREADED UPDATE');
6925: end if;
6926: update pay_run_balances
6927: set balance_value = balance_value - p_amount
6920: if (l_found = TRUE) then
6921: --
6922: if g_debug then
6923: hr_utility.set_location('pay_balance_pkg.subtract_from_grp_bal', 50);
6924: hr_utility.trace('MULTI THREADED UPDATE');
6925: end if;
6926: update pay_run_balances
6927: set balance_value = balance_value - p_amount
6928: where run_balance_id = p_int_mlt_thrd_cache(l_current_ptr).run_balance_id;
6928: where run_balance_id = p_int_mlt_thrd_cache(l_current_ptr).run_balance_id;
6929: --
6930: else -- no row for this balance
6931: if g_debug then
6932: hr_utility.trace('MULTI THREADED INSERT');
6933: hr_utility.set_location('pay_balance_pkg.subtract_from_grp_bal', 55);
6934: end if;
6935: --
6936: --
6929: --
6930: else -- no row for this balance
6931: if g_debug then
6932: hr_utility.trace('MULTI THREADED INSERT');
6933: hr_utility.set_location('pay_balance_pkg.subtract_from_grp_bal', 55);
6934: end if;
6935: --
6936: --
6937: select pay_run_balances_s.nextval
6985: ,p_source_number2
6986: ,p_organization_id
6987: );
6988: if g_debug then
6989: hr_utility.set_location('pay_balance_pkg.subtract_from_grp_bal', 60);
6990: end if;
6991: --
6992: -- cache the row details
6993: --
7018: p_int_mlt_thrd_cache(l_previous_ptr).next := l_cache_ct;
7019: end if;
7020: --
7021: if g_debug then
7022: hr_utility.set_location('pay_balance_pkg.subtract_from_grp_bal', 65);
7023: end if;
7024: --
7025: end if;
7026: --
7026: --
7027: end if;
7028: else -- not > 0
7029: if g_debug then
7030: hr_utility.set_location('pay_balance_pkg.subtract_from_grp_bal', 70);
7031: end if;
7032: end if;
7033: --
7034: end subtract_from_grp_bal;
7114: l_asgbal_cnt number;
7115: l_inp_val_name pay_input_values_f.name%type;
7116: --
7117: BEGIN
7118: g_debug := hr_utility.debug_enabled;
7119: if g_debug then
7120: hr_utility.set_location('Entering: pay_balance_pkg.remove_asg_contribs', 5);
7121: end if;
7122: --
7116: --
7117: BEGIN
7118: g_debug := hr_utility.debug_enabled;
7119: if g_debug then
7120: hr_utility.set_location('Entering: pay_balance_pkg.remove_asg_contribs', 5);
7121: end if;
7122: --
7123: if (p_payroll_action_id <> g_payroll_action) then
7124: --
7334: end if;
7335: --
7336: end if;
7337: if g_debug then
7338: hr_utility.set_location('Leaving: pay_balance_pkg.remove_asg_contribs', 80);
7339: end if;
7340: END remove_asg_contribs;
7341: --
7342: --------------------------------------------------------------------------
7452: l_jur2 pay_run_balances.jurisdiction_comp2%type;
7453: l_jur3 pay_run_balances.jurisdiction_comp3%type;
7454: begin
7455: --
7456: hr_utility.trace('Enter ins_run_balance_bulk ');
7457: i := 0;
7458: lv_run_balance_id := null;
7459: hr_utility.trace('In ins_run_balance_bulk 1');
7460: for cnt in 1..p_output_list.count loop
7455: --
7456: hr_utility.trace('Enter ins_run_balance_bulk ');
7457: i := 0;
7458: lv_run_balance_id := null;
7459: hr_utility.trace('In ins_run_balance_bulk 1');
7460: for cnt in 1..p_output_list.count loop
7461: if (p_output_list(cnt).balance_value <> 0) then
7462: i:=i+1;
7463:
7468: split_jurisdiction(p_output_list(cnt).jurisdiction_code,
7469: l_jur1,
7470: l_jur2,
7471: l_jur3);
7472: hr_utility.trace('In ins_run_balance_bulk 2');
7473: l_pay_run_bal(i).run_balance_id := lv_run_balance_id;
7474: l_pay_run_bal(i).defined_balance_id := p_output_list(cnt).defined_balance_id;
7475: l_pay_run_bal(i).assignment_action_id := p_asgact_id;
7476: l_pay_run_bal(i).payroll_action_id := p_pact_id;
7495: l_pay_run_bal(i).tax_group := null;
7496: end if;
7497: end loop;
7498: --
7499: hr_utility.trace('In ins_run_balance_bulk 3');
7500: forall a in 1..l_pay_run_bal.count
7501: insert into pay_run_balances values l_pay_run_bal(a) ;
7502:
7503: hr_utility.trace('Deleting the values from the l_pay_run_bal table ');
7499: hr_utility.trace('In ins_run_balance_bulk 3');
7500: forall a in 1..l_pay_run_bal.count
7501: insert into pay_run_balances values l_pay_run_bal(a) ;
7502:
7503: hr_utility.trace('Deleting the values from the l_pay_run_bal table ');
7504: l_pay_run_bal.delete;
7505: hr_utility.trace('Leaving ins_run_balance_bulk ');
7506: --
7507: end ins_run_balance_bulk;
7501: insert into pay_run_balances values l_pay_run_bal(a) ;
7502:
7503: hr_utility.trace('Deleting the values from the l_pay_run_bal table ');
7504: l_pay_run_bal.delete;
7505: hr_utility.trace('Leaving ins_run_balance_bulk ');
7506: --
7507: end ins_run_balance_bulk;
7508:
7509: --
7534: begin
7535: --
7536: --
7537: if g_debug then
7538: hr_utility.set_location('pay_balance_pkg.create_run_balance', 30);
7539: hr_utility.trace('Getting Balance'||p_def_bal_id);
7540: end if;
7541: /* First setup the contexts */
7542: if (nvl(p_contexts.tax_unit_id, -999) <> nvl(p_contexts.prv_tax_unit_id, -999)) then
7535: --
7536: --
7537: if g_debug then
7538: hr_utility.set_location('pay_balance_pkg.create_run_balance', 30);
7539: hr_utility.trace('Getting Balance'||p_def_bal_id);
7540: end if;
7541: /* First setup the contexts */
7542: if (nvl(p_contexts.tax_unit_id, -999) <> nvl(p_contexts.prv_tax_unit_id, -999)) then
7543: pay_balance_pkg.set_context('TAX_UNIT_ID', p_contexts.tax_unit_id);
7688: ,p_get_rb_route => false);
7689: --
7690: if (bal_val <> 0) then
7691: if g_debug then
7692: hr_utility.set_location('pay_balance_pkg.create_run_balance', 35);
7693: end if;
7694: ins_run_balance (p_defined_balance_id => p_def_bal_id,
7695: p_eff_date => p_effective_date,
7696: p_bal_val => bal_val,
7713: end if;
7714: end if;
7715: --
7716: if g_debug then
7717: hr_utility.set_location('pay_balance_pkg.create_run_balance', 40);
7718: end if;
7719: --
7720: end create_run_balance;
7721: --
7872: and prrv.result_value is not null;
7873:
7874: begin
7875: --
7876: g_debug := hr_utility.debug_enabled;
7877: if g_debug then
7878: hr_utility.set_location('Entering:'||l_proc, 5);
7879: end if;
7880: --
7874: begin
7875: --
7876: g_debug := hr_utility.debug_enabled;
7877: if g_debug then
7878: hr_utility.set_location('Entering:'||l_proc, 5);
7879: end if;
7880: --
7881: -- Derive information for the specified run result.
7882: --
7894:
7895: if NOT (l_legrule_found and l_save_run_bal_flag = 'Y') then
7896:
7897: if g_debug then
7898: hr_utility.set_location(l_proc, 10);
7899: end if;
7900: -- Saving run balance is not supported, exit the process.
7901: return;
7902: end if;
7901: return;
7902: end if;
7903:
7904: if g_debug then
7905: hr_utility.set_location(l_proc, 20);
7906: end if;
7907: --
7908: -- Setup run result level contexts.
7909: --
7930: )
7931: loop
7932:
7933: if g_debug then
7934: hr_utility.set_location(l_proc, 25);
7935: end if;
7936:
7937: -- copy the run result contexts
7938: l_context := l_rr_ctx;
7979: --
7980: if l_valid_contexts then
7981:
7982: if g_debug then
7983: hr_utility.set_location(l_proc, 30);
7984: hr_utility.trace(' Defined Balance ID: '
7985: || l_rec.defined_balance_id);
7986: end if;
7987: --
7980: if l_valid_contexts then
7981:
7982: if g_debug then
7983: hr_utility.set_location(l_proc, 30);
7984: hr_utility.trace(' Defined Balance ID: '
7985: || l_rec.defined_balance_id);
7986: end if;
7987: --
7988: -- Delete run balance records that may conflict with new one.
8032:
8033: end loop;
8034: --
8035: if g_debug then
8036: hr_utility.set_location('Leaving: '||l_proc, 50);
8037: end if;
8038: end create_rr_asg_balances;
8039: --
8040: --------------------------------------------------------------------------
8122: --
8123: begin
8124: --
8125: if g_debug then
8126: hr_utility.set_location('Entering: pay_balance_pkg.create_set_asg_balance', 5);
8127: end if;
8128: --
8129: -- Check whether the SOURCE_ID, SOURCE_TEXT contexts are used.
8130: l_si_needed_chr := 'N';
8196: for cnt in 1..p_defined_balance_lst.count loop
8197: --
8198: if (p_load_type = 'FORCE') then
8199: if g_debug then
8200: hr_utility.set_location('pay_balance_pkg.create_set_asg_balance', 20);
8201: end if;
8202: delete /* bue_delrb1 */ from pay_run_balances
8203: where defined_balance_id = p_defined_balance_lst(cnt).defined_balance_id
8204: and assignment_action_id = p_asgact_id;
8205: elsif (p_load_type = 'TRUSTED') then
8206: null;
8207: else
8208: if g_debug then
8209: hr_utility.set_location('pay_balance_pkg.create_set_asg_balance', 25);
8210: end if;
8211: declare
8212: l_dummy number;
8213: begin
8221: and assignment_action_id = p_asgact_id
8222: and balance_value <> 0);
8223: --
8224: /* Error, there should be no rows in this mode */
8225: hr_utility.set_message(801,'HR_34723_NO_ROWS_NORMAL_MODE');
8226: hr_utility.raise_error;
8227: --
8228: exception
8229: when no_data_found then
8222: and balance_value <> 0);
8223: --
8224: /* Error, there should be no rows in this mode */
8225: hr_utility.set_message(801,'HR_34723_NO_ROWS_NORMAL_MODE');
8226: hr_utility.raise_error;
8227: --
8228: exception
8229: when no_data_found then
8230: null;
8235: -- Generate the context list
8236: --
8237: l_cnt := 1;
8238: if g_debug then
8239: hr_utility.set_location('pay_balance_pkg.create_set_asg_balance', 27);
8240: end if;
8241: for cxt in get_contexts(p_asgact_id,
8242: l_si_needed_chr,
8243: l_st_needed_chr,
8245: l_st2_needed_chr,
8246: l_sn2_needed_chr,
8247: l_org_needed_chr) loop
8248: if g_debug then
8249: hr_utility.set_location('pay_balance_pkg.create_set_asg_balance', 28);
8250: end if;
8251: l_context_lst(l_cnt).tax_unit_id := cxt.tax_unit_id;
8252: l_context_lst(l_cnt).jurisdiction_code := cxt.jurisdiction_code;
8253: l_context_lst(l_cnt).source_id := cxt.source_id;
8265: --
8266: -- Go Get the balance values
8267: --
8268: if g_debug then
8269: hr_utility.set_location('pay_balance_pkg.create_set_asg_balance', 30);
8270: end if;
8271: for aarec in get_aa (p_asgact_id) loop
8272: pay_balance_pkg.get_value (p_asgact_id,
8273: p_defined_balance_lst,
8278: --
8279: -- Insert the results in the run_balance table.
8280: --
8281: if g_debug then
8282: hr_utility.set_location('pay_balance_pkg.create_set_asg_balance', 35);
8283: end if;
8284:
8285: ins_run_balance_bulk (p_output_list => l_output_list,
8286: p_asgact_id => p_asgact_id,
8290: p_effective_date => aarec.effective_date
8291: );
8292:
8293: if g_debug then
8294: hr_utility.set_location('pay_balance_pkg.create_set_asg_balance', 40);
8295: end if;
8296: /* Commented for bug 6676876, used bulk insert into pay_run_balances for performance improvement
8297: for cnt in 1..l_output_list.count loop
8298: if (l_output_list(cnt).balance_value <> 0) then
8297: for cnt in 1..l_output_list.count loop
8298: if (l_output_list(cnt).balance_value <> 0) then
8299: --
8300: if g_debug then
8301: hr_utility.set_location('pay_balance_pkg.create_set_asg_balance', 40);
8302: end if;
8303: ins_run_balance (p_defined_balance_id => l_output_list(cnt).defined_balance_id,
8304: p_eff_date => aarec.effective_date,
8305: p_bal_val => l_output_list(cnt).balance_value,
8325: */
8326: end loop;
8327: --
8328: if g_debug then
8329: hr_utility.set_location('Leaving: pay_balance_pkg.create_set_asg_balance', 50);
8330: end if;
8331: end create_set_asg_balance;
8332: --
8333: procedure add_grpbal_to_list(p_def_bal_id in number,
8414: --
8415: begin
8416: --
8417: if g_debug then
8418: hr_utility.set_location('Entering pay_balance_pkg.add_grpbal_to_list',10);
8419: hr_utility.trace('p_load_type: '||p_load_type);
8420: end if;
8421: --
8422: l_jc_needed_chr := 'N';
8415: begin
8416: --
8417: if g_debug then
8418: hr_utility.set_location('Entering pay_balance_pkg.add_grpbal_to_list',10);
8419: hr_utility.trace('p_load_type: '||p_load_type);
8420: end if;
8421: --
8422: l_jc_needed_chr := 'N';
8423: l_si_needed_chr := 'N';
8471: if (l_defbal_rec.org_needed = TRUE) then
8472: l_org_needed_chr := 'Y';
8473: end if;
8474: if g_debug then
8475: hr_utility.trace(' contects needed:jc.si.st.sn.tu.st2.td.bd.lu.sn2.org.jlvl'||
8476: l_jc_needed_chr||l_si_needed_chr||l_st_needed_chr||l_sn_needed_chr||l_tu_needed_chr||
8477: l_st2_needed_chr||l_td_needed_chr||l_bd_needed_chr||l_lu_needed_chr||
8478: l_sn2_needed_chr||l_org_needed_chr||l_defbal_rec.jurisdiction_lvl);
8479: end if;
8479: end if;
8480: --
8481: if (p_load_type = 'FORCE') then
8482: if g_debug then
8483: hr_utility.set_location('pay_balance_pkg.add_grpbal_to_list', 20);
8484: end if;
8485: delete /* bue_delrb2 */ from pay_run_balances
8486: where defined_balance_id = p_def_bal_id
8487: and payroll_action_id = p_pactid;
8488: elsif (p_load_type = 'TRUSTED') then
8489: null;
8490: else
8491: if g_debug then
8492: hr_utility.set_location('pay_balance_pkg.add_grpbal_to_list', 25);
8493: end if;
8494: declare
8495: l_dummy number;
8496: begin
8503: and payroll_action_id = p_pactid
8504: and balance_value <> 0);
8505: --
8506: -- Error, there should be no rows in this mode
8507: hr_utility.set_message(801,'HR_34723_NO_ROWS_NORMAL_MODE');
8508: hr_utility.raise_error;
8509: --
8510: exception
8511: when no_data_found then
8504: and balance_value <> 0);
8505: --
8506: -- Error, there should be no rows in this mode
8507: hr_utility.set_message(801,'HR_34723_NO_ROWS_NORMAL_MODE');
8508: hr_utility.raise_error;
8509: --
8510: exception
8511: when no_data_found then
8512: null;
8586: --
8587: end loop;
8588: --
8589: if g_debug then
8590: hr_utility.set_location('Leaving: pay_balance_pkg.add_grpbal_to_list. p_output_list.cnt:'||p_output_list.count, 40);
8591: end if;
8592: --
8593: end add_grpbal_to_list;
8594: -----------------------------------------------------------------------------
8868:
8869: begin
8870: --
8871: if g_debug then
8872: hr_utility.set_location(
8873: 'Entering:pay_balance_pkg.create_all_gre_balances_full',10);
8874: end if;
8875: --
8876: select pbg.business_group_id,
8902: --
8903: if (p_load_type = 'TRUSTED') then
8904: --
8905: if g_debug then
8906: hr_utility.set_location(
8907: 'pay_balance_pkg.create_all_grp_balances_full', 20);
8908: end if;
8909: --
8910: -- Bug 6676876 - split the cursor 'crs_balatt' based on the value of defined_balance_id
8943: elsif (p_load_type = 'FORCE'
8944: and p_def_bal is not null) then
8945: --
8946: if g_debug then
8947: hr_utility.set_location(
8948: 'pay_balance_pkg.create_all_grp_balances_full', 30);
8949: end if;
8950: --
8951: for dbsarec in crs_balatt_1(p_pact_id
8966: end if;
8967: else -- p_delta is Y thus in Delta mode
8968: --
8969: if g_debug then
8970: hr_utility.set_location(
8971: 'pay_balance_pkg.create_all_grp_balances_full', 35);
8972: end if;
8973: --
8974: for dbdrec in crs_delta_balatt(p_pact_id
8986: end if;
8987: --
8988: else
8989: if g_debug then
8990: hr_utility.set_location(
8991: 'pay_balance_pkg.create_all_grp_balances_full', 40);
8992: end if;
8993: --
8994: for dbrec in crs_asgact(p_pact_id,
9009: -- Bug 4318391.
9010: -- Ensure if any defind balances found.
9011: --
9012: if l_output_list.count = 0 then
9013: hr_utility.set_location('pay_balance_pkg.create_all_grp_balances_full', 50);
9014: --
9015: -- Exit this procedure.
9016: --
9017: return;
9087: end loop;
9088: --
9089: l_cnt := 1;
9090: if g_debug then
9091: hr_utility.set_location(
9092: 'pay_balance_pkg.create_all_grp_balances_full', 60);
9093: hr_utility.trace(' contects needed:si.st.sn.st2.sn2.org'||
9094: l_si_needed_chr||l_st_needed_chr||l_sn_needed_chr||
9095: l_st2_needed_chr||
9089: l_cnt := 1;
9090: if g_debug then
9091: hr_utility.set_location(
9092: 'pay_balance_pkg.create_all_grp_balances_full', 60);
9093: hr_utility.trace(' contects needed:si.st.sn.st2.sn2.org'||
9094: l_si_needed_chr||l_st_needed_chr||l_sn_needed_chr||
9095: l_st2_needed_chr||
9096: l_sn2_needed_chr||l_org_needed_chr);
9097: end if;
9103: l_st2_needed_chr,
9104: l_sn2_needed_chr,
9105: l_org_needed_chr) loop
9106: if g_debug then
9107: hr_utility.set_location(
9108: 'pay_balance_pkg.create_all_grp_balances_full', 70);
9109: hr_utility.trace('tu:'||cxt.tax_unit_id||' jc:'||cxt.jurisdiction_code||
9110: ' si:'||cxt.source_id||' st:'||cxt.source_text||
9111: ' sn:'||cxt.source_number||' st2'||cxt.source_text2||
9105: l_org_needed_chr) loop
9106: if g_debug then
9107: hr_utility.set_location(
9108: 'pay_balance_pkg.create_all_grp_balances_full', 70);
9109: hr_utility.trace('tu:'||cxt.tax_unit_id||' jc:'||cxt.jurisdiction_code||
9110: ' si:'||cxt.source_id||' st:'||cxt.source_text||
9111: ' sn:'||cxt.source_number||' st2'||cxt.source_text2||
9112: ' td:'||cxt.time_definition_id||' bd:'||cxt.balance_date||
9113: ' lu:'||cxt.local_unit_id||' sn2:'||cxt.source_number2||
9131: --
9132: -- Go Get the balance values
9133: --
9134: if g_debug then
9135: hr_utility.set_location(
9136: 'pay_balance_pkg.create_all_grp_balances_full', 80);
9137: end if;
9138: --
9139: for aarec in get_aa (p_pact_id) loop
9145: --
9146: -- Insert the results in the run_balance table.
9147: --
9148: if g_debug then
9149: hr_utility.set_location(
9150: 'pay_balance_pkg.create_all_grp_balances_full', 85);
9151: end if;
9152:
9153:
9158: p_action_sequence => null,
9159: p_effective_date => aarec.effective_date
9160: );
9161: if g_debug then
9162: hr_utility.set_location(
9163: 'pay_balance_pkg.create_all_grp_balances_full', 90);
9164: end if;
9165: /* Commented for bug 6676876, used bulk insert into pay_run_balances for performance improvement
9166: for cnt in 1..l_output_list.count loop
9166: for cnt in 1..l_output_list.count loop
9167: if (l_output_list(cnt).balance_value <> 0) then
9168: --
9169: if g_debug then
9170: hr_utility.set_location(
9171: 'pay_balance_pkg.create_all_grp_balances_full', 90);
9172: end if;
9173: --
9174: ins_run_balance (p_defined_balance_id => l_output_list(cnt).defined_balance_id,
9196: */
9197: end loop;
9198: --
9199: if g_debug then
9200: hr_utility.set_location(
9201: 'Leaving: pay_balance_pkg.create_all_grp_balances_full', 95);
9202: end if;
9203: /*Bug 9315998 */
9204: lt_tab_crs_balatt_2_grp.delete;
9288: --
9289: begin
9290: --
9291: if g_debug then
9292: hr_utility.set_location('Entering pay_balance_pkg.add_asgbal_to_list',10);
9293: hr_utility.trace('p_load_type: '||p_load_type);
9294: end if;
9295: l_jc_needed_chr := 'N';
9296: l_si_needed_chr := 'N';
9289: begin
9290: --
9291: if g_debug then
9292: hr_utility.set_location('Entering pay_balance_pkg.add_asgbal_to_list',10);
9293: hr_utility.trace('p_load_type: '||p_load_type);
9294: end if;
9295: l_jc_needed_chr := 'N';
9296: l_si_needed_chr := 'N';
9297: l_st_needed_chr := 'N';
9349: end if;
9350: --
9351: if (p_load_type = 'FORCE') then
9352: if g_debug then
9353: hr_utility.set_location('pay_balance_pkg.add_asgbal_to_list', 20);
9354: end if;
9355: delete /* bue_delrb3 */ from pay_run_balances
9356: where defined_balance_id = p_def_bal_id
9357: and assignment_action_id = p_asgact_id;
9358: elsif (p_load_type = 'TRUSTED') then
9359: null;
9360: else
9361: if g_debug then
9362: hr_utility.set_location('pay_balance_pkg.add_asgbal_to_list', 25);
9363: end if;
9364: declare
9365: l_dummy number;
9366: begin
9373: and assignment_action_id = p_asgact_id
9374: and balance_value <> 0);
9375: --
9376: -- Error, there should be no rows in this mode
9377: hr_utility.set_message(801,'HR_34723_NO_ROWS_NORMAL_MODE');
9378: hr_utility.raise_error;
9379: --
9380: exception
9381: when no_data_found then
9374: and balance_value <> 0);
9375: --
9376: -- Error, there should be no rows in this mode
9377: hr_utility.set_message(801,'HR_34723_NO_ROWS_NORMAL_MODE');
9378: hr_utility.raise_error;
9379: --
9380: exception
9381: when no_data_found then
9382: null;
9457: --
9458: end loop;
9459: --
9460: if g_debug then
9461: hr_utility.set_location('Leaving: pay_balance_pkg.add_asgbal_to_list', 40);
9462: end if;
9463: --
9464: end add_asgbal_to_list;
9465: --
9775:
9776: begin
9777: --
9778: if g_debug then
9779: hr_utility.set_location(
9780: 'Entering:pay_balance_pkg.create_all_asg_balances_full',10);
9781: end if;
9782: --
9783: select pbg.business_group_id,
9804: -- can be allowed. The run balances will be deleted in procedure
9805: -- add_asgbal_to_list.
9806: --
9807: if g_debug and p_delta is not null then
9808: hr_utility.trace('p_delta is: '||p_delta);
9809: end if;
9810: if (p_delta = 'N'
9811: or p_delta is null) then -- if NOT in DELTA MODE
9812: --
9812: --
9813: if (p_load_type = 'TRUSTED') then
9814: --
9815: if g_debug then
9816: hr_utility.set_location(
9817: 'pay_balance_pkg.create_all_asg_balances_full', 20);
9818: end if;
9819: -- Bug 6676876 - split the cursor 'crs_balatt' based on the value of defined_balance_id
9820: if p_def_bal is NULL then
9848: elsif (p_load_type = 'FORCE'
9849: and p_def_bal is not null) then
9850: --
9851: if g_debug then
9852: hr_utility.set_location(
9853: 'pay_balance_pkg.create_all_asg_balances_full', 30);
9854: end if;
9855: --
9856: -- bug 11874880 run balance need checked earlier
9867: end if;
9868: else -- p_delta is Y thus in DELTA mode
9869: --
9870: if g_debug then
9871: hr_utility.set_location(
9872: 'pay_balance_pkg.create_all_asg_balances_full', 35);
9873: end if;
9874: --
9875: --
9897: end if;
9898: --
9899: else
9900: if g_debug then
9901: hr_utility.set_location(
9902: 'pay_balance_pkg.create_all_asg_balances_full', 40);
9903: end if;
9904: --
9905: for dbrec in crs_asgact(p_asgact_id,
9989: --
9990: end loop;
9991: l_cnt := 1;
9992: if g_debug then
9993: hr_utility.set_location(
9994: 'pay_balance_pkg.create_all_asg_balances_full', 50);
9995: end if;
9996: for cxt in get_contexts_2(p_asgact_id,
9997: l_si_needed_chr,
10000: l_st2_needed_chr,
10001: l_sn2_needed_chr,
10002: l_org_needed_chr) loop
10003: if g_debug then
10004: hr_utility.set_location(
10005: 'pay_balance_pkg.create_all_asg_balances_full', 60);
10006: end if;
10007: l_context_lst(l_cnt).tax_unit_id := cxt.tax_unit_id;
10008: l_context_lst(l_cnt).jurisdiction_code := cxt.jurisdiction_code;
10021: --
10022: -- Go Get the balance values
10023: --
10024: if g_debug then
10025: hr_utility.set_location(
10026: 'pay_balance_pkg.create_all_asg_balances_full', 70);
10027: end if;
10028: for aarec in get_aa (p_asgact_id) loop
10029: pay_balance_pkg.get_value_int_batch (p_asgact_id,
10034: --
10035: -- Insert the results in the run_balance table.
10036: --
10037: if g_debug then
10038: hr_utility.set_location(
10039: 'pay_balance_pkg.create_all_asg_balances_full', 80);
10040: end if;
10041:
10042:
10048: p_effective_date => aarec.effective_date
10049: );
10050: --
10051: if g_debug then
10052: hr_utility.set_location(
10053: 'pay_balance_pkg.create_all_asg_balances_full', 90);
10054: end if;
10055: /* Commented for bug 6676876, used bulk insert into pay_run_balances for performance improvement
10056: for cnt in 1..l_output_list.count loop
10056: for cnt in 1..l_output_list.count loop
10057: if (l_output_list(cnt).balance_value <> 0) then
10058: --
10059: if g_debug then
10060: hr_utility.set_location(
10061: 'pay_balance_pkg.create_all_asg_balances_full', 90);
10062: end if;
10063: ins_run_balance (p_defined_balance_id => l_output_list(cnt).defined_balance_id,
10064: p_eff_date => aarec.effective_date,
10086: end loop;
10087: --
10088: else -- no balances returned from cursors crs_balatt or crs_asgact, so do
10089: -- nothing
10090: hr_utility.set_location('pay_balance_pkg.create_all_asg_balances_full',98);
10091: end if;
10092: --
10093: if g_debug then
10094: hr_utility.set_location(
10090: hr_utility.set_location('pay_balance_pkg.create_all_asg_balances_full',98);
10091: end if;
10092: --
10093: if g_debug then
10094: hr_utility.set_location(
10095: 'Leaving: pay_balance_pkg.create_all_asg_balances_full', 100);
10096: end if;
10097:
10098: /*Bug 9315998*/
10172: l_cnt number;
10173: l_delta boolean;
10174: --
10175: begin
10176: g_debug := hr_utility.debug_enabled;
10177: if g_debug then
10178: hr_utility.set_location('Entering: pay_balance_pkg.create_all_asg_balances', 5);
10179: end if;
10180: --
10174: --
10175: begin
10176: g_debug := hr_utility.debug_enabled;
10177: if g_debug then
10178: hr_utility.set_location('Entering: pay_balance_pkg.create_all_asg_balances', 5);
10179: end if;
10180: --
10181: l_balance_lst.delete;
10182: --
10191: end if;
10192: else
10193: bal_ret_buffer_sz := 1000;
10194: end if;
10195: hr_utility.trace('Action Parameter BAL_RETRIEVAL_BUFFER_SIZE = '||bal_ret_buffer_sz);
10196: --
10197: /* First get the legislation rule */
10198: begin
10199: select plr.rule_mode
10215: l_cnt := 1;
10216: if (save_run_bals = 'Y') then
10217: --
10218: if g_debug then
10219: hr_utility.set_location('pay_balance_pkg.create_all_asg_balances', 25);
10220: end if;
10221: --
10222: if (p_bal_list = 'ALL'
10223: or ( p_bal_list = 'INVALID'
10238: for fullrec in crs_invalid(p_asgact_id) loop
10239: --
10240: l_balance_lst(l_cnt).defined_balance_id := fullrec.defined_balance_id;
10241: if g_debug then
10242: hr_utility.trace('Added Def Bal ID '||
10243: l_balance_lst(l_cnt).defined_balance_id);
10244: end if;
10245: --
10246: /* Have we reached the buffer limit */
10270: --
10271: end if;
10272: --
10273: if g_debug then
10274: hr_utility.set_location('Leaving: pay_balance_pkg.create_all_asg_balances', 30);
10275: end if;
10276: end create_all_asg_balances;
10277: --
10278: --------------------------------------------------------------------------
10356: --
10357: begin
10358: --
10359: if g_debug then
10360: hr_utility.set_location('Entering: pay_balance_pkg.create_set_group_balance', 5);
10361: end if;
10362: --
10363: -- Check whether the SOURCE_ID, SOURCE_TEXT contexts are used.
10364: l_si_needed_chr := 'N';
10430: for cnt in 1..p_defined_balance_lst.count loop
10431: --
10432: if (p_load_type = 'FORCE') then
10433: if g_debug then
10434: hr_utility.set_location('pay_balance_pkg.create_set_group_balance', 20);
10435: end if;
10436: delete /* bue_delrb4 */ from pay_run_balances
10437: where defined_balance_id = p_defined_balance_lst(cnt).defined_balance_id
10438: and payroll_action_id = p_pact_id;
10439: elsif (p_load_type = 'TRUSTED') then
10440: null;
10441: else
10442: if g_debug then
10443: hr_utility.set_location('pay_balance_pkg.create_set_group_balance', 25);
10444: end if;
10445: declare
10446: l_dummy number;
10447: begin
10454: and payroll_action_id = p_pact_id
10455: and balance_value <> 0);
10456: --
10457: /* Error, there should be no rows in this mode */
10458: hr_utility.set_message(801,'HR_34723_NO_ROWS_NORMAL_MODE');
10459: hr_utility.raise_error;
10460: --
10461: exception
10462: when no_data_found then
10455: and balance_value <> 0);
10456: --
10457: /* Error, there should be no rows in this mode */
10458: hr_utility.set_message(801,'HR_34723_NO_ROWS_NORMAL_MODE');
10459: hr_utility.raise_error;
10460: --
10461: exception
10462: when no_data_found then
10463: null;
10468: -- Generate the context list
10469: --
10470: l_cnt := 1;
10471: if g_debug then
10472: hr_utility.set_location('pay_balance_pkg.create_set_group_balance', 27);
10473: end if;
10474: for cxt in get_contexts(p_pact_id,
10475: l_si_needed_chr,
10476: l_st_needed_chr,
10478: l_st2_needed_chr,
10479: l_sn2_needed_chr,
10480: l_org_needed_chr) loop
10481: if g_debug then
10482: hr_utility.set_location('pay_balance_pkg.create_set_group_balance', 28);
10483: end if;
10484: l_context_lst(l_cnt).tax_unit_id := cxt.tax_unit_id;
10485: l_context_lst(l_cnt).jurisdiction_code := cxt.jurisdiction_code;
10486: l_context_lst(l_cnt).source_id := cxt.source_id;
10498: --
10499: -- Go Get the balance values
10500: --
10501: if g_debug then
10502: hr_utility.set_location('pay_balance_pkg.create_set_group_balance', 30);
10503: end if;
10504: for aarec in get_aa (p_pact_id) loop
10505: pay_balance_pkg.get_value (aarec.assignment_action_id,
10506: p_defined_balance_lst,
10511: --
10512: -- Insert the results in the run_balance table.
10513: --
10514: if g_debug then
10515: hr_utility.set_location('pay_balance_pkg.create_set_group_balance', 35);
10516: end if;
10517:
10518: ins_run_balance_bulk (p_output_list => l_output_list,
10519: p_asgact_id => null,
10523: p_effective_date => aarec.effective_date
10524: );
10525: --
10526: if g_debug then
10527: hr_utility.set_location('pay_balance_pkg.create_set_group_balance', 40);
10528: end if;
10529: /* Commented for bug 6676876, used bulk insert into pay_run_balances for performance improvement
10530: for cnt in 1..l_output_list.count loop
10531: if g_debug then
10528: end if;
10529: /* Commented for bug 6676876, used bulk insert into pay_run_balances for performance improvement
10530: for cnt in 1..l_output_list.count loop
10531: if g_debug then
10532: hr_utility.trace('Bal Value = '||l_output_list(cnt).balance_value);
10533: end if;
10534: if (l_output_list(cnt).balance_value <> 0) then
10535: --
10536: if g_debug then
10533: end if;
10534: if (l_output_list(cnt).balance_value <> 0) then
10535: --
10536: if g_debug then
10537: hr_utility.set_location('pay_balance_pkg.create_set_group_balance', 40);
10538: end if;
10539: ins_run_balance (p_defined_balance_id => l_output_list(cnt).defined_balance_id,
10540: p_eff_date => aarec.effective_date,
10541: p_bal_val => l_output_list(cnt).balance_value,
10561: */
10562: end loop;
10563: --
10564: if g_debug then
10565: hr_utility.set_location('Leaving: pay_balance_pkg.create_set_group_balance', 50);
10566: end if;
10567: end create_set_group_balance;
10568: --------------------------------------------------------------------------
10569: -- procedure create_all_group_balances
10632: l_balance_lst t_balance_value_tab;
10633: l_cnt number;
10634: --
10635: begin
10636: g_debug := hr_utility.debug_enabled;
10637: if g_debug then
10638: hr_utility.set_location('Entering: pay_balance_pkg.create_all_group_balances', 5);
10639: end if;
10640: --
10634: --
10635: begin
10636: g_debug := hr_utility.debug_enabled;
10637: if g_debug then
10638: hr_utility.set_location('Entering: pay_balance_pkg.create_all_group_balances', 5);
10639: end if;
10640: --
10641: l_balance_lst.delete;
10642: --
10651: end if;
10652: else
10653: bal_ret_buffer_sz := 1000;
10654: end if;
10655: hr_utility.trace('Action Parameter BAL_RETRIEVAL_BUFFER_SIZE = '||bal_ret_buffer_sz);
10656: --
10657: /* First get the legislation rule */
10658: begin
10659: select plr.rule_mode
10672: --
10673: if (save_run_bals = 'Y') then
10674: --
10675: if g_debug then
10676: hr_utility.set_location('pay_balance_pkg.create_all_group_balances', 25);
10677: end if;
10678: l_cnt := 1;
10679: --
10680: if (p_bal_list = 'ALL'
10696: for fullrec in crs_invalid(p_pact_id) loop
10697: --
10698: l_balance_lst(l_cnt).defined_balance_id := fullrec.defined_balance_id;
10699: if g_debug then
10700: hr_utility.trace('Added Def Bal ID '||
10701: l_balance_lst(l_cnt).defined_balance_id);
10702: end if;
10703: --
10704: /* Have we reached the buffer limit */
10727: --
10728: end if;
10729: --
10730: if g_debug then
10731: hr_utility.set_location('Leaving: pay_balance_pkg.create_all_group_balances', 30);
10732: end if;
10733: end create_all_group_balances;
10734: --
10735: --------------------------------------------------------------------------
10811: l_bal_load_date pay_balance_validation.balance_load_date%type;
10812: l_rr_exists number;
10813: --
10814: BEGIN
10815: hr_utility.set_location('Entering: pay_balance_pkg.initialise_run_balance', 10);
10816: --
10817: -- we need to determine a legislation code; for core defined balances it will
10818: -- be null.
10819: --
10820: if p_legislation_code is null then
10821: if p_business_group_id is null then
10822: -- core row
10823: l_leg_code := '';
10824: hr_utility.set_location('pay_balance_pkg.initialise_run_balance', 20);
10825: else -- bg not nulli, so user row
10826: hr_utility.set_location('pay_balance_pkg.initialise_run_balance', 30);
10827: --
10828: open get_leg_code(p_business_group_id);
10822: -- core row
10823: l_leg_code := '';
10824: hr_utility.set_location('pay_balance_pkg.initialise_run_balance', 20);
10825: else -- bg not nulli, so user row
10826: hr_utility.set_location('pay_balance_pkg.initialise_run_balance', 30);
10827: --
10828: open get_leg_code(p_business_group_id);
10829: fetch get_leg_code into l_leg_code;
10830: if get_leg_code%notfound then
10829: fetch get_leg_code into l_leg_code;
10830: if get_leg_code%notfound then
10831: close get_leg_code;
10832: -- should raise error, but should never be raised so ok
10833: hr_utility.set_location('pay_balance_pkg.initialise_run_balance', 40);
10834: end if;
10835: end if;
10836: else -- leg row
10837: l_leg_code := p_legislation_code;
10834: end if;
10835: end if;
10836: else -- leg row
10837: l_leg_code := p_legislation_code;
10838: hr_utility.set_location('pay_balance_pkg.initialise_run_balance', 50);
10839: end if;
10840: --
10841: open get_dim_level(p_baldim_id);
10842: fetch get_dim_level into l_dim_level;
10850: fetch check_run_results into l_rr_exists;
10851: close check_run_results;
10852: --
10853: if l_dim_level = 'ASG' or l_dim_level = 'GRP' then
10854: hr_utility.set_location('pay_balance_pkg.initialise_run_balance', 70);
10855: --
10856: for each_bg in enabled_bg(p_business_group_id, l_leg_code, l_dim_level)
10857: loop
10858: --
10863: else
10864: l_bal_load_date := fnd_date.canonical_to_date('0001/01/01');
10865: end if;
10866: --
10867: hr_utility.set_location('pay_balance_pkg.initialise_run_balance', 80);
10868: hr_utility.trace('def_bal: '||to_char(p_defbal_id));
10869: hr_utility.trace('bg: '||to_char(each_bg.business_group_id));
10870: hr_utility.trace('bal_load_date: '||to_char(l_bal_load_date,'DD-MON-YYYY'));
10871: --
10864: l_bal_load_date := fnd_date.canonical_to_date('0001/01/01');
10865: end if;
10866: --
10867: hr_utility.set_location('pay_balance_pkg.initialise_run_balance', 80);
10868: hr_utility.trace('def_bal: '||to_char(p_defbal_id));
10869: hr_utility.trace('bg: '||to_char(each_bg.business_group_id));
10870: hr_utility.trace('bal_load_date: '||to_char(l_bal_load_date,'DD-MON-YYYY'));
10871: --
10872: insert into pay_balance_validation
10865: end if;
10866: --
10867: hr_utility.set_location('pay_balance_pkg.initialise_run_balance', 80);
10868: hr_utility.trace('def_bal: '||to_char(p_defbal_id));
10869: hr_utility.trace('bg: '||to_char(each_bg.business_group_id));
10870: hr_utility.trace('bal_load_date: '||to_char(l_bal_load_date,'DD-MON-YYYY'));
10871: --
10872: insert into pay_balance_validation
10873: (BALANCE_VALIDATION_ID
10866: --
10867: hr_utility.set_location('pay_balance_pkg.initialise_run_balance', 80);
10868: hr_utility.trace('def_bal: '||to_char(p_defbal_id));
10869: hr_utility.trace('bg: '||to_char(each_bg.business_group_id));
10870: hr_utility.trace('bal_load_date: '||to_char(l_bal_load_date,'DD-MON-YYYY'));
10871: --
10872: insert into pay_balance_validation
10873: (BALANCE_VALIDATION_ID
10874: ,DEFINED_BALANCE_ID
10885: from pay_balance_validation
10886: where defined_balance_id = p_defbal_id
10887: and business_group_id = each_bg.business_group_id);
10888: --
10889: hr_utility.set_location('pay_balance_pkg.initialise_run_balance', 90);
10890: --
10891: end loop;
10892: hr_utility.set_location('pay_balance_pkg.initialise_run_balance', 100);
10893: --
10888: --
10889: hr_utility.set_location('pay_balance_pkg.initialise_run_balance', 90);
10890: --
10891: end loop;
10892: hr_utility.set_location('pay_balance_pkg.initialise_run_balance', 100);
10893: --
10894: else -- l_dim_level is null
10895: --
10896: -- this is not a valid run balance defined balance, so do nothing
10894: else -- l_dim_level is null
10895: --
10896: -- this is not a valid run balance defined balance, so do nothing
10897: --
10898: hr_utility.set_location('pay_balance_pkg.initialise_run_balance', 110);
10899: end if;
10900: hr_utility.set_location('Leaving: pay_balance_pkg.initialise_run_balance', 120);
10901: end initialise_run_balance;
10902: --------------------------------------------------------------------------
10896: -- this is not a valid run balance defined balance, so do nothing
10897: --
10898: hr_utility.set_location('pay_balance_pkg.initialise_run_balance', 110);
10899: end if;
10900: hr_utility.set_location('Leaving: pay_balance_pkg.initialise_run_balance', 120);
10901: end initialise_run_balance;
10902: --------------------------------------------------------------------------
10903: -- procedure set_check_latest_balances
10904: -- This procedure sets the HRASSACT CHECK_LATEST_BALANCES global
10907: procedure set_check_latest_balances
10908: is
10909: --
10910: begin
10911: g_debug := hr_utility.debug_enabled;
10912: if g_debug then
10913: hr_utility.set_location('Entering: pay_balance_pkg.set_check_latest_balances',5);
10914: end if;
10915: --
10909: --
10910: begin
10911: g_debug := hr_utility.debug_enabled;
10912: if g_debug then
10913: hr_utility.set_location('Entering: pay_balance_pkg.set_check_latest_balances',5);
10914: end if;
10915: --
10916: HRASSACT.CHECK_LATEST_BALANCES := TRUE;
10917: --
10915: --
10916: HRASSACT.CHECK_LATEST_BALANCES := TRUE;
10917: --
10918: if g_debug then
10919: hr_utility.set_location('Exiting: pay_balance_pkg.set_check_latest_balances',5);
10920: end if;
10921: --
10922: end set_check_latest_balances;
10923: --------------------------------------------------------------------------
10928: procedure unset_check_latest_balances
10929: is
10930: --
10931: begin
10932: g_debug := hr_utility.debug_enabled;
10933: if g_debug then
10934: hr_utility.set_location('Entering: pay_balance_pkg.unset_check_latest_balances',5);
10935: end if;
10936: --
10930: --
10931: begin
10932: g_debug := hr_utility.debug_enabled;
10933: if g_debug then
10934: hr_utility.set_location('Entering: pay_balance_pkg.unset_check_latest_balances',5);
10935: end if;
10936: --
10937: HRASSACT.CHECK_LATEST_BALANCES := FALSE;
10938: --
10936: --
10937: HRASSACT.CHECK_LATEST_BALANCES := FALSE;
10938: --
10939: if g_debug then
10940: hr_utility.set_location('Exiting: pay_balance_pkg.unset_check_latest_balances',5);
10941: end if;
10942: --
10943: end unset_check_latest_balances;
10944: --------------------------------------------------------------------------
10957: --
10958: begin
10959: --
10960: if g_debug then
10961: hr_utility.set_location('enter:pay_bal_pkg.create_asg_balance',10);
10962: end if;
10963: create_all_asg_balances_full(p_asgact_id => p_asgact_id
10964: ,p_load_type => p_load_type
10965: ,p_def_bal => p_def_bal_id
10968: ,p_delta => p_delta
10969: );
10970: --
10971: if g_debug then
10972: hr_utility.set_location('leave:pay_bal_pkg.create_asg_balance',20);
10973: end if;
10974: end create_asg_balance;
10975: --
10976: --------------------------------------------------------------------------
10986: --
10987: begin
10988: --
10989: if g_debug then
10990: hr_utility.set_location('Entering:pay_bal_pkg.create_group_balance',10);
10991: end if;
10992: --
10993: create_all_grp_balances_full(p_pact_id => p_pact_id
10994: ,p_load_type => p_load_type
10998: ,p_delta => p_delta
10999: );
11000: --
11001: if g_debug then
11002: hr_utility.set_location('Leaving:pay_bal_pkg.create_group_balance',20);
11003: end if;
11004: end create_group_balance;
11005: --
11006: procedure maintain_balances_for_action(p_asg_action in number
11071:
11072:
11073: begin
11074: if g_debug then
11075: hr_utility.set_location('hrassact.reversal',50);
11076: end if;
11077: --
11078: select paa.assignment_id,
11079: ppa.effective_date,
11097: p_delta => null);
11098: --
11099: --
11100: if g_debug then
11101: hr_utility.set_location('hrassact.reversal',55);
11102: end if;
11103: --
11104: -- 2nd the group level run balances
11105: --
11103: --
11104: -- 2nd the group level run balances
11105: --
11106: if g_debug then
11107: hr_utility.set_location('hrassact.reversal',60);
11108: end if;
11109: --
11110: If not hrassact.gv_multi_reversal then /*Bug 7652030 Not calling create_all_group_balances for Batch reversal*/
11111: pay_balance_pkg.create_all_group_balances(l_pactid,
11167: --
11168: -- delete latest balances
11169: --
11170: if g_debug then
11171: hr_utility.set_location('hrassact.reversal', 70);
11172: end if;
11173:
11174: --
11175: -- NB could enhance to
11182: --
11183: -- Support maintenance of latest balances
11184: --
11185: if g_debug then
11186: hr_utility.set_location('hrassact.reversal', 80);
11187: end if;
11188: --
11189: hr_utility.trace('leg_code: '||leg_code);
11190: pay_core_utils.get_leg_context_iv_name('SOURCE_ID'
11185: if g_debug then
11186: hr_utility.set_location('hrassact.reversal', 80);
11187: end if;
11188: --
11189: hr_utility.trace('leg_code: '||leg_code);
11190: pay_core_utils.get_leg_context_iv_name('SOURCE_ID'
11191: ,leg_code
11192: ,l_src_iv
11193: ,l_iv_found);
11194: if (not l_iv_found) then
11195: l_src_iv := null;
11196: else
11197: l_si_needed := 'Y';
11198: hr_utility.trace('l_src_iv: '||l_src_iv);
11199: end if;
11200: --
11201: pay_core_utils.get_leg_context_iv_name('SOURCE_NUMBER'
11202: ,leg_code
11205: if (not l_num_found) then
11206: l_src_num := null;
11207: else
11208: l_sn_needed := 'Y';
11209: hr_utility.trace('l_src_num: '||l_src_num);
11210: end if;
11211: --
11212: pay_core_utils.get_leg_context_iv_name('SOURCE_NUMBER2'
11213: ,leg_code
11216: if (not l_num_found) then
11217: l_src_num2 := null;
11218: else
11219: l_sn2_needed := 'Y';
11220: hr_utility.trace('l_src_num2: '||l_src_num2);
11221: end if;
11222: pay_core_utils.get_leg_context_iv_name('ORGANIZATION_ID'
11223: ,leg_code
11224: ,l_org_id_iv
11226: if (not l_num_found) then
11227: l_org_id_iv := null;
11228: else
11229: l_org_needed := 'Y';
11230: hr_utility.trace('l_org_id_iv: '||l_org_id_iv);
11231: end if;
11232:
11233: pay_core_utils.get_leg_context_iv_name('SOURCE_TEXT',
11234: leg_code,
11379:
11380: end if;
11381: --
11382: if g_debug then
11383: hr_utility.set_location('Leaving: hrassact.reversal', 100);
11384: end if;
11385:
11386: end maintain_balances_for_action;
11387: --------------------------------------------------------------------------
11461: l_rrv_count number;
11462: l_start_time number;
11463: begin
11464: if g_debug then
11465: hr_utility.set_location('entering pay_balance_pkg.run_balance_row_status', 10);
11466: end if;
11467: if ((p_defined_balance_id <> g_defined_balance_id) or (g_defined_balance_id is null)) then
11468: select balance_type_id into g_balance_type_id
11469: from pay_defined_balances
11499: if (((p_assignment_id <> g_assignment_id) or (g_assignment_id is null)) or
11500: ((p_defined_balance_id <> g_defined_balance_id) or (g_defined_balance_id is null))) then --{ build gtab
11501: if g_debug then
11502: l_start_time := dbms_utility.get_time;
11503: hr_utility.set_location('pay_balance_pkg.run_balance_row_status. init gtab asg:'||p_assignment_id, 22);
11504: end if;
11505: g_assignment_id := p_assignment_id;
11506: g_defined_balance_id := p_defined_balance_id;
11507: g_asg_actions_tab.delete;
11508:
11509: if g_feeds_tab.count > 0 then --{ feed exists
11510: if g_feeds_tab.count < 5 then --{ primary balance
11511: if g_debug then
11512: hr_utility.set_location('pay_balance_pkg.run_balance_row_status. primbal feed.cnt:'||g_feeds_tab.count, 24);
11513: end if;
11514: l_rrv_count := 0;
11515: for a in g_feeds_tab.FIRST .. g_feeds_tab.LAST loop --{ g_feeds
11516: for rrv in rr_run_values(g_feeds_tab(a).input_value_id, g_feeds_tab(a).element_type_id,
11521: g_asg_actions_tab(l_rrv_count).assignment_action_id := rrv.assignment_action_id;
11522: g_asg_actions_tab(l_rrv_count).feed_count := rrv.feed_count;
11523: g_asg_actions_tab(l_rrv_count).asg_run := rrv.value;
11524: --if g_debug then
11525: -- hr_utility.trace('rr_run_values.'||l_rrv_count||' iv:'|| g_feeds_tab(a).input_value_id||' rrv.value:'||rrv.value||
11526: -- ' fed:'||rrv.feed_count||' rrv.assact:'||rrv.assignment_action_id);
11527: --end if;
11528: end loop; --} rr_run_values
11529: end loop; --} g_feeds
11529: end loop; --} g_feeds
11530:
11531: else -- more than 4 feeds
11532: if g_debug then
11533: hr_utility.set_location('pay_balance_pkg.run_balance_row_status. classbal feed.cnt:'||g_feeds_tab.count, 26);
11534: end if;
11535: select /*+ ORDERED INDEX(assact PAY_ASSIGNMENT_ACTIONS_N51)
11536: INDEX(pact PAY_PAYROLL_ACTIONS_PK)
11537: INDEX(rr PAY_RUN_RESULTS_N50)
11566: group by assact.assignment_action_id order by assact.assignment_action_id;
11567: end if; --} primary balance
11568: end if; --} feeds exist
11569: if g_debug then
11570: hr_utility.trace('gtab build rows:'||to_char(g_asg_actions_tab.count)||' time(hs):'||to_char(dbms_utility.get_time - l_start_time));
11571: end if;
11572: end if; --} build gtab
11573:
11574: open csr_run_bal( p_defined_balance_id, p_assignment_action_id);
11574: open csr_run_bal( p_defined_balance_id, p_assignment_action_id);
11575: fetch csr_run_bal into rb_row;
11576: if csr_run_bal%notfound then l_rb_status := 'N';
11577: if g_debug then
11578: hr_utility.set_location('pay_balance_pkg.run_balance_row_status. no rb', 30);
11579: end if;
11580: -- if there is no rb row check if its a candidate rb
11581: if g_debug then
11582: hr_utility.set_location('pay_balance_pkg.run_balance_row_status. search gtab ', 32);
11578: hr_utility.set_location('pay_balance_pkg.run_balance_row_status. no rb', 30);
11579: end if;
11580: -- if there is no rb row check if its a candidate rb
11581: if g_debug then
11582: hr_utility.set_location('pay_balance_pkg.run_balance_row_status. search gtab ', 32);
11583: end if;
11584: If g_asg_actions_tab.count > 0 then
11585: for actrec in g_asg_actions_tab.first..g_asg_actions_tab.last loop
11586: if p_assignment_action_id = g_asg_actions_tab(actrec).assignment_action_id then
11595: if (( l_rb_status = 'Z' ) and ( p_from_date is not null ) and
11596: ( rb_row.effective_date < p_from_date)) then
11597: l_rb_status := 'O';
11598: if g_debug then
11599: hr_utility.set_location('pay_balance_pkg.run_balance_row_status. old rb', 40);
11600: end if;
11601: end if;
11602:
11603: if ( l_rb_status = 'Z' ) then
11603: if ( l_rb_status = 'Z' ) then
11604: fetch csr_run_bal into rb_row2;
11605: if csr_run_bal%found then l_rb_status := 'U';
11606: if g_debug then
11607: hr_utility.set_location('pay_balance_pkg.run_balance_row_status. multiple rbs', 50);
11608: end if;
11609: end if;
11610: end if;
11611: -- check balance value is correct against asg_run if its cached and no context other that tax unit
11622: and rb_row.LOCAL_UNIT_ID is null
11623: and rb_row.ORGANIZATION_ID is null
11624: then
11625: if g_debug then
11626: hr_utility.set_location('pay_balance_pkg.run_balance_row_status. fetch gtab.asg_run value ', 60);
11627: end if;
11628: -- do we have balance in gtab
11629: l_value := 0;
11630: if ((g_asg_actions_tab.count > 0) and (p_assignment_id = nvl(g_assignment_id,-1)) and
11642: end if;
11643: -- check balance value is correct using rr_route
11644: if l_rb_status = 'Z' then
11645: if g_debug then
11646: hr_utility.set_location('pay_balance_pkg.run_balance_row_status. fetch rr value ', 70);
11647: end if;
11648: l_value := pay_balance_pkg.get_value(p_defined_balance_id,
11649: p_assignment_action_id,
11650: rb_row.tax_unit_id,
11670: end if;
11671: end if;
11672: close csr_run_bal;
11673: if g_debug then
11674: hr_utility.trace(' p_asg_action_id:'||p_assignment_action_id||
11675: ' p_def_bal_id:'||p_defined_balance_id||
11676: ' l_rb_status:'||l_rb_status);
11677: hr_utility.set_location('leaving pay_balance_pkg.run_balance_row_status ', 90);
11678: end if;
11673: if g_debug then
11674: hr_utility.trace(' p_asg_action_id:'||p_assignment_action_id||
11675: ' p_def_bal_id:'||p_defined_balance_id||
11676: ' l_rb_status:'||l_rb_status);
11677: hr_utility.set_location('leaving pay_balance_pkg.run_balance_row_status ', 90);
11678: end if;
11679: return l_rb_status;
11680:
11681: end run_balance_row_status;
11761: l_rrv_count number;
11762: l_start_time number;
11763: begin
11764: if g_debug then
11765: hr_utility.set_location('entering pay_balance_pkg.run_balance_row_status', 10);
11766: end if;
11767: if ((p_defined_balance_id <> g_defined_balance_id) or (g_defined_balance_id is null)) then
11768: select balance_type_id into g_balance_type_id
11769: from pay_defined_balances
11800: if (((p_chunk_number <> g_chunk_number) or (g_chunk_number is null)) or
11801: ((p_defined_balance_id <> g_defined_balance_id) or (g_defined_balance_id is null))) then --{ build gtab
11802: if g_debug then
11803: l_start_time := dbms_utility.get_time;
11804: hr_utility.set_location('pay_balance_pkg.run_balance_row_status. init gtab chunk:'||p_chunk_number, 22);
11805: end if;
11806: g_chunk_number := p_chunk_number;
11807: g_defined_balance_id := p_defined_balance_id;
11808: g_asg_actions_tab.delete;
11809:
11810: if g_feeds_tab.count > 0 then --{ feed exists
11811: if g_feeds_tab.count < 5 then --{ primary balance
11812: if g_debug then
11813: hr_utility.set_location('pay_balance_pkg.run_balance_row_status. primbal feed.cnt:'||g_feeds_tab.count, 24);
11814: end if;
11815: l_rrv_count := 0;
11816: for a in g_feeds_tab.FIRST .. g_feeds_tab.LAST loop --{ g_feeds
11817: for rrv in rr_run_values(g_feeds_tab(a).input_value_id, g_feeds_tab(a).element_type_id,
11823: g_asg_actions_tab(l_rrv_count).assignment_action_id := rrv.assignment_action_id;
11824: g_asg_actions_tab(l_rrv_count).feed_count := rrv.feed_count;
11825: g_asg_actions_tab(l_rrv_count).asg_run := rrv.value;
11826: --if g_debug then
11827: -- hr_utility.trace('rr_run_values.'||l_rrv_count||' iv:'|| g_feeds_tab(a).input_value_id||' rrv.value:'||rrv.value||
11828: -- ' fed:'||rrv.feed_count||' rrv.assact:'||rrv.assignment_action_id);
11829: --end if;
11830: end loop; --} rr_run_values
11831: end loop; --} g_feeds
11831: end loop; --} g_feeds
11832:
11833: else -- more than 4 feeds
11834: if g_debug then
11835: hr_utility.set_location('pay_balance_pkg.run_balance_row_status. classbal feed.cnt:'||g_feeds_tab.count, 26);
11836: end if;
11837: select /*+ ORDERED
11838: INDEX(gen_assact PAY_ASSIGNMENT_ACTIONS_N50)
11839: INDEX(assact PAY_ASSIGNMENT_ACTIONS_N51)
11870: group by assact.assignment_action_id order by assact.assignment_action_id;
11871: end if; --} primary balance
11872: end if; --} feeds exist
11873: if g_debug then
11874: hr_utility.trace('gtab build rows:'||to_char(g_asg_actions_tab.count)||' time(hs):'||to_char(dbms_utility.get_time - l_start_time));
11875: end if;
11876: end if; --} build gtab
11877:
11878: open csr_run_bal( p_defined_balance_id, p_assignment_action_id);
11878: open csr_run_bal( p_defined_balance_id, p_assignment_action_id);
11879: fetch csr_run_bal into rb_row;
11880: if csr_run_bal%notfound then l_rb_status := 'N';
11881: if g_debug then
11882: hr_utility.set_location('pay_balance_pkg.run_balance_row_status. no rb', 30);
11883: end if;
11884: -- if there is no rb row check if its a candidate rb
11885: if g_debug then
11886: hr_utility.set_location('pay_balance_pkg.run_balance_row_status. search gtab ', 32);
11882: hr_utility.set_location('pay_balance_pkg.run_balance_row_status. no rb', 30);
11883: end if;
11884: -- if there is no rb row check if its a candidate rb
11885: if g_debug then
11886: hr_utility.set_location('pay_balance_pkg.run_balance_row_status. search gtab ', 32);
11887: end if;
11888: If g_asg_actions_tab.count > 0 then
11889: for actrec in g_asg_actions_tab.first..g_asg_actions_tab.last loop
11890: if p_assignment_action_id = g_asg_actions_tab(actrec).assignment_action_id then
11899: if (( l_rb_status = 'Z' ) and ( p_from_date is not null ) and
11900: ( rb_row.effective_date < p_from_date)) then
11901: l_rb_status := 'O';
11902: if g_debug then
11903: hr_utility.set_location('pay_balance_pkg.run_balance_row_status. old rb', 40);
11904: end if;
11905: end if;
11906:
11907: if ( l_rb_status = 'Z' ) then
11907: if ( l_rb_status = 'Z' ) then
11908: fetch csr_run_bal into rb_row2;
11909: if csr_run_bal%found then l_rb_status := 'U';
11910: if g_debug then
11911: hr_utility.set_location('pay_balance_pkg.run_balance_row_status. multiple rbs', 50);
11912: end if;
11913: end if;
11914: end if;
11915: -- check balance value is correct against asg_run if its cached and no context other that tax unit
11926: and rb_row.LOCAL_UNIT_ID is null
11927: and rb_row.ORGANIZATION_ID is null
11928: then
11929: if g_debug then
11930: hr_utility.set_location('pay_balance_pkg.run_balance_row_status. fetch gtab.asg_run value ', 60);
11931: end if;
11932: -- do we have balance in gtab
11933: l_value := 0;
11934: if ((g_asg_actions_tab.count > 0) and (p_assignment_id = nvl(g_assignment_id,-1)) and
11946: end if;
11947: -- check balance value is correct using rr_route
11948: if l_rb_status = 'Z' then
11949: if g_debug then
11950: hr_utility.set_location('pay_balance_pkg.run_balance_row_status. fetch rr value ', 70);
11951: end if;
11952: l_value := pay_balance_pkg.get_value(p_defined_balance_id,
11953: p_assignment_action_id,
11954: rb_row.tax_unit_id,
11974: end if;
11975: end if;
11976: close csr_run_bal;
11977: if g_debug then
11978: hr_utility.trace(' p_asg_action_id:'||p_assignment_action_id||
11979: ' p_def_bal_id:'||p_defined_balance_id||
11980: ' l_rb_status:'||l_rb_status);
11981: hr_utility.set_location('leaving pay_balance_pkg.run_balance_row_status ', 90);
11982: end if;
11977: if g_debug then
11978: hr_utility.trace(' p_asg_action_id:'||p_assignment_action_id||
11979: ' p_def_bal_id:'||p_defined_balance_id||
11980: ' l_rb_status:'||l_rb_status);
11981: hr_utility.set_location('leaving pay_balance_pkg.run_balance_row_status ', 90);
11982: end if;
11983: return l_rb_status;
11984:
11985: end run_balance_row_status;
11998:
11999: begin
12000:
12001: if g_debug then
12002: hr_utility.trace('enter linked_defined_balance. p_defined_balance_id:'||p_defined_balance_id||
12003: ' p_business_group_id:'||p_business_group_id||
12004: ' p_balance_load_date:'||p_balance_load_date );
12005: end if;
12006:
12029: and bv.RUN_BALANCE_STATUS = 'V'
12030: and nvl(bv.BALANCE_LOAD_DATE,hr_api.g_eot) <= p_balance_load_date;
12031:
12032: if g_debug then
12033: hr_utility.trace('leaving linked_defined_balance. g_ldb_linked_def_bal:'||l_linked_def_bal);
12034: end if;
12035: return g_ldb_linked_def_bal;
12036:
12037: end linked_defined_balance;