APPS.PQH_PSU_BUS dependencies on PQH_FR_EMP_STAT_SITUATIONS

Line 102: , pqh_fr_emp_stat_situations psu

98: --
99: cursor csr_leg_code is
100: select pbg.legislation_code
101: from per_business_groups_perf pbg
102: , pqh_fr_emp_stat_situations psu
103: , pqh_fr_stat_situations sts
104: where psu.emp_stat_situation_id = p_emp_stat_situation_id
105: and psu.statutory_situation_id = sts.statutory_situation_id
106: and pbg.business_group_id = sts.business_group_id;

Line 481: FROM pqh_fr_emp_stat_situations

477: --Cursor to fetch continous renewal duration,
478: CURSOR csr_cont_renewals(p_date DATE) IS
479: SELECT NVL(actual_start_date,provisional_start_date),
480: NVL(actual_end_date,provisional_end_date)-NVL(actual_start_date,provisional_start_date) days
481: FROM pqh_fr_emp_stat_situations
482: WHERE person_id = p_rec.person_id
483: AND statutory_situation_id = p_rec.statutory_situation_id
484: AND emp_stat_situation_id <> NVL(p_rec.emp_stat_situation_id,-1)
485: AND((NVL(renewal_flag,'N') = 'N' AND emp_stat_situation_id = p_rec.renew_stat_situation_id)

Line 592: FROM pqh_fr_emp_stat_situations

588: --Cursor to fetch continous renewal duration,
589: CURSOR csr_cont_renewals(p_date DATE) IS
590: SELECT NVL(actual_start_date,provisional_start_date),
591: (NVL(actual_end_date,provisional_end_date)-NVL(actual_start_date,provisional_start_date)+1) days
592: FROM pqh_fr_emp_stat_situations
593: WHERE person_id = p_rec.person_id
594: AND statutory_situation_id = p_rec.statutory_situation_id
595: AND emp_stat_situation_id <> NVL(p_rec.emp_stat_situation_id,-1)
596: AND((NVL(renewal_flag,'N') = 'N' AND emp_stat_situation_id = p_rec.renew_stat_situation_id)

Line 794: FROM pqh_fr_emp_stat_situations

790: p_emp_stat_situation_id NUMBER) IS
791: SELECT 'x'
792: FROM DUAL
793: WHERE EXISTS(SELECT 'x'
794: FROM pqh_fr_emp_stat_situations
795: WHERE person_id = p_person_id
796: AND emp_stat_situation_id <> NVL(p_emp_stat_situation_id,-1)
797: -- AND NVL(renew_stat_situation_id,-2) <> NVL(p_emp_stat_situation_id,-1) --commented by deenath
798: AND statutory_situation_id <>

Line 898: FROM pqh_fr_emp_stat_situations

894: --
895: --Cursor to check if Situation exists for Person. Modified by deenath added emp_stat_sit clause.
896: CURSOR csr_emp_stat_situation IS
897: SELECT 'X' x
898: FROM pqh_fr_emp_stat_situations
899: WHERE person_id = p_rec.person_id
900: AND emp_stat_situation_id <> NVL(p_rec.emp_stat_situation_id,-1)
901: AND statutory_situation_id = p_rec.statutory_situation_id;
902: --

Line 917: FROM pqh_fr_emp_stat_situations

913: --Cursor to fetch total duration for the person. Modified by deenath added emp_stat_sit clause.
914: CURSOR csr_duration_days IS
915: SELECT SUM(days) total_duration_days
916: FROM(SELECT NVL(actual_end_date,provisional_end_date)-NVL(actual_start_date,provisional_start_date) days
917: FROM pqh_fr_emp_stat_situations
918: WHERE person_id = p_rec.person_id
919: AND emp_stat_situation_id <> NVL(p_rec.emp_stat_situation_id,-1)
920: AND statutory_situation_id = p_rec.statutory_situation_id);
921: --

Line 1016: FROM pqh_fr_emp_stat_situations

1012: --
1013: --Cursor to check if Situation exists for Person. Modified by deenath added emp_stat_sit clause.
1014: CURSOR csr_emp_stat_situation IS
1015: SELECT 'Y'
1016: FROM pqh_fr_emp_stat_situations
1017: WHERE person_id = p_rec.person_id
1018: AND emp_stat_situation_id <> NVL(p_rec.emp_stat_situation_id,-1)
1019: AND statutory_situation_id = p_rec.statutory_situation_id;
1020: --

Line 1037: FROM pqh_fr_emp_stat_situations

1033: --Cursor to fetch total duration for the person. Modified by deenath added emp_stat_sit clause.
1034: CURSOR csr_duration_days IS
1035: SELECT NVL(SUM(days),0) total_duration_days
1036: FROM(SELECT (NVL(actual_end_date,provisional_end_date)-NVL(actual_start_date,provisional_start_date)+1) days
1037: FROM pqh_fr_emp_stat_situations
1038: WHERE person_id = p_rec.person_id
1039: AND emp_stat_situation_id <> NVL(p_rec.emp_stat_situation_id,-1)
1040: AND statutory_situation_id = p_rec.statutory_situation_id);
1041: --