[Home] [Help]
45: --
46: --g_pattern_id_for_bg hr_patterns.pattern_id%TYPE;
47: --g_input_value_ids_ele1 t_input_value_ids; -- IVs of ssp_element
48: --g_input_value_ids_ele2 t_input_value_ids; -- IVs of ssp_correction_element
49: --g_person_id per_absence_attendances.person_id%TYPE;
50: --g_t_pat_exceptions t_pat_exceptions;
51: --g_t_per_pat_exceptions t_pat_exceptions;
52: --g_per_pattern c_per_pattern%ROWTYPE;
53:
213: -- 'GB_SSP NON PAYMENT'
214: --g_pattern_id_for_bg := NULL;--hr_patterns.pattern_id%TYPE
215: --g_input_value_ids_ele1.DELETE; --t_input_value_ids; -- IVs of ssp_element
216: --g_input_value_ids_ele2.DELETE; --t_input_value_ids; -- IVs of ssp_correction_element
217: --g_person_id := NULL;--per_absence_attendances.person_id%TYPE
218: --g_t_pat_exceptions.DELETE; --t_pat_exceptions
219: --g_t_per_pat_exceptions.DELETE; --t_pat_exceptions
220: --g_per_pattern := l_null_per_pattern;
221: --c_per_pattern%ROWTYPE
249: g_absence_category :=NULL;
250: END clear_cache;
251: --
252: -- pqp_get_absence_attendances is the function to get the value
253: -- of the column from table per_absence_attendances
254: --
255: FUNCTION pqp_get_absence_attendances(
256: p_absence_attendance_id IN NUMBER
257: ,p_col_name IN VARCHAR2
276: l_val :=
277: pqp_utilities.get_col_value(
278: p_col_nam => p_col_name
279: ,p_key_val => p_absence_attendance_id
280: ,p_table => 'PER_ABSENCE_ATTENDANCES'
281: ,p_key_col => 'ABSENCE_ATTENDANCE_ID'
282: ,p_where => NULL
283: ,p_error_code => p_error_code
284: ,p_message => p_message
341: l_val :=
342: pqp_utilities.pqp_get_concat_value(
343: p_key_col => 'ABSENCE_ATTENDANCE_ID'
344: ,p_key_val => p_absence_attendance_id
345: ,p_tab_name => 'PER_ABSENCE_ATTENDANCES'
346: ,p_view_name => 'PER_ABSENCE_ATTENDANCES_DFV'
347: ,p_message => p_message
348: );
349: --
342: pqp_utilities.pqp_get_concat_value(
343: p_key_col => 'ABSENCE_ATTENDANCE_ID'
344: ,p_key_val => p_absence_attendance_id
345: ,p_tab_name => 'PER_ABSENCE_ATTENDANCES'
346: ,p_view_name => 'PER_ABSENCE_ATTENDANCES_DFV'
347: ,p_message => p_message
348: );
349: --
350: ELSE
443: l_val :=
444: pqp_utilities.pqp_get_concat_value(
445: p_key_col => 'ABSENCE_ATTENDANCE_ID'
446: ,p_key_val => p_absence_attendance_id
447: ,p_tab_name => 'PER_ABSENCE_ATTENDANCES'
448: ,p_view_name => 'PER_ABSENCE_ATTENDANCES_DFV'
449: ,p_message => p_message
450: );
451: --
444: pqp_utilities.pqp_get_concat_value(
445: p_key_col => 'ABSENCE_ATTENDANCE_ID'
446: ,p_key_val => p_absence_attendance_id
447: ,p_tab_name => 'PER_ABSENCE_ATTENDANCES'
448: ,p_view_name => 'PER_ABSENCE_ATTENDANCES_DFV'
449: ,p_message => p_message
450: );
451: --
452: ELSE
453: --
454: debug(l_proc_name, 10);
455: l_val :=
456: pqp_utilities.get_df_value(
457: p_flex_name => 'PER_ABSENCE_ATTENDANCES'
458: ,p_flex_context => NULL
459: ,p_flex_field_title => p_segment_name
460: ,p_key_col => 'ABSENCE_ATTENDANCE_ID'
461: ,p_key_val => p_absence_attendance_id
458: ,p_flex_context => NULL
459: ,p_flex_field_title => p_segment_name
460: ,p_key_col => 'ABSENCE_ATTENDANCE_ID'
461: ,p_key_val => p_absence_attendance_id
462: ,p_tab_name => 'PER_ABSENCE_ATTENDANCES'
463: ,p_effective_date => p_effective_date
464: ,p_eff_date_req => 'N'
465: ,p_business_group_id => p_business_group_id
466: ,p_bus_group_id_req => 'N'
1159: ,p_effective_date IN DATE
1160: )
1161: RETURN NUMBER
1162: IS
1163: l_absence_id per_absence_attendances.absence_attendance_id%TYPE;
1164: l_proc_step NUMBER(38,10):=0;
1165: l_proc_name VARCHAR2(61):=
1166: g_package_name||'ben_get_absence_id';
1167: BEGIN
1198: END IF;
1199: END ben_get_absence_id;
1200:
1201: -- ben_get_per_abs_attendances is the main function to get the value
1202: -- of the column from table per_absence_attendances.Called from
1203: -- BEN where assignment_id and effective Date are contexts.
1204: FUNCTION ben_get_per_abs_attendances(
1205: p_assignment_id IN NUMBER
1206: ,p_effective_date IN DATE
1212: IS
1213: l_proc_step NUMBER(38,10):=0;
1214: l_proc_name VARCHAR2(61):=
1215: g_package_name||'ben_get_per_abs_attendances';
1216: l_absence_id per_absence_attendances.absence_attendance_id%TYPE;
1217: l_val VARCHAR2(2000);
1218: BEGIN
1219: IF g_debug THEN
1220: debug_enter(l_proc_name);
1275: IS
1276: l_proc_step NUMBER(38,10):=0;
1277: l_proc_name VARCHAR2(61):=
1278: g_package_name||'ben_get_absence_further_info';
1279: l_absence_id per_absence_attendances.absence_attendance_id%TYPE;
1280: l_val VARCHAR2(2000);
1281: BEGIN
1282: IF g_debug THEN
1283: debug_enter(l_proc_name);
1341: IS
1342: l_proc_step NUMBER(38,10):=0;
1343: l_proc_name VARCHAR2(61):=
1344: g_package_name||'ben_get_absence_addnl_attr';
1345: l_absence_id per_absence_attendances.absence_attendance_id%TYPE;
1346: l_val VARCHAR2(2000);
1347: BEGIN
1348: IF g_debug THEN
1349: debug_enter(l_proc_name);
1406: IS
1407: l_proc_step NUMBER(38,10):=0;
1408: l_proc_name VARCHAR2(61):=
1409: g_package_name||'ben_get_ssp_medicals_details';
1410: l_absence_id per_absence_attendances.absence_attendance_id%TYPE;
1411: l_val VARCHAR2(2000);
1412: BEGIN
1413: IF g_debug THEN
1414: debug_enter(l_proc_name);
1467: IS
1468: l_proc_step NUMBER(38,10):=0;
1469: l_proc_name VARCHAR2(61):=
1470: g_package_name||'ben_get_ssp_medical_addnl_attr';
1471: l_absence_id per_absence_attendances.absence_attendance_id%TYPE;
1472: l_val VARCHAR2(2000);
1473: BEGIN
1474: IF g_debug THEN
1475: debug_enter(l_proc_name);
1530: IS
1531: l_proc_step NUMBER(38,10):=0;
1532: l_proc_name VARCHAR2(61):=
1533: g_package_name||'ben_get_ssp_matrnty_details';
1534: l_absence_id per_absence_attendances.absence_attendance_id%TYPE;
1535: l_val VARCHAR2(2000);
1536: BEGIN
1537: IF g_debug THEN
1538: debug_enter(l_proc_name);
1589: IS
1590: l_proc_step NUMBER(38,10):=0;
1591: l_proc_name VARCHAR2(61):=
1592: g_package_name||'ben_get_ssp_matrnty_addnl_attr';
1593: l_absence_id per_absence_attendances.absence_attendance_id%TYPE;
1594: l_val VARCHAR2(2000);
1595: BEGIN
1596: IF g_debug THEN
1597: debug_enter(l_proc_name);
1696: END IF;
1697: END get_lookup_code;
1698:
1699: -- get_absence_details Returns the value of the column in table
1700: -- per_absence_attendances. Inputs are absence_attendance_id and the title
1701: -- of the column in the absence form.The mapping of form title to the DB
1702: -- column is done in Lookup and the same is fetched by calling
1703: -- get_lookup_code function. For the Fields which are displayed from
1704: -- Other Tables are derived individually and those columns are ABSENCE_TYPE,
1860: END IF;
1861: END get_absence_details;
1862:
1863: -- ben_get_absence_details is the main function to get the value
1864: -- of the column from table per_absence_attendances.Called from
1865: -- BEN where assignment_id and effective Date are contexts.
1866: FUNCTION ben_get_absence_details(
1867: p_assignment_id IN NUMBER
1868: ,p_effective_date IN DATE
1874: IS
1875: l_proc_step NUMBER(38,10):=0;
1876: l_proc_name VARCHAR2(61):=
1877: g_package_name||'ben_get_absence_details';
1878: l_absence_id per_absence_attendances.absence_attendance_id%TYPE;
1879: l_val VARCHAR2(2000);
1880: BEGIN
1881: IF g_debug THEN
1882: debug_enter(l_proc_name);
4883: IS
4884: l_proc_step NUMBER(38,10):=0;
4885: l_proc_name VARCHAR2(61):=
4886: g_package_name||'ben_matrnty_details';
4887: l_absence_id per_absence_attendances.absence_attendance_id%TYPE;
4888: l_val VARCHAR2(2000);
4889: BEGIN
4890: IF g_debug THEN
4891: debug_enter(l_proc_name);
4953: IS
4954: l_proc_step NUMBER(38,10):=0;
4955: l_proc_name VARCHAR2(61):=
4956: g_package_name||'ben_medical_details';
4957: l_absence_id per_absence_attendances.absence_attendance_id%TYPE;
4958: l_val VARCHAR2(2000);
4959: BEGIN
4960: IF g_debug THEN
4961: debug_enter(l_proc_name);
8744:
8745: l_max_ssp_period NUMBER;
8746: l_primary_assignment_id per_all_assignments_f.assignment_id%TYPE;
8747: l_absence csr_absence_details%ROWTYPE;
8748: l_piw_id per_absence_attendances.absence_attendance_id%TYPE;
8749: i BINARY_INTEGER:= 0;
8750: j BINARY_INTEGER:= 0;
8751: l_screen_entry_value pay_element_entry_values.screen_entry_value%TYPE;
8752: l_range_start_date DATE;
10933: IS
10934: SELECT atd.abs_information4 start_date_txt -- minimum pay start day
10935: ,atd.abs_information5 end_date_txt -- minimum pay end day
10936: ,atd.abs_information6 min_pay -- minimum pay value
10937: FROM per_absence_attendances atd, per_all_assignments_f asg
10938: WHERE atd.abs_information_category = 'GB_PQP_OSP_OMP_PART_DAYS'
10939: AND atd.absence_attendance_id = p_absence_id
10940: AND atd.person_id = asg.person_id
10941: AND asg.assignment_id = p_assignment_id
11243:
11244:
11245: CURSOR csr_absence_person IS
11246: SELECT abs.person_id
11247: FROM per_absence_attendances abs
11248: WHERE abs.absence_attendance_id = p_absence_attendance_id
11249: AND abs.business_group_id = p_business_group_id ;
11250:
11251: CURSOR csr_assignment_person IS
11253: FROM per_all_assignments_f asg
11254: WHERE asg.assignment_id = p_assignment_id
11255: AND rownum < 2 ;
11256:
11257: l_abs_person_id per_absence_attendances.person_id%TYPE ;
11258: l_asg_person_id per_all_assignments_f.person_id%TYPE ;
11259:
11260: BEGIN
11261:
11346: l_absence_amount NUMBER ;
11347:
11348: CURSOR csr_absence_details IS
11349: select abs.person_id, type.absence_category
11350: from per_absence_attendances abs
11351: ,per_absence_attendance_types type
11352: where abs.absence_attendance_type_id = type.absence_attendance_type_id
11353: and abs.absence_attendance_id = p_absence_attendance_id
11354: and abs.business_group_id = p_business_group_id ;
12146: g_package_name||'get_ssp_smp_paid_days';
12147:
12148:
12149: Cursor cur_no_paid_days(p_date date) IS
12150: select 1 from SSP_SMP_ENTRIES_V a1 , per_absence_attendances b1
12151: where a1.person_id = b1.person_id and a1.person_id = l_person_id
12152: and c_date between date_from and least(nvl(date_end, date_to), date_to)
12153: union
12154: (select 1 from SSP_SSP_ENTRIES_V where person_id = l_person_id
12153: union
12154: (select 1 from SSP_SSP_ENTRIES_V where person_id = l_person_id
12155: and c_date between date_from and date_to
12156: MINUS
12157: SELECT 1 FROM ssp_stoppages_v x, per_absence_attendances y
12158: WHERE X.absence_attendance_id = y.absence_attendance_id AND y.person_id = l_person_id
12159: AND c_date BETWEEN withhold_from AND withhold_to
12160: -- added for Bug 7304886
12161: -- The Work-Off days falling in between Waiting days should NOT be
12164: select 1 from
12165: (SELECT min(withhold_from) min_withhold_from
12166: , max(withhold_to) max_withhold_to
12167: , X.absence_attendance_id
12168: FROM ssp_stoppages_v x, per_absence_attendances y
12169: WHERE X.absence_attendance_id = y.absence_attendance_id
12170: AND y.person_id = l_person_id
12171: AND x.reason = 'Waiting day'
12172: GROUP BY X.absence_attendance_id
12174: where c_date between min_withhold_from and max_withhold_to
12175: )
12176: -- addition for Bug 7304886 ends
12177: union
12178: select 1 from SSP_SAP_ENTRIES_V a2 , per_absence_attendances b2
12179: where a2.person_id = b2.person_id and a2.person_id = l_person_id
12180: and c_date between date_from and least(nvl(date_end, date_to), date_to )
12181: union
12182: select 1 from SSP_SPPA_ENTRIES_V a3 , per_absence_attendances b3
12178: select 1 from SSP_SAP_ENTRIES_V a2 , per_absence_attendances b2
12179: where a2.person_id = b2.person_id and a2.person_id = l_person_id
12180: and c_date between date_from and least(nvl(date_end, date_to), date_to )
12181: union
12182: select 1 from SSP_SPPA_ENTRIES_V a3 , per_absence_attendances b3
12183: where a3.person_id = b3.person_id and a3.person_id = l_person_id
12184: and c_date between date_from and least(nvl(date_end, date_to), date_to );
12185:
12186: CURSOR cur_person_id is