[Home] [Help]
2564: ----------P R O C E D U R E: UPDATE_EFFORT_REPORTS -------------------------------
2565: --
2566: --
2567: -- Purpose: This procedure is called by procedure generate_lines described below.
2568: -- The purpose is to update the table psp_effort_reports with a
2569: -- status code of 'S' for superceded for those effort reports with
2570: -- distribution lines that have beenadjusted.
2571: --
2572: ----------------------------------------------------------------------------------
2600: Commenting the following cursor for Bug fix :2252881:
2601:
2602: cursor effort_reports_c(p_per_id IN NUMBER) is
2603: select template_id
2604: from psp_effort_reports
2605: where person_id = p_per_id;
2606: ***************************************************************
2607: ** Fixed Bug 1021852
2608: ***************************************************************
2607: ** Fixed Bug 1021852
2608: ***************************************************************
2609: cursor effort_reports_c(p_per_id IN NUMBER) is
2610: select per.template_id
2611: from psp_effort_reports per
2612: where per.person_id = p_per_id
2613: and per.business_group_id = p_business_group_id
2614: and per.set_of_books_id = p_set_of_books_id
2615: and per.template_id in (select pert.template_id
2635: FROM psp_effort_report_templates pert
2636: WHERE pert.report_type ='N'
2637: AND pert.template_id IN(
2638: SELECT per.template_id
2639: FROM psp_effort_reports per
2640: WHERE per.person_id = p_per_id
2641: AND per.business_group_id = p_business_group_id
2642: AND per.set_of_books_id = p_set_of_books_id
2643: );
2675: / *04/27/99 Shu Lei Distribution Adjustment Enhanced Workflow * /
2676: / * Record the status code in previous_status_code before * /
2677: / * updating status_code to "S". * /
2678: / *************************************************************************** /
2679: UPDATE psp_effort_reports
2680: SET previous_status_code = status_code
2681: WHERE person_id = l_per_id
2682: AND template_id = l_template_id;
2683:
2680: SET previous_status_code = status_code
2681: WHERE person_id = l_per_id
2682: AND template_id = l_template_id;
2683:
2684: update psp_effort_reports
2685: set status_code = 'S'
2686: where person_id = l_per_id
2687: and template_id = l_template_id;
2688: end if;
2693: end loop; / * adj_effort_c * /
2694: end of comment for bug fix 2724110 ***/
2695:
2696: -- Introduced the following for bug fix 2724110
2697: UPDATE psp_effort_reports per
2698: SET per.previous_status_code = per.status_code,
2699: per.status_code = 'S'
2700: WHERE per.status_code <> 'S'
2701: AND per.business_group_id = p_business_group_id
2702: AND per.set_of_books_id = p_set_of_books_id
2703: AND (per.person_id, per.template_id) IN
2704: (SELECT pal.person_id, pert.template_id
2705: FROM psp_effort_report_templates pert,
2706: psp_effort_reports per2,
2707: psp_adjustment_lines pal
2708: WHERE pal.batch_name = p_batch_name
2709: AND pal.business_group_id = p_business_group_id
2710: AND pal.set_of_books_id = p_set_of_books_id
4233: undo_failed_exception EXCEPTION;
4234:
4235: l_orig_line_id psp_adjustment_lines.orig_line_id%TYPE;
4236: l_orig_source_type psp_adjustment_lines.orig_source_type%TYPE;
4237: l_report_id psp_effort_reports.effort_report_id%TYPE;
4238: -- Introduced the following variables for bug fix 2724110
4239: l_person_id NUMBER(15);
4240: l_begin_date DATE;
4241: l_end_date DATE;
4255: SELECT report.effort_report_id,
4256: report.person_id,
4257: min(begin_date),
4258: max(end_date)
4259: FROM psp_effort_reports report,
4260: psp_adjustment_lines line,
4261: psp_effort_report_templates template
4262: WHERE line.batch_name = p_batch_name
4263: AND report.person_id = line.person_id
4270:
4271: /**** Commented the following for bug fix 2724110
4272: SELECT distinct report.effort_report_id
4273: FROM psp_adjustment_lines line,
4274: psp_effort_reports report,
4275: psp_effort_report_templates template
4276: WHERE line.business_group_id = p_business_group_id
4277: and line.set_of_books_id = p_set_of_books_id
4278: and line.batch_name = p_batch_name
4338: END IF;
4339: CLOSE get_line_id_csr;
4340:
4341: /*---------------------------------------------------------------------*/
4342: /*2. Update table PSP_EFFORT_REPORTS by setting status_code back to */
4343: /* what it is before the batch is created and flush the */
4344: /* previous_status_code column. */
4345: /*---------------------------------------------------------------------*/
4346: OPEN get_template_id_csr;
4357: -- Introduced check on migration status before reverting back superceded ER
4358:
4359: IF not (l_migration_status) THEN
4360: IF (l_batch_count = 0) THEN
4361: UPDATE psp_effort_reports
4362: SET status_code = previous_status_code,
4363: previous_status_code = NULL
4364: WHERE effort_report_id = l_report_id
4365: AND previous_status_code IS NOT NULL;
4369:
4370: END IF;
4371:
4372: /*** Commented for bug fix 2724110
4373: UPDATE psp_effort_reports
4374: SET status_code = previous_status_code
4375: WHERE effort_report_id = l_report_id;
4376:
4377: UPDATE psp_effort_reports
4373: UPDATE psp_effort_reports
4374: SET status_code = previous_status_code
4375: WHERE effort_report_id = l_report_id;
4376:
4377: UPDATE psp_effort_reports
4378: SET previous_status_code = NULL
4379: WHERE effort_report_id = l_report_id;
4380: End of comment for bug fix 2724110 ***/
4381: END LOOP;