[Home] [Help]
2555: ----------P R O C E D U R E: UPDATE_EFFORT_REPORTS -------------------------------
2556: --
2557: --
2558: -- Purpose: This procedure is called by procedure generate_lines described below.
2559: -- The purpose is to update the table psp_effort_reports with a
2560: -- status code of 'S' for superceded for those effort reports with
2561: -- distribution lines that have beenadjusted.
2562: --
2563: ----------------------------------------------------------------------------------
2591: Commenting the following cursor for Bug fix :2252881:
2592:
2593: cursor effort_reports_c(p_per_id IN NUMBER) is
2594: select template_id
2595: from psp_effort_reports
2596: where person_id = p_per_id;
2597: ***************************************************************
2598: ** Fixed Bug 1021852
2599: ***************************************************************
2598: ** Fixed Bug 1021852
2599: ***************************************************************
2600: cursor effort_reports_c(p_per_id IN NUMBER) is
2601: select per.template_id
2602: from psp_effort_reports per
2603: where per.person_id = p_per_id
2604: and per.business_group_id = p_business_group_id
2605: and per.set_of_books_id = p_set_of_books_id
2606: and per.template_id in (select pert.template_id
2626: FROM psp_effort_report_templates pert
2627: WHERE pert.report_type ='N'
2628: AND pert.template_id IN(
2629: SELECT per.template_id
2630: FROM psp_effort_reports per
2631: WHERE per.person_id = p_per_id
2632: AND per.business_group_id = p_business_group_id
2633: AND per.set_of_books_id = p_set_of_books_id
2634: );
2666: / *04/27/99 Shu Lei Distribution Adjustment Enhanced Workflow * /
2667: / * Record the status code in previous_status_code before * /
2668: / * updating status_code to "S". * /
2669: / *************************************************************************** /
2670: UPDATE psp_effort_reports
2671: SET previous_status_code = status_code
2672: WHERE person_id = l_per_id
2673: AND template_id = l_template_id;
2674:
2671: SET previous_status_code = status_code
2672: WHERE person_id = l_per_id
2673: AND template_id = l_template_id;
2674:
2675: update psp_effort_reports
2676: set status_code = 'S'
2677: where person_id = l_per_id
2678: and template_id = l_template_id;
2679: end if;
2684: end loop; / * adj_effort_c * /
2685: end of comment for bug fix 2724110 ***/
2686:
2687: -- Introduced the following for bug fix 2724110
2688: UPDATE psp_effort_reports per
2689: SET per.previous_status_code = per.status_code,
2690: per.status_code = 'S'
2691: WHERE per.status_code <> 'S'
2692: AND per.business_group_id = p_business_group_id
2693: AND per.set_of_books_id = p_set_of_books_id
2694: AND (per.person_id, per.template_id) IN
2695: (SELECT pal.person_id, pert.template_id
2696: FROM psp_effort_report_templates pert,
2697: psp_effort_reports per2,
2698: psp_adjustment_lines pal
2699: WHERE pal.batch_name = p_batch_name
2700: AND pal.business_group_id = p_business_group_id
2701: AND pal.set_of_books_id = p_set_of_books_id
4224: undo_failed_exception EXCEPTION;
4225:
4226: l_orig_line_id psp_adjustment_lines.orig_line_id%TYPE;
4227: l_orig_source_type psp_adjustment_lines.orig_source_type%TYPE;
4228: l_report_id psp_effort_reports.effort_report_id%TYPE;
4229: -- Introduced the following variables for bug fix 2724110
4230: l_person_id NUMBER(15);
4231: l_begin_date DATE;
4232: l_end_date DATE;
4246: SELECT report.effort_report_id,
4247: report.person_id,
4248: min(begin_date),
4249: max(end_date)
4250: FROM psp_effort_reports report,
4251: psp_adjustment_lines line,
4252: psp_effort_report_templates template
4253: WHERE line.batch_name = p_batch_name
4254: AND report.person_id = line.person_id
4261:
4262: /**** Commented the following for bug fix 2724110
4263: SELECT distinct report.effort_report_id
4264: FROM psp_adjustment_lines line,
4265: psp_effort_reports report,
4266: psp_effort_report_templates template
4267: WHERE line.business_group_id = p_business_group_id
4268: and line.set_of_books_id = p_set_of_books_id
4269: and line.batch_name = p_batch_name
4329: END IF;
4330: CLOSE get_line_id_csr;
4331:
4332: /*---------------------------------------------------------------------*/
4333: /*2. Update table PSP_EFFORT_REPORTS by setting status_code back to */
4334: /* what it is before the batch is created and flush the */
4335: /* previous_status_code column. */
4336: /*---------------------------------------------------------------------*/
4337: OPEN get_template_id_csr;
4348: -- Introduced check on migration status before reverting back superceded ER
4349:
4350: IF not (l_migration_status) THEN
4351: IF (l_batch_count = 0) THEN
4352: UPDATE psp_effort_reports
4353: SET status_code = previous_status_code,
4354: previous_status_code = NULL
4355: WHERE effort_report_id = l_report_id
4356: AND previous_status_code IS NOT NULL;
4360:
4361: END IF;
4362:
4363: /*** Commented for bug fix 2724110
4364: UPDATE psp_effort_reports
4365: SET status_code = previous_status_code
4366: WHERE effort_report_id = l_report_id;
4367:
4368: UPDATE psp_effort_reports
4364: UPDATE psp_effort_reports
4365: SET status_code = previous_status_code
4366: WHERE effort_report_id = l_report_id;
4367:
4368: UPDATE psp_effort_reports
4369: SET previous_status_code = NULL
4370: WHERE effort_report_id = l_report_id;
4371: End of comment for bug fix 2724110 ***/
4372: END LOOP;