eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.PQH_FR_EMP_STAT_SIT_UTILITY dependencies on PQH_FR_EMP_STAT_SITUATIONS

Line 73: FROM pqh_fr_emp_stat_situations

69: p_person_id NUMBER) IS
70: SELECT emp_stat_situation_id, statutory_situation_id, object_version_number,
71: actual_start_date, provisional_start_date,
72: actual_end_date, NVL(provisional_end_date,HR_GENERAL.end_of_time)
73: FROM pqh_fr_emp_stat_situations
74: WHERE person_id = p_person_id
75: AND statutory_situation_id IN (SELECT statutory_situation_id
76: FROM pqh_fr_stat_situations_v sit
77: ,per_shared_types_vl sh

Line 101: FROM pqh_fr_emp_stat_situations

97: p_iand_stat_sit_id NUMBER) IS
98: SELECT 'x'
99: FROM DUAL
100: WHERE EXISTS(SELECT 'x'
101: FROM pqh_fr_emp_stat_situations
102: WHERE person_id = p_person_id
103: -- AND emp_stat_situation_id <> NVL(p_emp_stat_situation_id,-1)
104: AND statutory_situation_id <> p_iand_stat_sit_id
105: AND(NVL(p_actual_start,p_provisional_start)

Line 113: l_iand_emp_stat_sit_id PQH_FR_EMP_STAT_SITUATIONS.emp_stat_situation_id%TYPE;

109: BETWEEN NVL(actual_start_date,provisional_start_date)
110: AND NVL(actual_end_date,NVL(provisional_end_date,HR_GENERAL.end_of_time))));
111: --
112: --Variable Declaration
113: l_iand_emp_stat_sit_id PQH_FR_EMP_STAT_SITUATIONS.emp_stat_situation_id%TYPE;
114: l_iand_stat_sit_id PQH_FR_EMP_STAT_SITUATIONS.statutory_situation_id%TYPE;
115: l_iand_ovn PQH_FR_EMP_STAT_SITUATIONS.object_version_number%TYPE;
116: l_iand_act_start_dt PQH_FR_EMP_STAT_SITUATIONS.actual_start_date%TYPE;
117: l_iand_prv_start_dt PQH_FR_EMP_STAT_SITUATIONS.provisional_start_date%TYPE;

Line 114: l_iand_stat_sit_id PQH_FR_EMP_STAT_SITUATIONS.statutory_situation_id%TYPE;

110: AND NVL(actual_end_date,NVL(provisional_end_date,HR_GENERAL.end_of_time))));
111: --
112: --Variable Declaration
113: l_iand_emp_stat_sit_id PQH_FR_EMP_STAT_SITUATIONS.emp_stat_situation_id%TYPE;
114: l_iand_stat_sit_id PQH_FR_EMP_STAT_SITUATIONS.statutory_situation_id%TYPE;
115: l_iand_ovn PQH_FR_EMP_STAT_SITUATIONS.object_version_number%TYPE;
116: l_iand_act_start_dt PQH_FR_EMP_STAT_SITUATIONS.actual_start_date%TYPE;
117: l_iand_prv_start_dt PQH_FR_EMP_STAT_SITUATIONS.provisional_start_date%TYPE;
118: l_iand_act_end_dt PQH_FR_EMP_STAT_SITUATIONS.actual_end_date%TYPE;

Line 115: l_iand_ovn PQH_FR_EMP_STAT_SITUATIONS.object_version_number%TYPE;

111: --
112: --Variable Declaration
113: l_iand_emp_stat_sit_id PQH_FR_EMP_STAT_SITUATIONS.emp_stat_situation_id%TYPE;
114: l_iand_stat_sit_id PQH_FR_EMP_STAT_SITUATIONS.statutory_situation_id%TYPE;
115: l_iand_ovn PQH_FR_EMP_STAT_SITUATIONS.object_version_number%TYPE;
116: l_iand_act_start_dt PQH_FR_EMP_STAT_SITUATIONS.actual_start_date%TYPE;
117: l_iand_prv_start_dt PQH_FR_EMP_STAT_SITUATIONS.provisional_start_date%TYPE;
118: l_iand_act_end_dt PQH_FR_EMP_STAT_SITUATIONS.actual_end_date%TYPE;
119: l_iand_prv_end_dt PQH_FR_EMP_STAT_SITUATIONS.provisional_end_date%TYPE;

Line 116: l_iand_act_start_dt PQH_FR_EMP_STAT_SITUATIONS.actual_start_date%TYPE;

112: --Variable Declaration
113: l_iand_emp_stat_sit_id PQH_FR_EMP_STAT_SITUATIONS.emp_stat_situation_id%TYPE;
114: l_iand_stat_sit_id PQH_FR_EMP_STAT_SITUATIONS.statutory_situation_id%TYPE;
115: l_iand_ovn PQH_FR_EMP_STAT_SITUATIONS.object_version_number%TYPE;
116: l_iand_act_start_dt PQH_FR_EMP_STAT_SITUATIONS.actual_start_date%TYPE;
117: l_iand_prv_start_dt PQH_FR_EMP_STAT_SITUATIONS.provisional_start_date%TYPE;
118: l_iand_act_end_dt PQH_FR_EMP_STAT_SITUATIONS.actual_end_date%TYPE;
119: l_iand_prv_end_dt PQH_FR_EMP_STAT_SITUATIONS.provisional_end_date%TYPE;
120: l_new_iand_emp_stat_sit_id PQH_FR_EMP_STAT_SITUATIONS.emp_stat_situation_id%TYPE;

Line 117: l_iand_prv_start_dt PQH_FR_EMP_STAT_SITUATIONS.provisional_start_date%TYPE;

113: l_iand_emp_stat_sit_id PQH_FR_EMP_STAT_SITUATIONS.emp_stat_situation_id%TYPE;
114: l_iand_stat_sit_id PQH_FR_EMP_STAT_SITUATIONS.statutory_situation_id%TYPE;
115: l_iand_ovn PQH_FR_EMP_STAT_SITUATIONS.object_version_number%TYPE;
116: l_iand_act_start_dt PQH_FR_EMP_STAT_SITUATIONS.actual_start_date%TYPE;
117: l_iand_prv_start_dt PQH_FR_EMP_STAT_SITUATIONS.provisional_start_date%TYPE;
118: l_iand_act_end_dt PQH_FR_EMP_STAT_SITUATIONS.actual_end_date%TYPE;
119: l_iand_prv_end_dt PQH_FR_EMP_STAT_SITUATIONS.provisional_end_date%TYPE;
120: l_new_iand_emp_stat_sit_id PQH_FR_EMP_STAT_SITUATIONS.emp_stat_situation_id%TYPE;
121: l_new_sit_ovn PQH_FR_EMP_STAT_SITUATIONS.object_version_number%TYPE;

Line 118: l_iand_act_end_dt PQH_FR_EMP_STAT_SITUATIONS.actual_end_date%TYPE;

114: l_iand_stat_sit_id PQH_FR_EMP_STAT_SITUATIONS.statutory_situation_id%TYPE;
115: l_iand_ovn PQH_FR_EMP_STAT_SITUATIONS.object_version_number%TYPE;
116: l_iand_act_start_dt PQH_FR_EMP_STAT_SITUATIONS.actual_start_date%TYPE;
117: l_iand_prv_start_dt PQH_FR_EMP_STAT_SITUATIONS.provisional_start_date%TYPE;
118: l_iand_act_end_dt PQH_FR_EMP_STAT_SITUATIONS.actual_end_date%TYPE;
119: l_iand_prv_end_dt PQH_FR_EMP_STAT_SITUATIONS.provisional_end_date%TYPE;
120: l_new_iand_emp_stat_sit_id PQH_FR_EMP_STAT_SITUATIONS.emp_stat_situation_id%TYPE;
121: l_new_sit_ovn PQH_FR_EMP_STAT_SITUATIONS.object_version_number%TYPE;
122: l_max_iand_emp_stat_sit_id PQH_FR_EMP_STAT_SITUATIONS.emp_stat_situation_id%TYPE;

Line 119: l_iand_prv_end_dt PQH_FR_EMP_STAT_SITUATIONS.provisional_end_date%TYPE;

115: l_iand_ovn PQH_FR_EMP_STAT_SITUATIONS.object_version_number%TYPE;
116: l_iand_act_start_dt PQH_FR_EMP_STAT_SITUATIONS.actual_start_date%TYPE;
117: l_iand_prv_start_dt PQH_FR_EMP_STAT_SITUATIONS.provisional_start_date%TYPE;
118: l_iand_act_end_dt PQH_FR_EMP_STAT_SITUATIONS.actual_end_date%TYPE;
119: l_iand_prv_end_dt PQH_FR_EMP_STAT_SITUATIONS.provisional_end_date%TYPE;
120: l_new_iand_emp_stat_sit_id PQH_FR_EMP_STAT_SITUATIONS.emp_stat_situation_id%TYPE;
121: l_new_sit_ovn PQH_FR_EMP_STAT_SITUATIONS.object_version_number%TYPE;
122: l_max_iand_emp_stat_sit_id PQH_FR_EMP_STAT_SITUATIONS.emp_stat_situation_id%TYPE;
123: l_max_iand_stat_sit_id PQH_FR_EMP_STAT_SITUATIONS.statutory_situation_id%TYPE;

Line 120: l_new_iand_emp_stat_sit_id PQH_FR_EMP_STAT_SITUATIONS.emp_stat_situation_id%TYPE;

116: l_iand_act_start_dt PQH_FR_EMP_STAT_SITUATIONS.actual_start_date%TYPE;
117: l_iand_prv_start_dt PQH_FR_EMP_STAT_SITUATIONS.provisional_start_date%TYPE;
118: l_iand_act_end_dt PQH_FR_EMP_STAT_SITUATIONS.actual_end_date%TYPE;
119: l_iand_prv_end_dt PQH_FR_EMP_STAT_SITUATIONS.provisional_end_date%TYPE;
120: l_new_iand_emp_stat_sit_id PQH_FR_EMP_STAT_SITUATIONS.emp_stat_situation_id%TYPE;
121: l_new_sit_ovn PQH_FR_EMP_STAT_SITUATIONS.object_version_number%TYPE;
122: l_max_iand_emp_stat_sit_id PQH_FR_EMP_STAT_SITUATIONS.emp_stat_situation_id%TYPE;
123: l_max_iand_stat_sit_id PQH_FR_EMP_STAT_SITUATIONS.statutory_situation_id%TYPE;
124: l_max_iand_ovn PQH_FR_EMP_STAT_SITUATIONS.object_version_number%TYPE;

Line 121: l_new_sit_ovn PQH_FR_EMP_STAT_SITUATIONS.object_version_number%TYPE;

117: l_iand_prv_start_dt PQH_FR_EMP_STAT_SITUATIONS.provisional_start_date%TYPE;
118: l_iand_act_end_dt PQH_FR_EMP_STAT_SITUATIONS.actual_end_date%TYPE;
119: l_iand_prv_end_dt PQH_FR_EMP_STAT_SITUATIONS.provisional_end_date%TYPE;
120: l_new_iand_emp_stat_sit_id PQH_FR_EMP_STAT_SITUATIONS.emp_stat_situation_id%TYPE;
121: l_new_sit_ovn PQH_FR_EMP_STAT_SITUATIONS.object_version_number%TYPE;
122: l_max_iand_emp_stat_sit_id PQH_FR_EMP_STAT_SITUATIONS.emp_stat_situation_id%TYPE;
123: l_max_iand_stat_sit_id PQH_FR_EMP_STAT_SITUATIONS.statutory_situation_id%TYPE;
124: l_max_iand_ovn PQH_FR_EMP_STAT_SITUATIONS.object_version_number%TYPE;
125: l_max_iand_act_start_dt PQH_FR_EMP_STAT_SITUATIONS.actual_start_date%TYPE;

Line 122: l_max_iand_emp_stat_sit_id PQH_FR_EMP_STAT_SITUATIONS.emp_stat_situation_id%TYPE;

118: l_iand_act_end_dt PQH_FR_EMP_STAT_SITUATIONS.actual_end_date%TYPE;
119: l_iand_prv_end_dt PQH_FR_EMP_STAT_SITUATIONS.provisional_end_date%TYPE;
120: l_new_iand_emp_stat_sit_id PQH_FR_EMP_STAT_SITUATIONS.emp_stat_situation_id%TYPE;
121: l_new_sit_ovn PQH_FR_EMP_STAT_SITUATIONS.object_version_number%TYPE;
122: l_max_iand_emp_stat_sit_id PQH_FR_EMP_STAT_SITUATIONS.emp_stat_situation_id%TYPE;
123: l_max_iand_stat_sit_id PQH_FR_EMP_STAT_SITUATIONS.statutory_situation_id%TYPE;
124: l_max_iand_ovn PQH_FR_EMP_STAT_SITUATIONS.object_version_number%TYPE;
125: l_max_iand_act_start_dt PQH_FR_EMP_STAT_SITUATIONS.actual_start_date%TYPE;
126: l_actual_end_date PQH_FR_EMP_STAT_SITUATIONS.actual_end_date%TYPE;

Line 123: l_max_iand_stat_sit_id PQH_FR_EMP_STAT_SITUATIONS.statutory_situation_id%TYPE;

119: l_iand_prv_end_dt PQH_FR_EMP_STAT_SITUATIONS.provisional_end_date%TYPE;
120: l_new_iand_emp_stat_sit_id PQH_FR_EMP_STAT_SITUATIONS.emp_stat_situation_id%TYPE;
121: l_new_sit_ovn PQH_FR_EMP_STAT_SITUATIONS.object_version_number%TYPE;
122: l_max_iand_emp_stat_sit_id PQH_FR_EMP_STAT_SITUATIONS.emp_stat_situation_id%TYPE;
123: l_max_iand_stat_sit_id PQH_FR_EMP_STAT_SITUATIONS.statutory_situation_id%TYPE;
124: l_max_iand_ovn PQH_FR_EMP_STAT_SITUATIONS.object_version_number%TYPE;
125: l_max_iand_act_start_dt PQH_FR_EMP_STAT_SITUATIONS.actual_start_date%TYPE;
126: l_actual_end_date PQH_FR_EMP_STAT_SITUATIONS.actual_end_date%TYPE;
127: l_approval_flag PQH_FR_EMP_STAT_SITUATIONS.approval_flag%TYPE;

Line 124: l_max_iand_ovn PQH_FR_EMP_STAT_SITUATIONS.object_version_number%TYPE;

120: l_new_iand_emp_stat_sit_id PQH_FR_EMP_STAT_SITUATIONS.emp_stat_situation_id%TYPE;
121: l_new_sit_ovn PQH_FR_EMP_STAT_SITUATIONS.object_version_number%TYPE;
122: l_max_iand_emp_stat_sit_id PQH_FR_EMP_STAT_SITUATIONS.emp_stat_situation_id%TYPE;
123: l_max_iand_stat_sit_id PQH_FR_EMP_STAT_SITUATIONS.statutory_situation_id%TYPE;
124: l_max_iand_ovn PQH_FR_EMP_STAT_SITUATIONS.object_version_number%TYPE;
125: l_max_iand_act_start_dt PQH_FR_EMP_STAT_SITUATIONS.actual_start_date%TYPE;
126: l_actual_end_date PQH_FR_EMP_STAT_SITUATIONS.actual_end_date%TYPE;
127: l_approval_flag PQH_FR_EMP_STAT_SITUATIONS.approval_flag%TYPE;
128: l_valid VARCHAR2(01) := NULL;

Line 125: l_max_iand_act_start_dt PQH_FR_EMP_STAT_SITUATIONS.actual_start_date%TYPE;

121: l_new_sit_ovn PQH_FR_EMP_STAT_SITUATIONS.object_version_number%TYPE;
122: l_max_iand_emp_stat_sit_id PQH_FR_EMP_STAT_SITUATIONS.emp_stat_situation_id%TYPE;
123: l_max_iand_stat_sit_id PQH_FR_EMP_STAT_SITUATIONS.statutory_situation_id%TYPE;
124: l_max_iand_ovn PQH_FR_EMP_STAT_SITUATIONS.object_version_number%TYPE;
125: l_max_iand_act_start_dt PQH_FR_EMP_STAT_SITUATIONS.actual_start_date%TYPE;
126: l_actual_end_date PQH_FR_EMP_STAT_SITUATIONS.actual_end_date%TYPE;
127: l_approval_flag PQH_FR_EMP_STAT_SITUATIONS.approval_flag%TYPE;
128: l_valid VARCHAR2(01) := NULL;
129: l_proc VARCHAR2(72) := g_package||'create_emp_stat_situation';

Line 126: l_actual_end_date PQH_FR_EMP_STAT_SITUATIONS.actual_end_date%TYPE;

122: l_max_iand_emp_stat_sit_id PQH_FR_EMP_STAT_SITUATIONS.emp_stat_situation_id%TYPE;
123: l_max_iand_stat_sit_id PQH_FR_EMP_STAT_SITUATIONS.statutory_situation_id%TYPE;
124: l_max_iand_ovn PQH_FR_EMP_STAT_SITUATIONS.object_version_number%TYPE;
125: l_max_iand_act_start_dt PQH_FR_EMP_STAT_SITUATIONS.actual_start_date%TYPE;
126: l_actual_end_date PQH_FR_EMP_STAT_SITUATIONS.actual_end_date%TYPE;
127: l_approval_flag PQH_FR_EMP_STAT_SITUATIONS.approval_flag%TYPE;
128: l_valid VARCHAR2(01) := NULL;
129: l_proc VARCHAR2(72) := g_package||'create_emp_stat_situation';
130: l_validate BOOLEAN;

Line 127: l_approval_flag PQH_FR_EMP_STAT_SITUATIONS.approval_flag%TYPE;

123: l_max_iand_stat_sit_id PQH_FR_EMP_STAT_SITUATIONS.statutory_situation_id%TYPE;
124: l_max_iand_ovn PQH_FR_EMP_STAT_SITUATIONS.object_version_number%TYPE;
125: l_max_iand_act_start_dt PQH_FR_EMP_STAT_SITUATIONS.actual_start_date%TYPE;
126: l_actual_end_date PQH_FR_EMP_STAT_SITUATIONS.actual_end_date%TYPE;
127: l_approval_flag PQH_FR_EMP_STAT_SITUATIONS.approval_flag%TYPE;
128: l_valid VARCHAR2(01) := NULL;
129: l_proc VARCHAR2(72) := g_package||'create_emp_stat_situation';
130: l_validate BOOLEAN;
131: l_rul_sit_return_st varchar2(2) ;

Line 380: FROM pqh_fr_emp_stat_situations

376: --
377: --Cursor to get current Situation details
378: CURSOR csr_current_situation_dtls(p_emp_stat_situation_id IN NUMBER) IS
379: SELECT person_id, statutory_situation_id, actual_start_date, provisional_start_date, actual_end_date, provisional_end_date
380: FROM pqh_fr_emp_stat_situations
381: WHERE emp_stat_situation_id = p_emp_stat_situation_id;
382: --
383: --Cursor to fetch prior Situation falling before the Situation to be deleted
384: CURSOR csr_get_prior_sit_dtls(p_person_id IN NUMBER,

Line 388: FROM pqh_fr_emp_stat_situations

384: CURSOR csr_get_prior_sit_dtls(p_person_id IN NUMBER,
385: p_date IN DATE) IS
386: SELECT emp_stat_situation_id, statutory_situation_id, object_version_number,
387: provisional_start_date, actual_start_date, provisional_end_date, actual_end_date
388: FROM pqh_fr_emp_stat_situations
389: WHERE person_id = p_person_id
390: AND TRUNC(NVL(actual_end_date,provisional_end_date)) = TRUNC(p_date-1);
391: --
392: --Cursor to fetch future Situation falling after the Situation to be deleted

Line 397: FROM pqh_fr_emp_stat_situations

393: CURSOR csr_get_next_sit_dtls(p_person_id IN NUMBER,
394: p_date IN DATE) IS
395: SELECT emp_stat_situation_id, statutory_situation_id, object_version_number,
396: provisional_start_date, actual_start_date, provisional_end_date, actual_end_date
397: FROM pqh_fr_emp_stat_situations
398: WHERE person_id = p_person_id
399: AND TRUNC(NVL(actual_start_date,provisional_start_date)) = TRUNC(p_date+1);
400: --
401: --Cursor for Overlapping non In Activity Normal Default Situations

Line 410: FROM pqh_fr_emp_stat_situations

406: p_iand_stat_sit_id NUMBER) IS
407: SELECT 'x'
408: FROM DUAL
409: WHERE EXISTS(SELECT 'x'
410: FROM pqh_fr_emp_stat_situations
411: WHERE person_id = p_person_id
412: AND emp_stat_situation_id <> NVL(p_emp_stat_situation_id,-1)
413: AND statutory_situation_id <> p_iand_stat_sit_id
414: AND(p_provisional_start BETWEEN NVL(actual_start_date,provisional_start_date)

Line 423: l_person_id PQH_FR_EMP_STAT_SITUATIONS.person_id%TYPE;

419: --Variable Declaration
420: l_proc VARCHAR2(72) := g_package||'update_emp_stat_situation';
421: l_validate BOOLEAN;
422: l_valid VARCHAR2(01) := NULL;
423: l_person_id PQH_FR_EMP_STAT_SITUATIONS.person_id%TYPE;
424: l_sit_id PQH_FR_EMP_STAT_SITUATIONS.statutory_situation_id%TYPE;
425: l_act_st_dt PQH_FR_EMP_STAT_SITUATIONS.actual_start_date%TYPE;
426: l_prov_st_dt PQH_FR_EMP_STAT_SITUATIONS.provisional_start_date%TYPE;
427: l_act_end_dt PQH_FR_EMP_STAT_SITUATIONS.actual_end_date%TYPE;

Line 424: l_sit_id PQH_FR_EMP_STAT_SITUATIONS.statutory_situation_id%TYPE;

420: l_proc VARCHAR2(72) := g_package||'update_emp_stat_situation';
421: l_validate BOOLEAN;
422: l_valid VARCHAR2(01) := NULL;
423: l_person_id PQH_FR_EMP_STAT_SITUATIONS.person_id%TYPE;
424: l_sit_id PQH_FR_EMP_STAT_SITUATIONS.statutory_situation_id%TYPE;
425: l_act_st_dt PQH_FR_EMP_STAT_SITUATIONS.actual_start_date%TYPE;
426: l_prov_st_dt PQH_FR_EMP_STAT_SITUATIONS.provisional_start_date%TYPE;
427: l_act_end_dt PQH_FR_EMP_STAT_SITUATIONS.actual_end_date%TYPE;
428: l_prov_end_dt PQH_FR_EMP_STAT_SITUATIONS.provisional_end_date%TYPE;

Line 425: l_act_st_dt PQH_FR_EMP_STAT_SITUATIONS.actual_start_date%TYPE;

421: l_validate BOOLEAN;
422: l_valid VARCHAR2(01) := NULL;
423: l_person_id PQH_FR_EMP_STAT_SITUATIONS.person_id%TYPE;
424: l_sit_id PQH_FR_EMP_STAT_SITUATIONS.statutory_situation_id%TYPE;
425: l_act_st_dt PQH_FR_EMP_STAT_SITUATIONS.actual_start_date%TYPE;
426: l_prov_st_dt PQH_FR_EMP_STAT_SITUATIONS.provisional_start_date%TYPE;
427: l_act_end_dt PQH_FR_EMP_STAT_SITUATIONS.actual_end_date%TYPE;
428: l_prov_end_dt PQH_FR_EMP_STAT_SITUATIONS.provisional_end_date%TYPE;
429: l_prior_emp_stat_sit_id PQH_FR_EMP_STAT_SITUATIONS.emp_stat_situation_id%TYPE;

Line 426: l_prov_st_dt PQH_FR_EMP_STAT_SITUATIONS.provisional_start_date%TYPE;

422: l_valid VARCHAR2(01) := NULL;
423: l_person_id PQH_FR_EMP_STAT_SITUATIONS.person_id%TYPE;
424: l_sit_id PQH_FR_EMP_STAT_SITUATIONS.statutory_situation_id%TYPE;
425: l_act_st_dt PQH_FR_EMP_STAT_SITUATIONS.actual_start_date%TYPE;
426: l_prov_st_dt PQH_FR_EMP_STAT_SITUATIONS.provisional_start_date%TYPE;
427: l_act_end_dt PQH_FR_EMP_STAT_SITUATIONS.actual_end_date%TYPE;
428: l_prov_end_dt PQH_FR_EMP_STAT_SITUATIONS.provisional_end_date%TYPE;
429: l_prior_emp_stat_sit_id PQH_FR_EMP_STAT_SITUATIONS.emp_stat_situation_id%TYPE;
430: l_prior_sit_id PQH_FR_EMP_STAT_SITUATIONS.statutory_situation_id%TYPE;

Line 427: l_act_end_dt PQH_FR_EMP_STAT_SITUATIONS.actual_end_date%TYPE;

423: l_person_id PQH_FR_EMP_STAT_SITUATIONS.person_id%TYPE;
424: l_sit_id PQH_FR_EMP_STAT_SITUATIONS.statutory_situation_id%TYPE;
425: l_act_st_dt PQH_FR_EMP_STAT_SITUATIONS.actual_start_date%TYPE;
426: l_prov_st_dt PQH_FR_EMP_STAT_SITUATIONS.provisional_start_date%TYPE;
427: l_act_end_dt PQH_FR_EMP_STAT_SITUATIONS.actual_end_date%TYPE;
428: l_prov_end_dt PQH_FR_EMP_STAT_SITUATIONS.provisional_end_date%TYPE;
429: l_prior_emp_stat_sit_id PQH_FR_EMP_STAT_SITUATIONS.emp_stat_situation_id%TYPE;
430: l_prior_sit_id PQH_FR_EMP_STAT_SITUATIONS.statutory_situation_id%TYPE;
431: l_prior_ovn PQH_FR_EMP_STAT_SITUATIONS.object_version_number%TYPE;

Line 428: l_prov_end_dt PQH_FR_EMP_STAT_SITUATIONS.provisional_end_date%TYPE;

424: l_sit_id PQH_FR_EMP_STAT_SITUATIONS.statutory_situation_id%TYPE;
425: l_act_st_dt PQH_FR_EMP_STAT_SITUATIONS.actual_start_date%TYPE;
426: l_prov_st_dt PQH_FR_EMP_STAT_SITUATIONS.provisional_start_date%TYPE;
427: l_act_end_dt PQH_FR_EMP_STAT_SITUATIONS.actual_end_date%TYPE;
428: l_prov_end_dt PQH_FR_EMP_STAT_SITUATIONS.provisional_end_date%TYPE;
429: l_prior_emp_stat_sit_id PQH_FR_EMP_STAT_SITUATIONS.emp_stat_situation_id%TYPE;
430: l_prior_sit_id PQH_FR_EMP_STAT_SITUATIONS.statutory_situation_id%TYPE;
431: l_prior_ovn PQH_FR_EMP_STAT_SITUATIONS.object_version_number%TYPE;
432: l_prior_prov_st_date PQH_FR_EMP_STAT_SITUATIONS.provisional_start_date%TYPE;

Line 429: l_prior_emp_stat_sit_id PQH_FR_EMP_STAT_SITUATIONS.emp_stat_situation_id%TYPE;

425: l_act_st_dt PQH_FR_EMP_STAT_SITUATIONS.actual_start_date%TYPE;
426: l_prov_st_dt PQH_FR_EMP_STAT_SITUATIONS.provisional_start_date%TYPE;
427: l_act_end_dt PQH_FR_EMP_STAT_SITUATIONS.actual_end_date%TYPE;
428: l_prov_end_dt PQH_FR_EMP_STAT_SITUATIONS.provisional_end_date%TYPE;
429: l_prior_emp_stat_sit_id PQH_FR_EMP_STAT_SITUATIONS.emp_stat_situation_id%TYPE;
430: l_prior_sit_id PQH_FR_EMP_STAT_SITUATIONS.statutory_situation_id%TYPE;
431: l_prior_ovn PQH_FR_EMP_STAT_SITUATIONS.object_version_number%TYPE;
432: l_prior_prov_st_date PQH_FR_EMP_STAT_SITUATIONS.provisional_start_date%TYPE;
433: l_prior_act_st_date PQH_FR_EMP_STAT_SITUATIONS.actual_start_date%TYPE;

Line 430: l_prior_sit_id PQH_FR_EMP_STAT_SITUATIONS.statutory_situation_id%TYPE;

426: l_prov_st_dt PQH_FR_EMP_STAT_SITUATIONS.provisional_start_date%TYPE;
427: l_act_end_dt PQH_FR_EMP_STAT_SITUATIONS.actual_end_date%TYPE;
428: l_prov_end_dt PQH_FR_EMP_STAT_SITUATIONS.provisional_end_date%TYPE;
429: l_prior_emp_stat_sit_id PQH_FR_EMP_STAT_SITUATIONS.emp_stat_situation_id%TYPE;
430: l_prior_sit_id PQH_FR_EMP_STAT_SITUATIONS.statutory_situation_id%TYPE;
431: l_prior_ovn PQH_FR_EMP_STAT_SITUATIONS.object_version_number%TYPE;
432: l_prior_prov_st_date PQH_FR_EMP_STAT_SITUATIONS.provisional_start_date%TYPE;
433: l_prior_act_st_date PQH_FR_EMP_STAT_SITUATIONS.actual_start_date%TYPE;
434: l_prior_prov_end_date PQH_FR_EMP_STAT_SITUATIONS.provisional_start_date%TYPE;

Line 431: l_prior_ovn PQH_FR_EMP_STAT_SITUATIONS.object_version_number%TYPE;

427: l_act_end_dt PQH_FR_EMP_STAT_SITUATIONS.actual_end_date%TYPE;
428: l_prov_end_dt PQH_FR_EMP_STAT_SITUATIONS.provisional_end_date%TYPE;
429: l_prior_emp_stat_sit_id PQH_FR_EMP_STAT_SITUATIONS.emp_stat_situation_id%TYPE;
430: l_prior_sit_id PQH_FR_EMP_STAT_SITUATIONS.statutory_situation_id%TYPE;
431: l_prior_ovn PQH_FR_EMP_STAT_SITUATIONS.object_version_number%TYPE;
432: l_prior_prov_st_date PQH_FR_EMP_STAT_SITUATIONS.provisional_start_date%TYPE;
433: l_prior_act_st_date PQH_FR_EMP_STAT_SITUATIONS.actual_start_date%TYPE;
434: l_prior_prov_end_date PQH_FR_EMP_STAT_SITUATIONS.provisional_start_date%TYPE;
435: l_prior_act_end_date PQH_FR_EMP_STAT_SITUATIONS.actual_end_date%TYPE;

Line 432: l_prior_prov_st_date PQH_FR_EMP_STAT_SITUATIONS.provisional_start_date%TYPE;

428: l_prov_end_dt PQH_FR_EMP_STAT_SITUATIONS.provisional_end_date%TYPE;
429: l_prior_emp_stat_sit_id PQH_FR_EMP_STAT_SITUATIONS.emp_stat_situation_id%TYPE;
430: l_prior_sit_id PQH_FR_EMP_STAT_SITUATIONS.statutory_situation_id%TYPE;
431: l_prior_ovn PQH_FR_EMP_STAT_SITUATIONS.object_version_number%TYPE;
432: l_prior_prov_st_date PQH_FR_EMP_STAT_SITUATIONS.provisional_start_date%TYPE;
433: l_prior_act_st_date PQH_FR_EMP_STAT_SITUATIONS.actual_start_date%TYPE;
434: l_prior_prov_end_date PQH_FR_EMP_STAT_SITUATIONS.provisional_start_date%TYPE;
435: l_prior_act_end_date PQH_FR_EMP_STAT_SITUATIONS.actual_end_date%TYPE;
436: l_iand_sit_id PQH_FR_EMP_STAT_SITUATIONS.statutory_situation_id%TYPE;

Line 433: l_prior_act_st_date PQH_FR_EMP_STAT_SITUATIONS.actual_start_date%TYPE;

429: l_prior_emp_stat_sit_id PQH_FR_EMP_STAT_SITUATIONS.emp_stat_situation_id%TYPE;
430: l_prior_sit_id PQH_FR_EMP_STAT_SITUATIONS.statutory_situation_id%TYPE;
431: l_prior_ovn PQH_FR_EMP_STAT_SITUATIONS.object_version_number%TYPE;
432: l_prior_prov_st_date PQH_FR_EMP_STAT_SITUATIONS.provisional_start_date%TYPE;
433: l_prior_act_st_date PQH_FR_EMP_STAT_SITUATIONS.actual_start_date%TYPE;
434: l_prior_prov_end_date PQH_FR_EMP_STAT_SITUATIONS.provisional_start_date%TYPE;
435: l_prior_act_end_date PQH_FR_EMP_STAT_SITUATIONS.actual_end_date%TYPE;
436: l_iand_sit_id PQH_FR_EMP_STAT_SITUATIONS.statutory_situation_id%TYPE;
437: l_next_emp_stat_sit_id PQH_FR_EMP_STAT_SITUATIONS.emp_stat_situation_id%TYPE;

Line 434: l_prior_prov_end_date PQH_FR_EMP_STAT_SITUATIONS.provisional_start_date%TYPE;

430: l_prior_sit_id PQH_FR_EMP_STAT_SITUATIONS.statutory_situation_id%TYPE;
431: l_prior_ovn PQH_FR_EMP_STAT_SITUATIONS.object_version_number%TYPE;
432: l_prior_prov_st_date PQH_FR_EMP_STAT_SITUATIONS.provisional_start_date%TYPE;
433: l_prior_act_st_date PQH_FR_EMP_STAT_SITUATIONS.actual_start_date%TYPE;
434: l_prior_prov_end_date PQH_FR_EMP_STAT_SITUATIONS.provisional_start_date%TYPE;
435: l_prior_act_end_date PQH_FR_EMP_STAT_SITUATIONS.actual_end_date%TYPE;
436: l_iand_sit_id PQH_FR_EMP_STAT_SITUATIONS.statutory_situation_id%TYPE;
437: l_next_emp_stat_sit_id PQH_FR_EMP_STAT_SITUATIONS.emp_stat_situation_id%TYPE;
438: l_next_sit_id PQH_FR_EMP_STAT_SITUATIONS.statutory_situation_id%TYPE;

Line 435: l_prior_act_end_date PQH_FR_EMP_STAT_SITUATIONS.actual_end_date%TYPE;

431: l_prior_ovn PQH_FR_EMP_STAT_SITUATIONS.object_version_number%TYPE;
432: l_prior_prov_st_date PQH_FR_EMP_STAT_SITUATIONS.provisional_start_date%TYPE;
433: l_prior_act_st_date PQH_FR_EMP_STAT_SITUATIONS.actual_start_date%TYPE;
434: l_prior_prov_end_date PQH_FR_EMP_STAT_SITUATIONS.provisional_start_date%TYPE;
435: l_prior_act_end_date PQH_FR_EMP_STAT_SITUATIONS.actual_end_date%TYPE;
436: l_iand_sit_id PQH_FR_EMP_STAT_SITUATIONS.statutory_situation_id%TYPE;
437: l_next_emp_stat_sit_id PQH_FR_EMP_STAT_SITUATIONS.emp_stat_situation_id%TYPE;
438: l_next_sit_id PQH_FR_EMP_STAT_SITUATIONS.statutory_situation_id%TYPE;
439: l_next_ovn PQH_FR_EMP_STAT_SITUATIONS.object_version_number%TYPE;

Line 436: l_iand_sit_id PQH_FR_EMP_STAT_SITUATIONS.statutory_situation_id%TYPE;

432: l_prior_prov_st_date PQH_FR_EMP_STAT_SITUATIONS.provisional_start_date%TYPE;
433: l_prior_act_st_date PQH_FR_EMP_STAT_SITUATIONS.actual_start_date%TYPE;
434: l_prior_prov_end_date PQH_FR_EMP_STAT_SITUATIONS.provisional_start_date%TYPE;
435: l_prior_act_end_date PQH_FR_EMP_STAT_SITUATIONS.actual_end_date%TYPE;
436: l_iand_sit_id PQH_FR_EMP_STAT_SITUATIONS.statutory_situation_id%TYPE;
437: l_next_emp_stat_sit_id PQH_FR_EMP_STAT_SITUATIONS.emp_stat_situation_id%TYPE;
438: l_next_sit_id PQH_FR_EMP_STAT_SITUATIONS.statutory_situation_id%TYPE;
439: l_next_ovn PQH_FR_EMP_STAT_SITUATIONS.object_version_number%TYPE;
440: l_next_prov_st_date PQH_FR_EMP_STAT_SITUATIONS.provisional_start_date%TYPE;

Line 437: l_next_emp_stat_sit_id PQH_FR_EMP_STAT_SITUATIONS.emp_stat_situation_id%TYPE;

433: l_prior_act_st_date PQH_FR_EMP_STAT_SITUATIONS.actual_start_date%TYPE;
434: l_prior_prov_end_date PQH_FR_EMP_STAT_SITUATIONS.provisional_start_date%TYPE;
435: l_prior_act_end_date PQH_FR_EMP_STAT_SITUATIONS.actual_end_date%TYPE;
436: l_iand_sit_id PQH_FR_EMP_STAT_SITUATIONS.statutory_situation_id%TYPE;
437: l_next_emp_stat_sit_id PQH_FR_EMP_STAT_SITUATIONS.emp_stat_situation_id%TYPE;
438: l_next_sit_id PQH_FR_EMP_STAT_SITUATIONS.statutory_situation_id%TYPE;
439: l_next_ovn PQH_FR_EMP_STAT_SITUATIONS.object_version_number%TYPE;
440: l_next_prov_st_date PQH_FR_EMP_STAT_SITUATIONS.provisional_start_date%TYPE;
441: l_next_act_st_date PQH_FR_EMP_STAT_SITUATIONS.actual_start_date%TYPE;

Line 438: l_next_sit_id PQH_FR_EMP_STAT_SITUATIONS.statutory_situation_id%TYPE;

434: l_prior_prov_end_date PQH_FR_EMP_STAT_SITUATIONS.provisional_start_date%TYPE;
435: l_prior_act_end_date PQH_FR_EMP_STAT_SITUATIONS.actual_end_date%TYPE;
436: l_iand_sit_id PQH_FR_EMP_STAT_SITUATIONS.statutory_situation_id%TYPE;
437: l_next_emp_stat_sit_id PQH_FR_EMP_STAT_SITUATIONS.emp_stat_situation_id%TYPE;
438: l_next_sit_id PQH_FR_EMP_STAT_SITUATIONS.statutory_situation_id%TYPE;
439: l_next_ovn PQH_FR_EMP_STAT_SITUATIONS.object_version_number%TYPE;
440: l_next_prov_st_date PQH_FR_EMP_STAT_SITUATIONS.provisional_start_date%TYPE;
441: l_next_act_st_date PQH_FR_EMP_STAT_SITUATIONS.actual_start_date%TYPE;
442: l_next_prov_end_date PQH_FR_EMP_STAT_SITUATIONS.provisional_end_date%TYPE;

Line 439: l_next_ovn PQH_FR_EMP_STAT_SITUATIONS.object_version_number%TYPE;

435: l_prior_act_end_date PQH_FR_EMP_STAT_SITUATIONS.actual_end_date%TYPE;
436: l_iand_sit_id PQH_FR_EMP_STAT_SITUATIONS.statutory_situation_id%TYPE;
437: l_next_emp_stat_sit_id PQH_FR_EMP_STAT_SITUATIONS.emp_stat_situation_id%TYPE;
438: l_next_sit_id PQH_FR_EMP_STAT_SITUATIONS.statutory_situation_id%TYPE;
439: l_next_ovn PQH_FR_EMP_STAT_SITUATIONS.object_version_number%TYPE;
440: l_next_prov_st_date PQH_FR_EMP_STAT_SITUATIONS.provisional_start_date%TYPE;
441: l_next_act_st_date PQH_FR_EMP_STAT_SITUATIONS.actual_start_date%TYPE;
442: l_next_prov_end_date PQH_FR_EMP_STAT_SITUATIONS.provisional_end_date%TYPE;
443: l_next_act_end_date PQH_FR_EMP_STAT_SITUATIONS.actual_end_date%TYPE;

Line 440: l_next_prov_st_date PQH_FR_EMP_STAT_SITUATIONS.provisional_start_date%TYPE;

436: l_iand_sit_id PQH_FR_EMP_STAT_SITUATIONS.statutory_situation_id%TYPE;
437: l_next_emp_stat_sit_id PQH_FR_EMP_STAT_SITUATIONS.emp_stat_situation_id%TYPE;
438: l_next_sit_id PQH_FR_EMP_STAT_SITUATIONS.statutory_situation_id%TYPE;
439: l_next_ovn PQH_FR_EMP_STAT_SITUATIONS.object_version_number%TYPE;
440: l_next_prov_st_date PQH_FR_EMP_STAT_SITUATIONS.provisional_start_date%TYPE;
441: l_next_act_st_date PQH_FR_EMP_STAT_SITUATIONS.actual_start_date%TYPE;
442: l_next_prov_end_date PQH_FR_EMP_STAT_SITUATIONS.provisional_end_date%TYPE;
443: l_next_act_end_date PQH_FR_EMP_STAT_SITUATIONS.actual_end_date%TYPE;
444: l_new_iand_emp_stat_sit_id PQH_FR_EMP_STAT_SITUATIONS.emp_stat_situation_id%TYPE;

Line 441: l_next_act_st_date PQH_FR_EMP_STAT_SITUATIONS.actual_start_date%TYPE;

437: l_next_emp_stat_sit_id PQH_FR_EMP_STAT_SITUATIONS.emp_stat_situation_id%TYPE;
438: l_next_sit_id PQH_FR_EMP_STAT_SITUATIONS.statutory_situation_id%TYPE;
439: l_next_ovn PQH_FR_EMP_STAT_SITUATIONS.object_version_number%TYPE;
440: l_next_prov_st_date PQH_FR_EMP_STAT_SITUATIONS.provisional_start_date%TYPE;
441: l_next_act_st_date PQH_FR_EMP_STAT_SITUATIONS.actual_start_date%TYPE;
442: l_next_prov_end_date PQH_FR_EMP_STAT_SITUATIONS.provisional_end_date%TYPE;
443: l_next_act_end_date PQH_FR_EMP_STAT_SITUATIONS.actual_end_date%TYPE;
444: l_new_iand_emp_stat_sit_id PQH_FR_EMP_STAT_SITUATIONS.emp_stat_situation_id%TYPE;
445: l_new_iand_ovn PQH_FR_EMP_STAT_SITUATIONS.object_version_number%TYPE;

Line 442: l_next_prov_end_date PQH_FR_EMP_STAT_SITUATIONS.provisional_end_date%TYPE;

438: l_next_sit_id PQH_FR_EMP_STAT_SITUATIONS.statutory_situation_id%TYPE;
439: l_next_ovn PQH_FR_EMP_STAT_SITUATIONS.object_version_number%TYPE;
440: l_next_prov_st_date PQH_FR_EMP_STAT_SITUATIONS.provisional_start_date%TYPE;
441: l_next_act_st_date PQH_FR_EMP_STAT_SITUATIONS.actual_start_date%TYPE;
442: l_next_prov_end_date PQH_FR_EMP_STAT_SITUATIONS.provisional_end_date%TYPE;
443: l_next_act_end_date PQH_FR_EMP_STAT_SITUATIONS.actual_end_date%TYPE;
444: l_new_iand_emp_stat_sit_id PQH_FR_EMP_STAT_SITUATIONS.emp_stat_situation_id%TYPE;
445: l_new_iand_ovn PQH_FR_EMP_STAT_SITUATIONS.object_version_number%TYPE;
446: l_object_version_number PQH_FR_EMP_STAT_SITUATIONS.object_version_number%TYPE;

Line 443: l_next_act_end_date PQH_FR_EMP_STAT_SITUATIONS.actual_end_date%TYPE;

439: l_next_ovn PQH_FR_EMP_STAT_SITUATIONS.object_version_number%TYPE;
440: l_next_prov_st_date PQH_FR_EMP_STAT_SITUATIONS.provisional_start_date%TYPE;
441: l_next_act_st_date PQH_FR_EMP_STAT_SITUATIONS.actual_start_date%TYPE;
442: l_next_prov_end_date PQH_FR_EMP_STAT_SITUATIONS.provisional_end_date%TYPE;
443: l_next_act_end_date PQH_FR_EMP_STAT_SITUATIONS.actual_end_date%TYPE;
444: l_new_iand_emp_stat_sit_id PQH_FR_EMP_STAT_SITUATIONS.emp_stat_situation_id%TYPE;
445: l_new_iand_ovn PQH_FR_EMP_STAT_SITUATIONS.object_version_number%TYPE;
446: l_object_version_number PQH_FR_EMP_STAT_SITUATIONS.object_version_number%TYPE;
447: --

Line 444: l_new_iand_emp_stat_sit_id PQH_FR_EMP_STAT_SITUATIONS.emp_stat_situation_id%TYPE;

440: l_next_prov_st_date PQH_FR_EMP_STAT_SITUATIONS.provisional_start_date%TYPE;
441: l_next_act_st_date PQH_FR_EMP_STAT_SITUATIONS.actual_start_date%TYPE;
442: l_next_prov_end_date PQH_FR_EMP_STAT_SITUATIONS.provisional_end_date%TYPE;
443: l_next_act_end_date PQH_FR_EMP_STAT_SITUATIONS.actual_end_date%TYPE;
444: l_new_iand_emp_stat_sit_id PQH_FR_EMP_STAT_SITUATIONS.emp_stat_situation_id%TYPE;
445: l_new_iand_ovn PQH_FR_EMP_STAT_SITUATIONS.object_version_number%TYPE;
446: l_object_version_number PQH_FR_EMP_STAT_SITUATIONS.object_version_number%TYPE;
447: --
448: BEGIN

Line 445: l_new_iand_ovn PQH_FR_EMP_STAT_SITUATIONS.object_version_number%TYPE;

441: l_next_act_st_date PQH_FR_EMP_STAT_SITUATIONS.actual_start_date%TYPE;
442: l_next_prov_end_date PQH_FR_EMP_STAT_SITUATIONS.provisional_end_date%TYPE;
443: l_next_act_end_date PQH_FR_EMP_STAT_SITUATIONS.actual_end_date%TYPE;
444: l_new_iand_emp_stat_sit_id PQH_FR_EMP_STAT_SITUATIONS.emp_stat_situation_id%TYPE;
445: l_new_iand_ovn PQH_FR_EMP_STAT_SITUATIONS.object_version_number%TYPE;
446: l_object_version_number PQH_FR_EMP_STAT_SITUATIONS.object_version_number%TYPE;
447: --
448: BEGIN
449: --

Line 446: l_object_version_number PQH_FR_EMP_STAT_SITUATIONS.object_version_number%TYPE;

442: l_next_prov_end_date PQH_FR_EMP_STAT_SITUATIONS.provisional_end_date%TYPE;
443: l_next_act_end_date PQH_FR_EMP_STAT_SITUATIONS.actual_end_date%TYPE;
444: l_new_iand_emp_stat_sit_id PQH_FR_EMP_STAT_SITUATIONS.emp_stat_situation_id%TYPE;
445: l_new_iand_ovn PQH_FR_EMP_STAT_SITUATIONS.object_version_number%TYPE;
446: l_object_version_number PQH_FR_EMP_STAT_SITUATIONS.object_version_number%TYPE;
447: --
448: BEGIN
449: --
450: --Log entry

Line 694: FROM pqh_fr_emp_stat_situations

690: --
691: --Cursor to fetch the current Situation Details.
692: CURSOR csr_current_situation_dtls(p_emp_stat_situation_id IN NUMBER) IS
693: SELECT *
694: FROM pqh_fr_emp_stat_situations
695: WHERE emp_stat_situation_id = p_emp_stat_situation_id;
696: --
697: --Cursor to fetch IAND record falling after the Situation to be reinstated
698: CURSOR csr_get_iand_dtls(p_iand_stat_sit_id IN NUMBER,

Line 701: FROM pqh_fr_emp_stat_situations

697: --Cursor to fetch IAND record falling after the Situation to be reinstated
698: CURSOR csr_get_iand_dtls(p_iand_stat_sit_id IN NUMBER,
699: p_start_date IN DATE) IS
700: SELECT emp_stat_situation_id, provisional_end_date, actual_end_date, object_version_number
701: FROM pqh_fr_emp_stat_situations
702: WHERE person_id = p_person_id
703: AND statutory_situation_id = p_iand_stat_sit_id
704: AND TRUNC(NVL(actual_start_date,provisional_start_date)) = TRUNC(p_start_date+1);
705: --

Line 709: l_object_version_number PQH_FR_EMP_STAT_SITUATIONS.object_version_number%TYPE;

705: --
706: --Variable Declaration
707: l_proc VARCHAR2(72) := g_package||'reinstate_emp_stat_situation';
708: l_validate BOOLEAN;
709: l_object_version_number PQH_FR_EMP_STAT_SITUATIONS.object_version_number%TYPE;
710: lr_currec csr_current_situation_dtls%ROWTYPE;
711: l_reinstate_sit_id PQH_FR_EMP_STAT_SITUATIONS.statutory_situation_id%TYPE;
712: l_upd_sit_ovn PQH_FR_EMP_STAT_SITUATIONS.object_version_number%TYPE;
713: l_new_emp_sit_id PQH_FR_EMP_STAT_SITUATIONS.emp_stat_situation_id%TYPE;

Line 711: l_reinstate_sit_id PQH_FR_EMP_STAT_SITUATIONS.statutory_situation_id%TYPE;

707: l_proc VARCHAR2(72) := g_package||'reinstate_emp_stat_situation';
708: l_validate BOOLEAN;
709: l_object_version_number PQH_FR_EMP_STAT_SITUATIONS.object_version_number%TYPE;
710: lr_currec csr_current_situation_dtls%ROWTYPE;
711: l_reinstate_sit_id PQH_FR_EMP_STAT_SITUATIONS.statutory_situation_id%TYPE;
712: l_upd_sit_ovn PQH_FR_EMP_STAT_SITUATIONS.object_version_number%TYPE;
713: l_new_emp_sit_id PQH_FR_EMP_STAT_SITUATIONS.emp_stat_situation_id%TYPE;
714: l_new_sit_ovn PQH_FR_EMP_STAT_SITUATIONS.object_version_number%TYPE;
715: l_iand_emp_stat_sit_id PQH_FR_EMP_STAT_SITUATIONS.emp_stat_situation_id%TYPE;

Line 712: l_upd_sit_ovn PQH_FR_EMP_STAT_SITUATIONS.object_version_number%TYPE;

708: l_validate BOOLEAN;
709: l_object_version_number PQH_FR_EMP_STAT_SITUATIONS.object_version_number%TYPE;
710: lr_currec csr_current_situation_dtls%ROWTYPE;
711: l_reinstate_sit_id PQH_FR_EMP_STAT_SITUATIONS.statutory_situation_id%TYPE;
712: l_upd_sit_ovn PQH_FR_EMP_STAT_SITUATIONS.object_version_number%TYPE;
713: l_new_emp_sit_id PQH_FR_EMP_STAT_SITUATIONS.emp_stat_situation_id%TYPE;
714: l_new_sit_ovn PQH_FR_EMP_STAT_SITUATIONS.object_version_number%TYPE;
715: l_iand_emp_stat_sit_id PQH_FR_EMP_STAT_SITUATIONS.emp_stat_situation_id%TYPE;
716: l_iand_prov_end_date PQH_FR_EMP_STAT_SITUATIONS.provisional_end_date%TYPE;

Line 713: l_new_emp_sit_id PQH_FR_EMP_STAT_SITUATIONS.emp_stat_situation_id%TYPE;

709: l_object_version_number PQH_FR_EMP_STAT_SITUATIONS.object_version_number%TYPE;
710: lr_currec csr_current_situation_dtls%ROWTYPE;
711: l_reinstate_sit_id PQH_FR_EMP_STAT_SITUATIONS.statutory_situation_id%TYPE;
712: l_upd_sit_ovn PQH_FR_EMP_STAT_SITUATIONS.object_version_number%TYPE;
713: l_new_emp_sit_id PQH_FR_EMP_STAT_SITUATIONS.emp_stat_situation_id%TYPE;
714: l_new_sit_ovn PQH_FR_EMP_STAT_SITUATIONS.object_version_number%TYPE;
715: l_iand_emp_stat_sit_id PQH_FR_EMP_STAT_SITUATIONS.emp_stat_situation_id%TYPE;
716: l_iand_prov_end_date PQH_FR_EMP_STAT_SITUATIONS.provisional_end_date%TYPE;
717: l_iand_act_end_date PQH_FR_EMP_STAT_SITUATIONS.actual_end_date%TYPE;

Line 714: l_new_sit_ovn PQH_FR_EMP_STAT_SITUATIONS.object_version_number%TYPE;

710: lr_currec csr_current_situation_dtls%ROWTYPE;
711: l_reinstate_sit_id PQH_FR_EMP_STAT_SITUATIONS.statutory_situation_id%TYPE;
712: l_upd_sit_ovn PQH_FR_EMP_STAT_SITUATIONS.object_version_number%TYPE;
713: l_new_emp_sit_id PQH_FR_EMP_STAT_SITUATIONS.emp_stat_situation_id%TYPE;
714: l_new_sit_ovn PQH_FR_EMP_STAT_SITUATIONS.object_version_number%TYPE;
715: l_iand_emp_stat_sit_id PQH_FR_EMP_STAT_SITUATIONS.emp_stat_situation_id%TYPE;
716: l_iand_prov_end_date PQH_FR_EMP_STAT_SITUATIONS.provisional_end_date%TYPE;
717: l_iand_act_end_date PQH_FR_EMP_STAT_SITUATIONS.actual_end_date%TYPE;
718: l_iand_ovn PQH_FR_EMP_STAT_SITUATIONS.object_version_number%TYPE;

Line 715: l_iand_emp_stat_sit_id PQH_FR_EMP_STAT_SITUATIONS.emp_stat_situation_id%TYPE;

711: l_reinstate_sit_id PQH_FR_EMP_STAT_SITUATIONS.statutory_situation_id%TYPE;
712: l_upd_sit_ovn PQH_FR_EMP_STAT_SITUATIONS.object_version_number%TYPE;
713: l_new_emp_sit_id PQH_FR_EMP_STAT_SITUATIONS.emp_stat_situation_id%TYPE;
714: l_new_sit_ovn PQH_FR_EMP_STAT_SITUATIONS.object_version_number%TYPE;
715: l_iand_emp_stat_sit_id PQH_FR_EMP_STAT_SITUATIONS.emp_stat_situation_id%TYPE;
716: l_iand_prov_end_date PQH_FR_EMP_STAT_SITUATIONS.provisional_end_date%TYPE;
717: l_iand_act_end_date PQH_FR_EMP_STAT_SITUATIONS.actual_end_date%TYPE;
718: l_iand_ovn PQH_FR_EMP_STAT_SITUATIONS.object_version_number%TYPE;
719: --

Line 716: l_iand_prov_end_date PQH_FR_EMP_STAT_SITUATIONS.provisional_end_date%TYPE;

712: l_upd_sit_ovn PQH_FR_EMP_STAT_SITUATIONS.object_version_number%TYPE;
713: l_new_emp_sit_id PQH_FR_EMP_STAT_SITUATIONS.emp_stat_situation_id%TYPE;
714: l_new_sit_ovn PQH_FR_EMP_STAT_SITUATIONS.object_version_number%TYPE;
715: l_iand_emp_stat_sit_id PQH_FR_EMP_STAT_SITUATIONS.emp_stat_situation_id%TYPE;
716: l_iand_prov_end_date PQH_FR_EMP_STAT_SITUATIONS.provisional_end_date%TYPE;
717: l_iand_act_end_date PQH_FR_EMP_STAT_SITUATIONS.actual_end_date%TYPE;
718: l_iand_ovn PQH_FR_EMP_STAT_SITUATIONS.object_version_number%TYPE;
719: --
720: BEGIN

Line 717: l_iand_act_end_date PQH_FR_EMP_STAT_SITUATIONS.actual_end_date%TYPE;

713: l_new_emp_sit_id PQH_FR_EMP_STAT_SITUATIONS.emp_stat_situation_id%TYPE;
714: l_new_sit_ovn PQH_FR_EMP_STAT_SITUATIONS.object_version_number%TYPE;
715: l_iand_emp_stat_sit_id PQH_FR_EMP_STAT_SITUATIONS.emp_stat_situation_id%TYPE;
716: l_iand_prov_end_date PQH_FR_EMP_STAT_SITUATIONS.provisional_end_date%TYPE;
717: l_iand_act_end_date PQH_FR_EMP_STAT_SITUATIONS.actual_end_date%TYPE;
718: l_iand_ovn PQH_FR_EMP_STAT_SITUATIONS.object_version_number%TYPE;
719: --
720: BEGIN
721: --

Line 718: l_iand_ovn PQH_FR_EMP_STAT_SITUATIONS.object_version_number%TYPE;

714: l_new_sit_ovn PQH_FR_EMP_STAT_SITUATIONS.object_version_number%TYPE;
715: l_iand_emp_stat_sit_id PQH_FR_EMP_STAT_SITUATIONS.emp_stat_situation_id%TYPE;
716: l_iand_prov_end_date PQH_FR_EMP_STAT_SITUATIONS.provisional_end_date%TYPE;
717: l_iand_act_end_date PQH_FR_EMP_STAT_SITUATIONS.actual_end_date%TYPE;
718: l_iand_ovn PQH_FR_EMP_STAT_SITUATIONS.object_version_number%TYPE;
719: --
720: BEGIN
721: --
722: --Log entry

Line 860: FROM pqh_fr_emp_stat_situations

856: CURSOR csr_current_situation_dtls(p_emp_stat_situation_id IN NUMBER) IS
857: SELECT emp_stat_situation_id, person_id, statutory_situation_id,approval_flag,
858: actual_start_date,provisional_start_date, actual_end_date, provisional_end_date,
859: renewal_flag, renew_stat_situation_id, object_version_number
860: FROM pqh_fr_emp_stat_situations
861: WHERE emp_stat_situation_id = p_emp_stat_situation_id;
862: --
863: --Cursor to get most recent Renewal Situation details
864: CURSOR csr_renew_situation_dtls(p_renew_emp_stat_sit_id IN NUMBER) IS

Line 868: FROM pqh_fr_emp_stat_situations

864: CURSOR csr_renew_situation_dtls(p_renew_emp_stat_sit_id IN NUMBER) IS
865: SELECT emp_stat_situation_id, person_id, statutory_situation_id, approval_flag,
866: actual_start_date, provisional_start_date,
867: actual_end_date, provisional_end_date, object_version_number
868: FROM pqh_fr_emp_stat_situations
869: WHERE renew_stat_situation_id = p_renew_emp_stat_sit_id
870: AND renewal_flag = 'Y'
871: AND NVL(actual_start_date,provisional_start_date)
872: = (SELECT MAX(NVL(actual_start_date,provisional_start_date))

Line 873: FROM pqh_fr_emp_stat_situations

869: WHERE renew_stat_situation_id = p_renew_emp_stat_sit_id
870: AND renewal_flag = 'Y'
871: AND NVL(actual_start_date,provisional_start_date)
872: = (SELECT MAX(NVL(actual_start_date,provisional_start_date))
873: FROM pqh_fr_emp_stat_situations
874: WHERE renew_stat_situation_id = p_renew_emp_stat_sit_id
875: AND renewal_flag = 'Y');
876: --
877: --Cursor for fetching In Activity Normal Default Situation existing after the Situation being renewed.

Line 882: FROM pqh_fr_emp_stat_situations

878: CURSOR csr_get_iand_dtls(p_person_id NUMBER,
879: p_end_date DATE) IS
880: SELECT emp_stat_situation_id, statutory_situation_id, object_version_number,
881: provisional_end_date, actual_end_date
882: FROM pqh_fr_emp_stat_situations
883: WHERE person_id = p_person_id
884: AND statutory_situation_id IN(SELECT statutory_situation_id
885: FROM pqh_fr_stat_situations_v sit
886: ,per_shared_types_vl sh

Line 900: l_emp_stat_situation_id PQH_FR_EMP_STAT_SITUATIONS.emp_stat_situation_id%TYPE;

896: --
897: --Variable Declaration
898: l_proc VARCHAR2(72) := g_package||'renew_emp_stat_situation';
899: l_validate BOOLEAN;
900: l_emp_stat_situation_id PQH_FR_EMP_STAT_SITUATIONS.emp_stat_situation_id%TYPE;
901: l_object_version_number PQH_FR_EMP_STAT_SITUATIONS.object_version_number%TYPE;
902: l_renew_start_date PQH_FR_EMP_STAT_SITUATIONS.provisional_start_date%TYPE;
903: l_renew_end_date PQH_FR_EMP_STAT_SITUATIONS.provisional_start_date%TYPE;
904: l_iand_emp_stat_sit_id PQH_FR_EMP_STAT_SITUATIONS.emp_stat_situation_id%TYPE;

Line 901: l_object_version_number PQH_FR_EMP_STAT_SITUATIONS.object_version_number%TYPE;

897: --Variable Declaration
898: l_proc VARCHAR2(72) := g_package||'renew_emp_stat_situation';
899: l_validate BOOLEAN;
900: l_emp_stat_situation_id PQH_FR_EMP_STAT_SITUATIONS.emp_stat_situation_id%TYPE;
901: l_object_version_number PQH_FR_EMP_STAT_SITUATIONS.object_version_number%TYPE;
902: l_renew_start_date PQH_FR_EMP_STAT_SITUATIONS.provisional_start_date%TYPE;
903: l_renew_end_date PQH_FR_EMP_STAT_SITUATIONS.provisional_start_date%TYPE;
904: l_iand_emp_stat_sit_id PQH_FR_EMP_STAT_SITUATIONS.emp_stat_situation_id%TYPE;
905: l_iand_stat_sit_id PQH_FR_EMP_STAT_SITUATIONS.statutory_situation_id%TYPE;

Line 902: l_renew_start_date PQH_FR_EMP_STAT_SITUATIONS.provisional_start_date%TYPE;

898: l_proc VARCHAR2(72) := g_package||'renew_emp_stat_situation';
899: l_validate BOOLEAN;
900: l_emp_stat_situation_id PQH_FR_EMP_STAT_SITUATIONS.emp_stat_situation_id%TYPE;
901: l_object_version_number PQH_FR_EMP_STAT_SITUATIONS.object_version_number%TYPE;
902: l_renew_start_date PQH_FR_EMP_STAT_SITUATIONS.provisional_start_date%TYPE;
903: l_renew_end_date PQH_FR_EMP_STAT_SITUATIONS.provisional_start_date%TYPE;
904: l_iand_emp_stat_sit_id PQH_FR_EMP_STAT_SITUATIONS.emp_stat_situation_id%TYPE;
905: l_iand_stat_sit_id PQH_FR_EMP_STAT_SITUATIONS.statutory_situation_id%TYPE;
906: l_iand_ovn PQH_FR_EMP_STAT_SITUATIONS.object_version_number%TYPE;

Line 903: l_renew_end_date PQH_FR_EMP_STAT_SITUATIONS.provisional_start_date%TYPE;

899: l_validate BOOLEAN;
900: l_emp_stat_situation_id PQH_FR_EMP_STAT_SITUATIONS.emp_stat_situation_id%TYPE;
901: l_object_version_number PQH_FR_EMP_STAT_SITUATIONS.object_version_number%TYPE;
902: l_renew_start_date PQH_FR_EMP_STAT_SITUATIONS.provisional_start_date%TYPE;
903: l_renew_end_date PQH_FR_EMP_STAT_SITUATIONS.provisional_start_date%TYPE;
904: l_iand_emp_stat_sit_id PQH_FR_EMP_STAT_SITUATIONS.emp_stat_situation_id%TYPE;
905: l_iand_stat_sit_id PQH_FR_EMP_STAT_SITUATIONS.statutory_situation_id%TYPE;
906: l_iand_ovn PQH_FR_EMP_STAT_SITUATIONS.object_version_number%TYPE;
907: l_iand_prov_end_date PQH_FR_EMP_STAT_SITUATIONS.provisional_end_date%TYPE;

Line 904: l_iand_emp_stat_sit_id PQH_FR_EMP_STAT_SITUATIONS.emp_stat_situation_id%TYPE;

900: l_emp_stat_situation_id PQH_FR_EMP_STAT_SITUATIONS.emp_stat_situation_id%TYPE;
901: l_object_version_number PQH_FR_EMP_STAT_SITUATIONS.object_version_number%TYPE;
902: l_renew_start_date PQH_FR_EMP_STAT_SITUATIONS.provisional_start_date%TYPE;
903: l_renew_end_date PQH_FR_EMP_STAT_SITUATIONS.provisional_start_date%TYPE;
904: l_iand_emp_stat_sit_id PQH_FR_EMP_STAT_SITUATIONS.emp_stat_situation_id%TYPE;
905: l_iand_stat_sit_id PQH_FR_EMP_STAT_SITUATIONS.statutory_situation_id%TYPE;
906: l_iand_ovn PQH_FR_EMP_STAT_SITUATIONS.object_version_number%TYPE;
907: l_iand_prov_end_date PQH_FR_EMP_STAT_SITUATIONS.provisional_end_date%TYPE;
908: l_iand_act_end_date PQH_FR_EMP_STAT_SITUATIONS.actual_end_date%TYPE;

Line 905: l_iand_stat_sit_id PQH_FR_EMP_STAT_SITUATIONS.statutory_situation_id%TYPE;

901: l_object_version_number PQH_FR_EMP_STAT_SITUATIONS.object_version_number%TYPE;
902: l_renew_start_date PQH_FR_EMP_STAT_SITUATIONS.provisional_start_date%TYPE;
903: l_renew_end_date PQH_FR_EMP_STAT_SITUATIONS.provisional_start_date%TYPE;
904: l_iand_emp_stat_sit_id PQH_FR_EMP_STAT_SITUATIONS.emp_stat_situation_id%TYPE;
905: l_iand_stat_sit_id PQH_FR_EMP_STAT_SITUATIONS.statutory_situation_id%TYPE;
906: l_iand_ovn PQH_FR_EMP_STAT_SITUATIONS.object_version_number%TYPE;
907: l_iand_prov_end_date PQH_FR_EMP_STAT_SITUATIONS.provisional_end_date%TYPE;
908: l_iand_act_end_date PQH_FR_EMP_STAT_SITUATIONS.actual_end_date%TYPE;
909: l_new_emp_sit_id PQH_FR_EMP_STAT_SITUATIONS.emp_stat_situation_id%TYPE;

Line 906: l_iand_ovn PQH_FR_EMP_STAT_SITUATIONS.object_version_number%TYPE;

902: l_renew_start_date PQH_FR_EMP_STAT_SITUATIONS.provisional_start_date%TYPE;
903: l_renew_end_date PQH_FR_EMP_STAT_SITUATIONS.provisional_start_date%TYPE;
904: l_iand_emp_stat_sit_id PQH_FR_EMP_STAT_SITUATIONS.emp_stat_situation_id%TYPE;
905: l_iand_stat_sit_id PQH_FR_EMP_STAT_SITUATIONS.statutory_situation_id%TYPE;
906: l_iand_ovn PQH_FR_EMP_STAT_SITUATIONS.object_version_number%TYPE;
907: l_iand_prov_end_date PQH_FR_EMP_STAT_SITUATIONS.provisional_end_date%TYPE;
908: l_iand_act_end_date PQH_FR_EMP_STAT_SITUATIONS.actual_end_date%TYPE;
909: l_new_emp_sit_id PQH_FR_EMP_STAT_SITUATIONS.emp_stat_situation_id%TYPE;
910: l_new_sit_ovn PQH_FR_EMP_STAT_SITUATIONS.object_version_number%TYPE;

Line 907: l_iand_prov_end_date PQH_FR_EMP_STAT_SITUATIONS.provisional_end_date%TYPE;

903: l_renew_end_date PQH_FR_EMP_STAT_SITUATIONS.provisional_start_date%TYPE;
904: l_iand_emp_stat_sit_id PQH_FR_EMP_STAT_SITUATIONS.emp_stat_situation_id%TYPE;
905: l_iand_stat_sit_id PQH_FR_EMP_STAT_SITUATIONS.statutory_situation_id%TYPE;
906: l_iand_ovn PQH_FR_EMP_STAT_SITUATIONS.object_version_number%TYPE;
907: l_iand_prov_end_date PQH_FR_EMP_STAT_SITUATIONS.provisional_end_date%TYPE;
908: l_iand_act_end_date PQH_FR_EMP_STAT_SITUATIONS.actual_end_date%TYPE;
909: l_new_emp_sit_id PQH_FR_EMP_STAT_SITUATIONS.emp_stat_situation_id%TYPE;
910: l_new_sit_ovn PQH_FR_EMP_STAT_SITUATIONS.object_version_number%TYPE;
911: l_rn_emp_stat_sit_id PQH_FR_EMP_STAT_SITUATIONS.emp_stat_situation_id%TYPE;

Line 908: l_iand_act_end_date PQH_FR_EMP_STAT_SITUATIONS.actual_end_date%TYPE;

904: l_iand_emp_stat_sit_id PQH_FR_EMP_STAT_SITUATIONS.emp_stat_situation_id%TYPE;
905: l_iand_stat_sit_id PQH_FR_EMP_STAT_SITUATIONS.statutory_situation_id%TYPE;
906: l_iand_ovn PQH_FR_EMP_STAT_SITUATIONS.object_version_number%TYPE;
907: l_iand_prov_end_date PQH_FR_EMP_STAT_SITUATIONS.provisional_end_date%TYPE;
908: l_iand_act_end_date PQH_FR_EMP_STAT_SITUATIONS.actual_end_date%TYPE;
909: l_new_emp_sit_id PQH_FR_EMP_STAT_SITUATIONS.emp_stat_situation_id%TYPE;
910: l_new_sit_ovn PQH_FR_EMP_STAT_SITUATIONS.object_version_number%TYPE;
911: l_rn_emp_stat_sit_id PQH_FR_EMP_STAT_SITUATIONS.emp_stat_situation_id%TYPE;
912: l_rn_person_id PQH_FR_EMP_STAT_SITUATIONS.person_id%TYPE;

Line 909: l_new_emp_sit_id PQH_FR_EMP_STAT_SITUATIONS.emp_stat_situation_id%TYPE;

905: l_iand_stat_sit_id PQH_FR_EMP_STAT_SITUATIONS.statutory_situation_id%TYPE;
906: l_iand_ovn PQH_FR_EMP_STAT_SITUATIONS.object_version_number%TYPE;
907: l_iand_prov_end_date PQH_FR_EMP_STAT_SITUATIONS.provisional_end_date%TYPE;
908: l_iand_act_end_date PQH_FR_EMP_STAT_SITUATIONS.actual_end_date%TYPE;
909: l_new_emp_sit_id PQH_FR_EMP_STAT_SITUATIONS.emp_stat_situation_id%TYPE;
910: l_new_sit_ovn PQH_FR_EMP_STAT_SITUATIONS.object_version_number%TYPE;
911: l_rn_emp_stat_sit_id PQH_FR_EMP_STAT_SITUATIONS.emp_stat_situation_id%TYPE;
912: l_rn_person_id PQH_FR_EMP_STAT_SITUATIONS.person_id%TYPE;
913: l_rn_stat_sit_id PQH_FR_EMP_STAT_SITUATIONS.statutory_situation_id%TYPE;

Line 910: l_new_sit_ovn PQH_FR_EMP_STAT_SITUATIONS.object_version_number%TYPE;

906: l_iand_ovn PQH_FR_EMP_STAT_SITUATIONS.object_version_number%TYPE;
907: l_iand_prov_end_date PQH_FR_EMP_STAT_SITUATIONS.provisional_end_date%TYPE;
908: l_iand_act_end_date PQH_FR_EMP_STAT_SITUATIONS.actual_end_date%TYPE;
909: l_new_emp_sit_id PQH_FR_EMP_STAT_SITUATIONS.emp_stat_situation_id%TYPE;
910: l_new_sit_ovn PQH_FR_EMP_STAT_SITUATIONS.object_version_number%TYPE;
911: l_rn_emp_stat_sit_id PQH_FR_EMP_STAT_SITUATIONS.emp_stat_situation_id%TYPE;
912: l_rn_person_id PQH_FR_EMP_STAT_SITUATIONS.person_id%TYPE;
913: l_rn_stat_sit_id PQH_FR_EMP_STAT_SITUATIONS.statutory_situation_id%TYPE;
914: l_rn_approval_flag PQH_FR_EMP_STAT_SITUATIONS.approval_flag%TYPE;

Line 911: l_rn_emp_stat_sit_id PQH_FR_EMP_STAT_SITUATIONS.emp_stat_situation_id%TYPE;

907: l_iand_prov_end_date PQH_FR_EMP_STAT_SITUATIONS.provisional_end_date%TYPE;
908: l_iand_act_end_date PQH_FR_EMP_STAT_SITUATIONS.actual_end_date%TYPE;
909: l_new_emp_sit_id PQH_FR_EMP_STAT_SITUATIONS.emp_stat_situation_id%TYPE;
910: l_new_sit_ovn PQH_FR_EMP_STAT_SITUATIONS.object_version_number%TYPE;
911: l_rn_emp_stat_sit_id PQH_FR_EMP_STAT_SITUATIONS.emp_stat_situation_id%TYPE;
912: l_rn_person_id PQH_FR_EMP_STAT_SITUATIONS.person_id%TYPE;
913: l_rn_stat_sit_id PQH_FR_EMP_STAT_SITUATIONS.statutory_situation_id%TYPE;
914: l_rn_approval_flag PQH_FR_EMP_STAT_SITUATIONS.approval_flag%TYPE;
915: l_rn_act_st_date PQH_FR_EMP_STAT_SITUATIONS.actual_start_date%TYPE;

Line 912: l_rn_person_id PQH_FR_EMP_STAT_SITUATIONS.person_id%TYPE;

908: l_iand_act_end_date PQH_FR_EMP_STAT_SITUATIONS.actual_end_date%TYPE;
909: l_new_emp_sit_id PQH_FR_EMP_STAT_SITUATIONS.emp_stat_situation_id%TYPE;
910: l_new_sit_ovn PQH_FR_EMP_STAT_SITUATIONS.object_version_number%TYPE;
911: l_rn_emp_stat_sit_id PQH_FR_EMP_STAT_SITUATIONS.emp_stat_situation_id%TYPE;
912: l_rn_person_id PQH_FR_EMP_STAT_SITUATIONS.person_id%TYPE;
913: l_rn_stat_sit_id PQH_FR_EMP_STAT_SITUATIONS.statutory_situation_id%TYPE;
914: l_rn_approval_flag PQH_FR_EMP_STAT_SITUATIONS.approval_flag%TYPE;
915: l_rn_act_st_date PQH_FR_EMP_STAT_SITUATIONS.actual_start_date%TYPE;
916: l_rn_prov_st_date PQH_FR_EMP_STAT_SITUATIONS.provisional_start_date%TYPE;

Line 913: l_rn_stat_sit_id PQH_FR_EMP_STAT_SITUATIONS.statutory_situation_id%TYPE;

909: l_new_emp_sit_id PQH_FR_EMP_STAT_SITUATIONS.emp_stat_situation_id%TYPE;
910: l_new_sit_ovn PQH_FR_EMP_STAT_SITUATIONS.object_version_number%TYPE;
911: l_rn_emp_stat_sit_id PQH_FR_EMP_STAT_SITUATIONS.emp_stat_situation_id%TYPE;
912: l_rn_person_id PQH_FR_EMP_STAT_SITUATIONS.person_id%TYPE;
913: l_rn_stat_sit_id PQH_FR_EMP_STAT_SITUATIONS.statutory_situation_id%TYPE;
914: l_rn_approval_flag PQH_FR_EMP_STAT_SITUATIONS.approval_flag%TYPE;
915: l_rn_act_st_date PQH_FR_EMP_STAT_SITUATIONS.actual_start_date%TYPE;
916: l_rn_prov_st_date PQH_FR_EMP_STAT_SITUATIONS.provisional_start_date%TYPE;
917: l_rn_act_end_date PQH_FR_EMP_STAT_SITUATIONS.actual_end_date%TYPE;

Line 914: l_rn_approval_flag PQH_FR_EMP_STAT_SITUATIONS.approval_flag%TYPE;

910: l_new_sit_ovn PQH_FR_EMP_STAT_SITUATIONS.object_version_number%TYPE;
911: l_rn_emp_stat_sit_id PQH_FR_EMP_STAT_SITUATIONS.emp_stat_situation_id%TYPE;
912: l_rn_person_id PQH_FR_EMP_STAT_SITUATIONS.person_id%TYPE;
913: l_rn_stat_sit_id PQH_FR_EMP_STAT_SITUATIONS.statutory_situation_id%TYPE;
914: l_rn_approval_flag PQH_FR_EMP_STAT_SITUATIONS.approval_flag%TYPE;
915: l_rn_act_st_date PQH_FR_EMP_STAT_SITUATIONS.actual_start_date%TYPE;
916: l_rn_prov_st_date PQH_FR_EMP_STAT_SITUATIONS.provisional_start_date%TYPE;
917: l_rn_act_end_date PQH_FR_EMP_STAT_SITUATIONS.actual_end_date%TYPE;
918: l_rn_prov_end_date PQH_FR_EMP_STAT_SITUATIONS.provisional_end_date%TYPE;

Line 915: l_rn_act_st_date PQH_FR_EMP_STAT_SITUATIONS.actual_start_date%TYPE;

911: l_rn_emp_stat_sit_id PQH_FR_EMP_STAT_SITUATIONS.emp_stat_situation_id%TYPE;
912: l_rn_person_id PQH_FR_EMP_STAT_SITUATIONS.person_id%TYPE;
913: l_rn_stat_sit_id PQH_FR_EMP_STAT_SITUATIONS.statutory_situation_id%TYPE;
914: l_rn_approval_flag PQH_FR_EMP_STAT_SITUATIONS.approval_flag%TYPE;
915: l_rn_act_st_date PQH_FR_EMP_STAT_SITUATIONS.actual_start_date%TYPE;
916: l_rn_prov_st_date PQH_FR_EMP_STAT_SITUATIONS.provisional_start_date%TYPE;
917: l_rn_act_end_date PQH_FR_EMP_STAT_SITUATIONS.actual_end_date%TYPE;
918: l_rn_prov_end_date PQH_FR_EMP_STAT_SITUATIONS.provisional_end_date%TYPE;
919: l_rn_renewal_flag PQH_FR_EMP_STAT_SITUATIONS.renewal_flag%TYPE;

Line 916: l_rn_prov_st_date PQH_FR_EMP_STAT_SITUATIONS.provisional_start_date%TYPE;

912: l_rn_person_id PQH_FR_EMP_STAT_SITUATIONS.person_id%TYPE;
913: l_rn_stat_sit_id PQH_FR_EMP_STAT_SITUATIONS.statutory_situation_id%TYPE;
914: l_rn_approval_flag PQH_FR_EMP_STAT_SITUATIONS.approval_flag%TYPE;
915: l_rn_act_st_date PQH_FR_EMP_STAT_SITUATIONS.actual_start_date%TYPE;
916: l_rn_prov_st_date PQH_FR_EMP_STAT_SITUATIONS.provisional_start_date%TYPE;
917: l_rn_act_end_date PQH_FR_EMP_STAT_SITUATIONS.actual_end_date%TYPE;
918: l_rn_prov_end_date PQH_FR_EMP_STAT_SITUATIONS.provisional_end_date%TYPE;
919: l_rn_renewal_flag PQH_FR_EMP_STAT_SITUATIONS.renewal_flag%TYPE;
920: l_rn_renew_stat_sit_id PQH_FR_EMP_STAT_SITUATIONS.renew_stat_situation_id%TYPE;

Line 917: l_rn_act_end_date PQH_FR_EMP_STAT_SITUATIONS.actual_end_date%TYPE;

913: l_rn_stat_sit_id PQH_FR_EMP_STAT_SITUATIONS.statutory_situation_id%TYPE;
914: l_rn_approval_flag PQH_FR_EMP_STAT_SITUATIONS.approval_flag%TYPE;
915: l_rn_act_st_date PQH_FR_EMP_STAT_SITUATIONS.actual_start_date%TYPE;
916: l_rn_prov_st_date PQH_FR_EMP_STAT_SITUATIONS.provisional_start_date%TYPE;
917: l_rn_act_end_date PQH_FR_EMP_STAT_SITUATIONS.actual_end_date%TYPE;
918: l_rn_prov_end_date PQH_FR_EMP_STAT_SITUATIONS.provisional_end_date%TYPE;
919: l_rn_renewal_flag PQH_FR_EMP_STAT_SITUATIONS.renewal_flag%TYPE;
920: l_rn_renew_stat_sit_id PQH_FR_EMP_STAT_SITUATIONS.renew_stat_situation_id%TYPE;
921: l_rn_ovn PQH_FR_EMP_STAT_SITUATIONS.object_version_number%TYPE;

Line 918: l_rn_prov_end_date PQH_FR_EMP_STAT_SITUATIONS.provisional_end_date%TYPE;

914: l_rn_approval_flag PQH_FR_EMP_STAT_SITUATIONS.approval_flag%TYPE;
915: l_rn_act_st_date PQH_FR_EMP_STAT_SITUATIONS.actual_start_date%TYPE;
916: l_rn_prov_st_date PQH_FR_EMP_STAT_SITUATIONS.provisional_start_date%TYPE;
917: l_rn_act_end_date PQH_FR_EMP_STAT_SITUATIONS.actual_end_date%TYPE;
918: l_rn_prov_end_date PQH_FR_EMP_STAT_SITUATIONS.provisional_end_date%TYPE;
919: l_rn_renewal_flag PQH_FR_EMP_STAT_SITUATIONS.renewal_flag%TYPE;
920: l_rn_renew_stat_sit_id PQH_FR_EMP_STAT_SITUATIONS.renew_stat_situation_id%TYPE;
921: l_rn_ovn PQH_FR_EMP_STAT_SITUATIONS.object_version_number%TYPE;
922: --

Line 919: l_rn_renewal_flag PQH_FR_EMP_STAT_SITUATIONS.renewal_flag%TYPE;

915: l_rn_act_st_date PQH_FR_EMP_STAT_SITUATIONS.actual_start_date%TYPE;
916: l_rn_prov_st_date PQH_FR_EMP_STAT_SITUATIONS.provisional_start_date%TYPE;
917: l_rn_act_end_date PQH_FR_EMP_STAT_SITUATIONS.actual_end_date%TYPE;
918: l_rn_prov_end_date PQH_FR_EMP_STAT_SITUATIONS.provisional_end_date%TYPE;
919: l_rn_renewal_flag PQH_FR_EMP_STAT_SITUATIONS.renewal_flag%TYPE;
920: l_rn_renew_stat_sit_id PQH_FR_EMP_STAT_SITUATIONS.renew_stat_situation_id%TYPE;
921: l_rn_ovn PQH_FR_EMP_STAT_SITUATIONS.object_version_number%TYPE;
922: --
923: BEGIN

Line 920: l_rn_renew_stat_sit_id PQH_FR_EMP_STAT_SITUATIONS.renew_stat_situation_id%TYPE;

916: l_rn_prov_st_date PQH_FR_EMP_STAT_SITUATIONS.provisional_start_date%TYPE;
917: l_rn_act_end_date PQH_FR_EMP_STAT_SITUATIONS.actual_end_date%TYPE;
918: l_rn_prov_end_date PQH_FR_EMP_STAT_SITUATIONS.provisional_end_date%TYPE;
919: l_rn_renewal_flag PQH_FR_EMP_STAT_SITUATIONS.renewal_flag%TYPE;
920: l_rn_renew_stat_sit_id PQH_FR_EMP_STAT_SITUATIONS.renew_stat_situation_id%TYPE;
921: l_rn_ovn PQH_FR_EMP_STAT_SITUATIONS.object_version_number%TYPE;
922: --
923: BEGIN
924: --

Line 921: l_rn_ovn PQH_FR_EMP_STAT_SITUATIONS.object_version_number%TYPE;

917: l_rn_act_end_date PQH_FR_EMP_STAT_SITUATIONS.actual_end_date%TYPE;
918: l_rn_prov_end_date PQH_FR_EMP_STAT_SITUATIONS.provisional_end_date%TYPE;
919: l_rn_renewal_flag PQH_FR_EMP_STAT_SITUATIONS.renewal_flag%TYPE;
920: l_rn_renew_stat_sit_id PQH_FR_EMP_STAT_SITUATIONS.renew_stat_situation_id%TYPE;
921: l_rn_ovn PQH_FR_EMP_STAT_SITUATIONS.object_version_number%TYPE;
922: --
923: BEGIN
924: --
925: --Log entry

Line 1110: FROM pqh_fr_emp_stat_situations

1106: --
1107: --Cursor to get current Situation details
1108: CURSOR csr_current_situation_dtls(p_emp_stat_situation_id IN NUMBER) IS
1109: SELECT person_id, actual_start_date, provisional_start_date, actual_end_date, provisional_end_date
1110: FROM pqh_fr_emp_stat_situations
1111: WHERE emp_stat_situation_id = p_emp_stat_situation_id;
1112: --
1113: --Cursor to fetch prior Situation falling before the Situation to be deleted
1114: CURSOR csr_get_prior_sit_dtls(p_person_id IN NUMBER,

Line 1118: FROM pqh_fr_emp_stat_situations

1114: CURSOR csr_get_prior_sit_dtls(p_person_id IN NUMBER,
1115: p_date IN DATE) IS
1116: SELECT emp_stat_situation_id, statutory_situation_id, object_version_number,
1117: provisional_start_date, actual_start_date, provisional_end_date, actual_end_date
1118: FROM pqh_fr_emp_stat_situations
1119: WHERE person_id = p_person_id
1120: AND TRUNC(NVL(actual_end_date,provisional_end_date)) = TRUNC(p_date-1);
1121: --
1122: --Cursor to fetch future Situation falling after the Situation to be deleted

Line 1127: FROM pqh_fr_emp_stat_situations

1123: CURSOR csr_get_next_sit_dtls(p_person_id IN NUMBER,
1124: p_date IN DATE) IS
1125: SELECT emp_stat_situation_id, statutory_situation_id, object_version_number,
1126: provisional_end_date, actual_end_date
1127: FROM pqh_fr_emp_stat_situations
1128: WHERE person_id = p_person_id
1129: AND TRUNC(NVL(actual_start_date,provisional_start_date)) = TRUNC(p_date+1);
1130: --
1131: -- Variable Declaration

Line 1134: l_person_id PQH_FR_EMP_STAT_SITUATIONS.person_id%TYPE;

1130: --
1131: -- Variable Declaration
1132: l_proc VARCHAR2(72) := g_package||'delete_emp_stat_situation';
1133: l_validate BOOLEAN;
1134: l_person_id PQH_FR_EMP_STAT_SITUATIONS.person_id%TYPE;
1135: l_act_st_dt PQH_FR_EMP_STAT_SITUATIONS.actual_start_date%TYPE;
1136: l_prov_st_dt PQH_FR_EMP_STAT_SITUATIONS.provisional_start_date%TYPE;
1137: l_act_end_dt PQH_FR_EMP_STAT_SITUATIONS.actual_end_date%TYPE;
1138: l_prov_end_dt PQH_FR_EMP_STAT_SITUATIONS.provisional_end_date%TYPE;

Line 1135: l_act_st_dt PQH_FR_EMP_STAT_SITUATIONS.actual_start_date%TYPE;

1131: -- Variable Declaration
1132: l_proc VARCHAR2(72) := g_package||'delete_emp_stat_situation';
1133: l_validate BOOLEAN;
1134: l_person_id PQH_FR_EMP_STAT_SITUATIONS.person_id%TYPE;
1135: l_act_st_dt PQH_FR_EMP_STAT_SITUATIONS.actual_start_date%TYPE;
1136: l_prov_st_dt PQH_FR_EMP_STAT_SITUATIONS.provisional_start_date%TYPE;
1137: l_act_end_dt PQH_FR_EMP_STAT_SITUATIONS.actual_end_date%TYPE;
1138: l_prov_end_dt PQH_FR_EMP_STAT_SITUATIONS.provisional_end_date%TYPE;
1139: l_prior_emp_stat_sit_id PQH_FR_EMP_STAT_SITUATIONS.emp_stat_situation_id%TYPE;

Line 1136: l_prov_st_dt PQH_FR_EMP_STAT_SITUATIONS.provisional_start_date%TYPE;

1132: l_proc VARCHAR2(72) := g_package||'delete_emp_stat_situation';
1133: l_validate BOOLEAN;
1134: l_person_id PQH_FR_EMP_STAT_SITUATIONS.person_id%TYPE;
1135: l_act_st_dt PQH_FR_EMP_STAT_SITUATIONS.actual_start_date%TYPE;
1136: l_prov_st_dt PQH_FR_EMP_STAT_SITUATIONS.provisional_start_date%TYPE;
1137: l_act_end_dt PQH_FR_EMP_STAT_SITUATIONS.actual_end_date%TYPE;
1138: l_prov_end_dt PQH_FR_EMP_STAT_SITUATIONS.provisional_end_date%TYPE;
1139: l_prior_emp_stat_sit_id PQH_FR_EMP_STAT_SITUATIONS.emp_stat_situation_id%TYPE;
1140: l_prior_sit_id PQH_FR_EMP_STAT_SITUATIONS.statutory_situation_id%TYPE;

Line 1137: l_act_end_dt PQH_FR_EMP_STAT_SITUATIONS.actual_end_date%TYPE;

1133: l_validate BOOLEAN;
1134: l_person_id PQH_FR_EMP_STAT_SITUATIONS.person_id%TYPE;
1135: l_act_st_dt PQH_FR_EMP_STAT_SITUATIONS.actual_start_date%TYPE;
1136: l_prov_st_dt PQH_FR_EMP_STAT_SITUATIONS.provisional_start_date%TYPE;
1137: l_act_end_dt PQH_FR_EMP_STAT_SITUATIONS.actual_end_date%TYPE;
1138: l_prov_end_dt PQH_FR_EMP_STAT_SITUATIONS.provisional_end_date%TYPE;
1139: l_prior_emp_stat_sit_id PQH_FR_EMP_STAT_SITUATIONS.emp_stat_situation_id%TYPE;
1140: l_prior_sit_id PQH_FR_EMP_STAT_SITUATIONS.statutory_situation_id%TYPE;
1141: l_prior_ovn PQH_FR_EMP_STAT_SITUATIONS.object_version_number%TYPE;

Line 1138: l_prov_end_dt PQH_FR_EMP_STAT_SITUATIONS.provisional_end_date%TYPE;

1134: l_person_id PQH_FR_EMP_STAT_SITUATIONS.person_id%TYPE;
1135: l_act_st_dt PQH_FR_EMP_STAT_SITUATIONS.actual_start_date%TYPE;
1136: l_prov_st_dt PQH_FR_EMP_STAT_SITUATIONS.provisional_start_date%TYPE;
1137: l_act_end_dt PQH_FR_EMP_STAT_SITUATIONS.actual_end_date%TYPE;
1138: l_prov_end_dt PQH_FR_EMP_STAT_SITUATIONS.provisional_end_date%TYPE;
1139: l_prior_emp_stat_sit_id PQH_FR_EMP_STAT_SITUATIONS.emp_stat_situation_id%TYPE;
1140: l_prior_sit_id PQH_FR_EMP_STAT_SITUATIONS.statutory_situation_id%TYPE;
1141: l_prior_ovn PQH_FR_EMP_STAT_SITUATIONS.object_version_number%TYPE;
1142: l_prior_prov_st_date PQH_FR_EMP_STAT_SITUATIONS.provisional_start_date%TYPE;

Line 1139: l_prior_emp_stat_sit_id PQH_FR_EMP_STAT_SITUATIONS.emp_stat_situation_id%TYPE;

1135: l_act_st_dt PQH_FR_EMP_STAT_SITUATIONS.actual_start_date%TYPE;
1136: l_prov_st_dt PQH_FR_EMP_STAT_SITUATIONS.provisional_start_date%TYPE;
1137: l_act_end_dt PQH_FR_EMP_STAT_SITUATIONS.actual_end_date%TYPE;
1138: l_prov_end_dt PQH_FR_EMP_STAT_SITUATIONS.provisional_end_date%TYPE;
1139: l_prior_emp_stat_sit_id PQH_FR_EMP_STAT_SITUATIONS.emp_stat_situation_id%TYPE;
1140: l_prior_sit_id PQH_FR_EMP_STAT_SITUATIONS.statutory_situation_id%TYPE;
1141: l_prior_ovn PQH_FR_EMP_STAT_SITUATIONS.object_version_number%TYPE;
1142: l_prior_prov_st_date PQH_FR_EMP_STAT_SITUATIONS.provisional_start_date%TYPE;
1143: l_prior_act_st_date PQH_FR_EMP_STAT_SITUATIONS.actual_start_date%TYPE;

Line 1140: l_prior_sit_id PQH_FR_EMP_STAT_SITUATIONS.statutory_situation_id%TYPE;

1136: l_prov_st_dt PQH_FR_EMP_STAT_SITUATIONS.provisional_start_date%TYPE;
1137: l_act_end_dt PQH_FR_EMP_STAT_SITUATIONS.actual_end_date%TYPE;
1138: l_prov_end_dt PQH_FR_EMP_STAT_SITUATIONS.provisional_end_date%TYPE;
1139: l_prior_emp_stat_sit_id PQH_FR_EMP_STAT_SITUATIONS.emp_stat_situation_id%TYPE;
1140: l_prior_sit_id PQH_FR_EMP_STAT_SITUATIONS.statutory_situation_id%TYPE;
1141: l_prior_ovn PQH_FR_EMP_STAT_SITUATIONS.object_version_number%TYPE;
1142: l_prior_prov_st_date PQH_FR_EMP_STAT_SITUATIONS.provisional_start_date%TYPE;
1143: l_prior_act_st_date PQH_FR_EMP_STAT_SITUATIONS.actual_start_date%TYPE;
1144: l_prior_prov_end_date PQH_FR_EMP_STAT_SITUATIONS.provisional_start_date%TYPE;

Line 1141: l_prior_ovn PQH_FR_EMP_STAT_SITUATIONS.object_version_number%TYPE;

1137: l_act_end_dt PQH_FR_EMP_STAT_SITUATIONS.actual_end_date%TYPE;
1138: l_prov_end_dt PQH_FR_EMP_STAT_SITUATIONS.provisional_end_date%TYPE;
1139: l_prior_emp_stat_sit_id PQH_FR_EMP_STAT_SITUATIONS.emp_stat_situation_id%TYPE;
1140: l_prior_sit_id PQH_FR_EMP_STAT_SITUATIONS.statutory_situation_id%TYPE;
1141: l_prior_ovn PQH_FR_EMP_STAT_SITUATIONS.object_version_number%TYPE;
1142: l_prior_prov_st_date PQH_FR_EMP_STAT_SITUATIONS.provisional_start_date%TYPE;
1143: l_prior_act_st_date PQH_FR_EMP_STAT_SITUATIONS.actual_start_date%TYPE;
1144: l_prior_prov_end_date PQH_FR_EMP_STAT_SITUATIONS.provisional_start_date%TYPE;
1145: l_prior_act_end_date PQH_FR_EMP_STAT_SITUATIONS.actual_end_date%TYPE;

Line 1142: l_prior_prov_st_date PQH_FR_EMP_STAT_SITUATIONS.provisional_start_date%TYPE;

1138: l_prov_end_dt PQH_FR_EMP_STAT_SITUATIONS.provisional_end_date%TYPE;
1139: l_prior_emp_stat_sit_id PQH_FR_EMP_STAT_SITUATIONS.emp_stat_situation_id%TYPE;
1140: l_prior_sit_id PQH_FR_EMP_STAT_SITUATIONS.statutory_situation_id%TYPE;
1141: l_prior_ovn PQH_FR_EMP_STAT_SITUATIONS.object_version_number%TYPE;
1142: l_prior_prov_st_date PQH_FR_EMP_STAT_SITUATIONS.provisional_start_date%TYPE;
1143: l_prior_act_st_date PQH_FR_EMP_STAT_SITUATIONS.actual_start_date%TYPE;
1144: l_prior_prov_end_date PQH_FR_EMP_STAT_SITUATIONS.provisional_start_date%TYPE;
1145: l_prior_act_end_date PQH_FR_EMP_STAT_SITUATIONS.actual_end_date%TYPE;
1146: l_next_emp_stat_sit_id PQH_FR_EMP_STAT_SITUATIONS.emp_stat_situation_id%TYPE;

Line 1143: l_prior_act_st_date PQH_FR_EMP_STAT_SITUATIONS.actual_start_date%TYPE;

1139: l_prior_emp_stat_sit_id PQH_FR_EMP_STAT_SITUATIONS.emp_stat_situation_id%TYPE;
1140: l_prior_sit_id PQH_FR_EMP_STAT_SITUATIONS.statutory_situation_id%TYPE;
1141: l_prior_ovn PQH_FR_EMP_STAT_SITUATIONS.object_version_number%TYPE;
1142: l_prior_prov_st_date PQH_FR_EMP_STAT_SITUATIONS.provisional_start_date%TYPE;
1143: l_prior_act_st_date PQH_FR_EMP_STAT_SITUATIONS.actual_start_date%TYPE;
1144: l_prior_prov_end_date PQH_FR_EMP_STAT_SITUATIONS.provisional_start_date%TYPE;
1145: l_prior_act_end_date PQH_FR_EMP_STAT_SITUATIONS.actual_end_date%TYPE;
1146: l_next_emp_stat_sit_id PQH_FR_EMP_STAT_SITUATIONS.emp_stat_situation_id%TYPE;
1147: l_next_sit_id PQH_FR_EMP_STAT_SITUATIONS.statutory_situation_id%TYPE;

Line 1144: l_prior_prov_end_date PQH_FR_EMP_STAT_SITUATIONS.provisional_start_date%TYPE;

1140: l_prior_sit_id PQH_FR_EMP_STAT_SITUATIONS.statutory_situation_id%TYPE;
1141: l_prior_ovn PQH_FR_EMP_STAT_SITUATIONS.object_version_number%TYPE;
1142: l_prior_prov_st_date PQH_FR_EMP_STAT_SITUATIONS.provisional_start_date%TYPE;
1143: l_prior_act_st_date PQH_FR_EMP_STAT_SITUATIONS.actual_start_date%TYPE;
1144: l_prior_prov_end_date PQH_FR_EMP_STAT_SITUATIONS.provisional_start_date%TYPE;
1145: l_prior_act_end_date PQH_FR_EMP_STAT_SITUATIONS.actual_end_date%TYPE;
1146: l_next_emp_stat_sit_id PQH_FR_EMP_STAT_SITUATIONS.emp_stat_situation_id%TYPE;
1147: l_next_sit_id PQH_FR_EMP_STAT_SITUATIONS.statutory_situation_id%TYPE;
1148: l_next_ovn PQH_FR_EMP_STAT_SITUATIONS.object_version_number%TYPE;

Line 1145: l_prior_act_end_date PQH_FR_EMP_STAT_SITUATIONS.actual_end_date%TYPE;

1141: l_prior_ovn PQH_FR_EMP_STAT_SITUATIONS.object_version_number%TYPE;
1142: l_prior_prov_st_date PQH_FR_EMP_STAT_SITUATIONS.provisional_start_date%TYPE;
1143: l_prior_act_st_date PQH_FR_EMP_STAT_SITUATIONS.actual_start_date%TYPE;
1144: l_prior_prov_end_date PQH_FR_EMP_STAT_SITUATIONS.provisional_start_date%TYPE;
1145: l_prior_act_end_date PQH_FR_EMP_STAT_SITUATIONS.actual_end_date%TYPE;
1146: l_next_emp_stat_sit_id PQH_FR_EMP_STAT_SITUATIONS.emp_stat_situation_id%TYPE;
1147: l_next_sit_id PQH_FR_EMP_STAT_SITUATIONS.statutory_situation_id%TYPE;
1148: l_next_ovn PQH_FR_EMP_STAT_SITUATIONS.object_version_number%TYPE;
1149: l_next_prov_end_date PQH_FR_EMP_STAT_SITUATIONS.provisional_end_date%TYPE;

Line 1146: l_next_emp_stat_sit_id PQH_FR_EMP_STAT_SITUATIONS.emp_stat_situation_id%TYPE;

1142: l_prior_prov_st_date PQH_FR_EMP_STAT_SITUATIONS.provisional_start_date%TYPE;
1143: l_prior_act_st_date PQH_FR_EMP_STAT_SITUATIONS.actual_start_date%TYPE;
1144: l_prior_prov_end_date PQH_FR_EMP_STAT_SITUATIONS.provisional_start_date%TYPE;
1145: l_prior_act_end_date PQH_FR_EMP_STAT_SITUATIONS.actual_end_date%TYPE;
1146: l_next_emp_stat_sit_id PQH_FR_EMP_STAT_SITUATIONS.emp_stat_situation_id%TYPE;
1147: l_next_sit_id PQH_FR_EMP_STAT_SITUATIONS.statutory_situation_id%TYPE;
1148: l_next_ovn PQH_FR_EMP_STAT_SITUATIONS.object_version_number%TYPE;
1149: l_next_prov_end_date PQH_FR_EMP_STAT_SITUATIONS.provisional_end_date%TYPE;
1150: l_next_act_end_date PQH_FR_EMP_STAT_SITUATIONS.actual_end_date%TYPE;

Line 1147: l_next_sit_id PQH_FR_EMP_STAT_SITUATIONS.statutory_situation_id%TYPE;

1143: l_prior_act_st_date PQH_FR_EMP_STAT_SITUATIONS.actual_start_date%TYPE;
1144: l_prior_prov_end_date PQH_FR_EMP_STAT_SITUATIONS.provisional_start_date%TYPE;
1145: l_prior_act_end_date PQH_FR_EMP_STAT_SITUATIONS.actual_end_date%TYPE;
1146: l_next_emp_stat_sit_id PQH_FR_EMP_STAT_SITUATIONS.emp_stat_situation_id%TYPE;
1147: l_next_sit_id PQH_FR_EMP_STAT_SITUATIONS.statutory_situation_id%TYPE;
1148: l_next_ovn PQH_FR_EMP_STAT_SITUATIONS.object_version_number%TYPE;
1149: l_next_prov_end_date PQH_FR_EMP_STAT_SITUATIONS.provisional_end_date%TYPE;
1150: l_next_act_end_date PQH_FR_EMP_STAT_SITUATIONS.actual_end_date%TYPE;
1151: l_iand_sit_id PQH_FR_EMP_STAT_SITUATIONS.statutory_situation_id%TYPE;

Line 1148: l_next_ovn PQH_FR_EMP_STAT_SITUATIONS.object_version_number%TYPE;

1144: l_prior_prov_end_date PQH_FR_EMP_STAT_SITUATIONS.provisional_start_date%TYPE;
1145: l_prior_act_end_date PQH_FR_EMP_STAT_SITUATIONS.actual_end_date%TYPE;
1146: l_next_emp_stat_sit_id PQH_FR_EMP_STAT_SITUATIONS.emp_stat_situation_id%TYPE;
1147: l_next_sit_id PQH_FR_EMP_STAT_SITUATIONS.statutory_situation_id%TYPE;
1148: l_next_ovn PQH_FR_EMP_STAT_SITUATIONS.object_version_number%TYPE;
1149: l_next_prov_end_date PQH_FR_EMP_STAT_SITUATIONS.provisional_end_date%TYPE;
1150: l_next_act_end_date PQH_FR_EMP_STAT_SITUATIONS.actual_end_date%TYPE;
1151: l_iand_sit_id PQH_FR_EMP_STAT_SITUATIONS.statutory_situation_id%TYPE;
1152: l_new_iand_emp_stat_sit_id PQH_FR_EMP_STAT_SITUATIONS.emp_stat_situation_id%TYPE;

Line 1149: l_next_prov_end_date PQH_FR_EMP_STAT_SITUATIONS.provisional_end_date%TYPE;

1145: l_prior_act_end_date PQH_FR_EMP_STAT_SITUATIONS.actual_end_date%TYPE;
1146: l_next_emp_stat_sit_id PQH_FR_EMP_STAT_SITUATIONS.emp_stat_situation_id%TYPE;
1147: l_next_sit_id PQH_FR_EMP_STAT_SITUATIONS.statutory_situation_id%TYPE;
1148: l_next_ovn PQH_FR_EMP_STAT_SITUATIONS.object_version_number%TYPE;
1149: l_next_prov_end_date PQH_FR_EMP_STAT_SITUATIONS.provisional_end_date%TYPE;
1150: l_next_act_end_date PQH_FR_EMP_STAT_SITUATIONS.actual_end_date%TYPE;
1151: l_iand_sit_id PQH_FR_EMP_STAT_SITUATIONS.statutory_situation_id%TYPE;
1152: l_new_iand_emp_stat_sit_id PQH_FR_EMP_STAT_SITUATIONS.emp_stat_situation_id%TYPE;
1153: l_new_iand_ovn PQH_FR_EMP_STAT_SITUATIONS.object_version_number%TYPE;

Line 1150: l_next_act_end_date PQH_FR_EMP_STAT_SITUATIONS.actual_end_date%TYPE;

1146: l_next_emp_stat_sit_id PQH_FR_EMP_STAT_SITUATIONS.emp_stat_situation_id%TYPE;
1147: l_next_sit_id PQH_FR_EMP_STAT_SITUATIONS.statutory_situation_id%TYPE;
1148: l_next_ovn PQH_FR_EMP_STAT_SITUATIONS.object_version_number%TYPE;
1149: l_next_prov_end_date PQH_FR_EMP_STAT_SITUATIONS.provisional_end_date%TYPE;
1150: l_next_act_end_date PQH_FR_EMP_STAT_SITUATIONS.actual_end_date%TYPE;
1151: l_iand_sit_id PQH_FR_EMP_STAT_SITUATIONS.statutory_situation_id%TYPE;
1152: l_new_iand_emp_stat_sit_id PQH_FR_EMP_STAT_SITUATIONS.emp_stat_situation_id%TYPE;
1153: l_new_iand_ovn PQH_FR_EMP_STAT_SITUATIONS.object_version_number%TYPE;
1154: --

Line 1151: l_iand_sit_id PQH_FR_EMP_STAT_SITUATIONS.statutory_situation_id%TYPE;

1147: l_next_sit_id PQH_FR_EMP_STAT_SITUATIONS.statutory_situation_id%TYPE;
1148: l_next_ovn PQH_FR_EMP_STAT_SITUATIONS.object_version_number%TYPE;
1149: l_next_prov_end_date PQH_FR_EMP_STAT_SITUATIONS.provisional_end_date%TYPE;
1150: l_next_act_end_date PQH_FR_EMP_STAT_SITUATIONS.actual_end_date%TYPE;
1151: l_iand_sit_id PQH_FR_EMP_STAT_SITUATIONS.statutory_situation_id%TYPE;
1152: l_new_iand_emp_stat_sit_id PQH_FR_EMP_STAT_SITUATIONS.emp_stat_situation_id%TYPE;
1153: l_new_iand_ovn PQH_FR_EMP_STAT_SITUATIONS.object_version_number%TYPE;
1154: --
1155: BEGIN

Line 1152: l_new_iand_emp_stat_sit_id PQH_FR_EMP_STAT_SITUATIONS.emp_stat_situation_id%TYPE;

1148: l_next_ovn PQH_FR_EMP_STAT_SITUATIONS.object_version_number%TYPE;
1149: l_next_prov_end_date PQH_FR_EMP_STAT_SITUATIONS.provisional_end_date%TYPE;
1150: l_next_act_end_date PQH_FR_EMP_STAT_SITUATIONS.actual_end_date%TYPE;
1151: l_iand_sit_id PQH_FR_EMP_STAT_SITUATIONS.statutory_situation_id%TYPE;
1152: l_new_iand_emp_stat_sit_id PQH_FR_EMP_STAT_SITUATIONS.emp_stat_situation_id%TYPE;
1153: l_new_iand_ovn PQH_FR_EMP_STAT_SITUATIONS.object_version_number%TYPE;
1154: --
1155: BEGIN
1156: --

Line 1153: l_new_iand_ovn PQH_FR_EMP_STAT_SITUATIONS.object_version_number%TYPE;

1149: l_next_prov_end_date PQH_FR_EMP_STAT_SITUATIONS.provisional_end_date%TYPE;
1150: l_next_act_end_date PQH_FR_EMP_STAT_SITUATIONS.actual_end_date%TYPE;
1151: l_iand_sit_id PQH_FR_EMP_STAT_SITUATIONS.statutory_situation_id%TYPE;
1152: l_new_iand_emp_stat_sit_id PQH_FR_EMP_STAT_SITUATIONS.emp_stat_situation_id%TYPE;
1153: l_new_iand_ovn PQH_FR_EMP_STAT_SITUATIONS.object_version_number%TYPE;
1154: --
1155: BEGIN
1156: --
1157: --Log entry

Line 1457: FROM pqh_fr_emp_stat_situations

1453: --
1454: --Cursor to fetch Situation as on effective date.
1455: CURSOR csr_emp_sit_dtls IS
1456: SELECT statutory_situation_id
1457: FROM pqh_fr_emp_stat_situations
1458: WHERE person_id = p_person_id
1459: AND p_effective_date BETWEEN NVL(actual_start_date,provisional_start_date)
1460: AND NVL(actual_end_date,NVL(provisional_end_date,HR_GENERAL.end_of_time));
1461: --

Line 1463: l_default_sit_id PQH_FR_EMP_STAT_SITUATIONS.statutory_situation_id%TYPE;

1459: AND p_effective_date BETWEEN NVL(actual_start_date,provisional_start_date)
1460: AND NVL(actual_end_date,NVL(provisional_end_date,HR_GENERAL.end_of_time));
1461: --
1462: --Varialbe Declarations.
1463: l_default_sit_id PQH_FR_EMP_STAT_SITUATIONS.statutory_situation_id%TYPE;
1464: l_sit_id PQH_FR_EMP_STAT_SITUATIONS.statutory_situation_id%TYPE;
1465: l_is_active VARCHAR2(10) := NULL;
1466: --
1467: BEGIN

Line 1464: l_sit_id PQH_FR_EMP_STAT_SITUATIONS.statutory_situation_id%TYPE;

1460: AND NVL(actual_end_date,NVL(provisional_end_date,HR_GENERAL.end_of_time));
1461: --
1462: --Varialbe Declarations.
1463: l_default_sit_id PQH_FR_EMP_STAT_SITUATIONS.statutory_situation_id%TYPE;
1464: l_sit_id PQH_FR_EMP_STAT_SITUATIONS.statutory_situation_id%TYPE;
1465: l_is_active VARCHAR2(10) := NULL;
1466: --
1467: BEGIN
1468: --