[Home] [Help]
52: RETURN NUMBER IS
53: BEGIN
54: SELECT *
55: INTO g_template_row
56: FROM psp_effort_report_templates
57: WHERE template_id = a_template_id;
58: RETURN(0);
59: EXCEPTION
60: WHEN OTHERS THEN
287: IS
288: CURSOR t_old_templates(template_id2 IN NUMBER, person_id2 IN NUMBER,
289: begin_date2 IN DATE, end_date2 IN DATE) IS
290: SELECT distinct E.template_id
291: FROM PSP_EFFORT_REPORTS E,PSP_EFFORT_REPORT_TEMPLATES T
292: WHERE E.person_id = person_id2 AND
293: E.template_id = T.template_id AND
294: begin_date2 = T.begin_date AND
295: end_date2 = T.end_date AND
393: INSERT INTO PSP_EFFORT_ERRORS(template_id,person_id,message,prev_effort_report_id,
394: prev_version_num) values (template_id1,person_id1,errmsg,effort_report_id1,version_num1);
395: -- enumerated the INSERT list for bug fix 2307100
396: ---
397: UPDATE PSP_EFFORT_REPORT_TEMPLATES
398: SET error_date_time = SYSDATE
399: WHERE template_id = template_id1;
400: ---
401: IF (SQL%NOTFOUND) THEN
623: --
624: CURSOR t_all_person_id IS
625: /* replaced by new cursor for perf. fix for bug 1874615
626: SELECT distinct F.person_id,to_date(NULL) effective_start_date,to_date(NULL) effective_end_date
627: FROM psp_effort_report_templates E,
628: per_assignments_f F
629: WHERE NVL(E.people_group_id, NVL(F.people_group_id,-99)) = NVL(F.people_group_id,-99) AND
630: NVL(E.supervisor_id, NVL(F.supervisor_id,-99)) = NVL(F.supervisor_id,-99) AND
631: E.template_id = a_template_id AND
635: WHERE V.person_id = F.person_id AND
636: V.distribution_date between E.begin_date and E.end_date )
637: UNION
638: SELECT P.person_id,P.effective_start_date,P.effective_end_date
639: FROM psp_effort_report_templates P
640: WHERE template_id = a_template_id AND
641: P.person_id IS NOT NULL AND
642: exists (SELECT 'x'
643: FROM PSP_DISTRIBUTION_COMBO_V V
650: to_date(NULL) effective_end_date
651: FROM
652: per_all_assignments_f paf,
653: psp_Summary_lines psl,
654: psp_effort_report_templates pet
655: WHERE
656: (pet.template_id=a_template_id and
657: pet.person_id is null and
658: (
713: pet.person_id,
714: pet.effective_start_date,
715: pet.effective_end_date
716: FROM psp_Summary_lines psl,
717: psp_effort_report_templates pet
718: WHERE
719: pet.template_id=a_template_id and
720: pet.person_id=psl.person_id and
721: pet.person_id is not null
754: ----
755: CURSOR determine_version_num(person_id1 IN NUMBER,begin_date1 IN DATE,
756: end_date1 IN DATE,report_type1 IN VARCHAR2) IS
757: SELECT version_num + 1,effort_report_id ----NVL(MAX(version_num) + 1,1)
758: FROM psp_effort_reports P, psp_effort_report_templates T
759: WHERE P.person_id = person_id1 AND ---person_id has no effective dt in Assignments
760: P.template_id = T.template_id AND
761: ((begin_date1 BETWEEN T.begin_date AND T.end_date) OR
762: (end_date1 BETWEEN T.begin_date AND T.end_date) OR
767: CURSOR t_warning_report_exists(person_id1 IN NUMBER,begin_date1 IN DATE, end_date1 IN DATE) IS
768: SELECT decode(upper(status_code),'S','N','R', 'N', 'W'),
769: E.effort_report_id,E.version_num
770: ---INTO t_status_code,t_prev_effort_report_id,t_prev_version_num
771: FROM PSP_EFFORT_REPORTS E, PSP_EFFORT_REPORT_TEMPLATES T
772: WHERE E.person_id = person_id1 AND --- person_id has no effective dt in Assignments
773: ((begin_date1 BETWEEN T.begin_date AND T.end_date) OR
774: (end_date1 BETWEEN T.begin_date AND T.end_date) OR
775: (begin_date1 <= T.begin_date AND end_date1 >= T.end_date)) AND
840: /*
841: SELECT decode(upper(status_code),'S','N','W'),
842: E.effort_report_id,E.version_num
843: INTO t_status_code,t_prev_effort_report_id,t_prev_version_num
844: FROM PSP_EFFORT_REPORTS E, PSP_EFFORT_REPORT_TEMPLATES T
845: WHERE E.person_id = t_PERS_rec.person_id AND --- person_id has no effective dt in Assignments
846: ((g_template_row.begin_date BETWEEN T.begin_date AND T.end_date) OR
847: (g_template_row.end_date BETWEEN T.begin_date AND T.end_date) OR
848: (g_template_row.begin_date <= T.begin_date AND g_template_row.end_date >= T.end_date)) AND