[Home] [Help]
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
321: NULL;
322: END IF;
323: DELETE FROM PSP_EFFORT_REPORT_DETAILS D
324: WHERE exists ( SELECT 'x'
325: FROM PSP_EFFORT_REPORTS R
326: WHERE R.effort_report_id = D.effort_report_id AND
327: R.version_num = D.version_num AND
328: template_id = t_old_template_id );
329: IF (SQL%NOTFOUND) THEN
328: template_id = t_old_template_id );
329: IF (SQL%NOTFOUND) THEN
330: NULL;
331: END IF;
332: DELETE FROM PSP_EFFORT_REPORTS
333: WHERE template_id = t_old_template_id;
334: IF (SQL%NOTFOUND) THEN
335: NULL;
336: END IF;
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
779: ORDER BY status_code;
780: ---
781: CURSOR t_get_no_csr IS
782: SELECT COUNT(*)
783: FROM psp_effort_reports
784: WHERE template_id = a_template_id;
785: ---
786: CURSOR t_errors IS
787: SELECT message
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
879: ELSE
880: t_status_code := 'N'; --- See High Level Design Doc.
881: END IF;
882: ---
883: --- Insert One row in PSP_EFFORT_REPORTS for each person selected.
884: --- Increment Version No. by 1 if for a given person and date range, there is an
885: --- existing report . Do Not Create new records , if the report type is (N)ormal
886: --- and the status is 'W'arning. */
887: ---
893: CLOSE determine_version_num;
894: ----
895: ----
896: IF t_version_number = 1 THEN
897: SELECT psp_effort_reports_s.NEXTVAL
898: INTO t_effort_report_id
899: FROM DUAL;
900: END IF;
901: ---
898: INTO t_effort_report_id
899: FROM DUAL;
900: END IF;
901: ---
902: PSP_EFFORT_REPORTS_PKG.insert_row (
903: t_rowid,
904: t_effort_report_id, --- in NUMBER,
905: t_version_number, --- in NUMBER,
906: t_PERS_rec.person_id, --- in NUMBER,
962: BEGIN
963: --- If The Status Code In Distribution Lines Is T, Then Generate Warning
964: --- Report For That Person.
965: IF NVL(t_DIST_row.status_code,'0') = 'T' THEN
966: DELETE FROM PSP_EFFORT_REPORTS
967: WHERE person_id = t_PERS_rec.person_id AND
968: template_id = a_template_id;
969: IF (SQL%NOTFOUND) THEN
970: NULL;
1025: --- Check to see whether Effort Report Already taken
1026: ---
1027: IF t_DIST_row.effort_report_id IS NOT NULL THEN
1028: ----
1029: --- Do Not use PSP_EFFORT_REPORTS_PKG.update_row Table Handler For The
1030: --- Following Update Statement , as it has a implicit COMMIT, used by
1031: --- PSPERCER Form.
1032: ---
1033: UPDATE psp_effort_reports
1029: --- Do Not use PSP_EFFORT_REPORTS_PKG.update_row Table Handler For The
1030: --- Following Update Statement , as it has a implicit COMMIT, used by
1031: --- PSPERCER Form.
1032: ---
1033: UPDATE psp_effort_reports
1034: SET prev_effort_report_id = t_DIST_row.effort_report_id
1035: WHERE effort_report_id = t_effort_report_id AND
1036: version_num = t_version_number;
1037: ---
1126: END;
1127: END LOOP;
1128: CLOSE t_DIST;
1129: ---
1130: --- Delete Header in case there were no details selected for a row in PSP_EFFORT_REPORTS
1131: ---
1132: IF p_effort_details_exists(t_effort_report_id,t_version_number) = -7 THEN
1133: DELETE FROM PSP_EFFORT_REPORTS
1134: WHERE effort_report_id = t_effort_report_id AND
1129: ---
1130: --- Delete Header in case there were no details selected for a row in PSP_EFFORT_REPORTS
1131: ---
1132: IF p_effort_details_exists(t_effort_report_id,t_version_number) = -7 THEN
1133: DELETE FROM PSP_EFFORT_REPORTS
1134: WHERE effort_report_id = t_effort_report_id AND
1135: version_num = t_version_number;
1136: IF (SQL%NOTFOUND) THEN
1137: NULL;
1250: RETURN NUMBER
1251: IS
1252: CURSOR get_report_id_csr IS
1253: SELECT effort_report_id , max(version_num)
1254: FROM psp_effort_reports
1255: WHERE template_id = a_template_id
1256: GROUP BY effort_report_id;
1257:
1258: l_user_name VARCHAR2(240); -- Bug 7004679