Rem drv: <create type="pkgbodys" pos="template_pkgbody.sql+"/>
Rem
Rem $Header: em_comparison_ui_pkgbody.sql 25-jun-2007.03:06:50 paachary Exp $
Rem
Rem em_comparison_ui_pkgbody.sql
Rem
Rem Copyright (c) 2006, 2007, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      em_comparison_ui_pkgbody.sql - <one-line expansion of the name>
Rem
Rem    DESCRIPTION
Rem      <short description of component this file declares/defines>
Rem
Rem    NOTES
Rem      <other useful comments, qualifications, etc.>
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    paachary    06/25/07 - Setting the Default compare option
Rem    ssukavan    03/23/07 - Modifying the API get_last_applied_targets
Rem    paachary    03/30/06 - Created
Rem

CREATE OR REPLACE PACKAGE BODY em_comparison_ui
AS
    PROCEDURE delete_job_execution_ids(p_execution_ids IN MGMT_JOB_GUID_ARRAY)
    AS
    BEGIN
        mgmt_jobs.delete_job_executions(p_execution_ids => p_execution_ids,
                                        p_commit        => 1);

    EXCEPTION
        WHEN OTHERS THEN
            NULL;
    END delete_job_execution_ids;

    FUNCTION get_execution_ids(p_template_name IN VARCHAR2 , 
                               p_target_name   IN VARCHAR2,
                               p_target_type   IN VARCHAR2,
                               p_job_owner     IN VARCHAR2,
                               p_job_type      IN VARCHAR2) 
        return MGMT_JOB_GUID_ARRAY
    AS
        p_execution_ids MGMT_JOB_GUID_ARRAY := MGMT_JOB_GUID_ARRAY();
    begin
        SELECT job_execution_id
        BULK collect into p_execution_ids
        FROM em_comparison_summary
        WHERE job_owner = p_job_owner
          AND template_name = p_template_name
          AND target_type = p_target_type
          AND target_name = p_target_name
          AND job_type = p_job_type;

        RETURN p_execution_ids;
    EXCEPTION
        WHEN OTHERS THEN
            null;
    END get_execution_ids;

    PROCEDURE delete_summary_info(p_template_name IN VARCHAR2 , 
                                  p_target_name   IN VARCHAR2,
                                  p_target_type   IN VARCHAR2,
                                  p_job_owner     IN VARCHAR2,
                                  p_job_type      IN VARCHAR2)
    IS
        p_execution_ids MGMT_JOB_GUID_ARRAY := MGMT_JOB_GUID_ARRAY();
    BEGIN
        p_execution_ids := 
                    get_execution_ids(p_template_name => p_template_name, 
                                      p_target_name   => p_target_name,
                                      p_target_type   => p_target_type,
                                      p_job_owner     => p_job_owner,
                                      p_job_type      => p_job_type);

        delete_job_execution_ids(p_execution_ids);

        DELETE em_comparison_summary
        WHERE job_owner = p_job_owner
          AND template_name = p_template_name
          AND target_type = p_target_type
          AND target_name = p_target_name
          AND job_type = p_job_type;

    exception
        when others then
            null;
     END delete_summary_info;

    PROCEDURE insert_summary_info(         
                                    p_job_id         IN VARCHAR2, 
                                    p_execution_id   IN VARCHAR2, 
                                    p_template_owner IN VARCHAR2, 
                                    p_job_owner      IN VARCHAR2, 
                                    p_template_name  IN VARCHAR2, 
                                    p_target_type    IN VARCHAR2, 
                                    p_target_name    IN VARCHAR2, 
                                    p_job_name       IN VARCHAR2, 
                                    p_job_type       IN VARCHAR2, 
                                    p_result         IN VARCHAR2, 
                                    p_area_of_diff   IN VARCHAR2,
                                    p_apply_option   IN VARCHAR2)
    IS

    BEGIN

    INSERT INTO em_comparison_summary 
        (job_id, 
        job_execution_id, 
        template_owner, 
        job_owner, 
        template_name,
        target_type, 
        target_name, 
        result, 
        area_of_diff, 
        job_name, 
        job_type,
        apply_option) 
    VALUES
        (p_job_id, 
        p_execution_id, 
        p_template_owner,
        p_job_owner,
        p_template_name,
        p_target_type,
        p_target_name,
        p_result,
        p_area_of_diff,
        p_job_name,
        p_job_type,
        p_apply_option);

    exception
        when others then
            null;
    END insert_summary_info;
    
    PROCEDURE set_comparison_summary_details(
                                                p_job_id          IN VARCHAR2, 
                                                p_execution_id    IN VARCHAR2, 
                                                p_template_owner  IN VARCHAR2, 
                                                p_job_owner       IN VARCHAR2, 
                                                p_template_name   IN VARCHAR2, 
                                                p_target_type     IN VARCHAR2, 
                                                p_target_name     IN VARCHAR2, 
                                                p_job_name        IN VARCHAR2, 
                                                p_job_type        IN VARCHAR2, 
                                                p_result          IN VARCHAR2, 
                                                p_area_of_diff    IN VARCHAR2,
                                                p_apply_option    IN VARCHAR2)
    IS
    BEGIN
        delete_summary_info(p_job_owner         => p_job_owner,
                            p_template_name     => p_template_name,
                            p_target_type       => p_target_type,
                            p_target_name       => p_target_name,
                            p_job_type          => p_job_type);

        insert_summary_info(
                            p_job_id            => p_job_id, 
                            p_execution_id      => p_execution_id,
                            p_template_owner    => p_template_owner,
                            p_job_owner         => p_job_owner,
                            p_template_name     => p_template_name,
                            p_target_type       => p_target_type,
                            p_target_name       => p_target_name,
                            p_job_name          => p_job_name,
                            p_job_type          => p_job_type,
                            p_result            => p_result,
                            p_area_of_diff      => p_area_of_diff,
                            p_apply_option      => p_apply_option);
    END set_comparison_summary_details;

    PROCEDURE get_comparison_summary_details(p_execution_id         IN VARCHAR2,
                                             p_comp_summary_cursor  OUT cursor_type,
                                             p_return_count         OUT NUMBER)
    IS

    BEGIN
        SELECT count(*)
        into p_return_count
        FROM em_comparison_summary
        WHERE job_execution_id = p_execution_id;
        IF p_return_count > 0 
        THEN
            OPEN p_comp_summary_cursor
            FOR
            SELECT 
                template_name, 
                target_type,
                target_name,
                result,            
                area_of_diff,
                template_owner,
                apply_option
            FROM em_comparison_summary
            WHERE job_execution_id = p_execution_id;
        ELSE
            OPEN p_comp_summary_cursor
            FOR
            SELECT 
                NULL AS template_name, 
                NULL AS target_type,
                NULL AS target_name,
                NULL AS result,            
                NULL AS area_of_diff,
                NULL AS template_owner,
                NULL AS apply_option
            FROM em_comparison_summary;
        END IF;
    END get_comparison_summary_details;

    PROCEDURE get_last_applied_targets(p_template_name  IN VARCHAR2,
                                       p_template_owner IN VARCHAR2,
                                       p_apply_option   OUT NUMBER,
                                       p_targets_list   OUT cursor_type)
    IS
        p_template_guid  mgmt_templates.template_guid%TYPE;
    BEGIN
        SELECT template_guid
        INTO p_template_guid
        FROM mgmt_templates
        WHERE template_name = p_template_name;
        p_apply_option := 2;

        OPEN p_targets_list
        FOR
         SELECT muodt.destination_target_guid target_guid,
               mt.target_name target_name,
               mt.target_type target_type,
               mtc.copy_type  copy_type
        FROM   mgmt_update_operations_details muodt,
               mgmt_update_operations_data muoda,
               mgmt_update_template_data_map mutdm,
               mgmt_template_copies mtc,
               mgmt_targets mt
        WHERE  muodt.operation_guid = muoda.operation_guid
           AND muoda.data_set_guid =  mutdm.data_set_guid
           AND muoda.data_set_type = 4
           AND mutdm.template_copy_guid = mtc.template_copy_guid 
           AND mt.target_guid =  muodt.destination_target_guid
           AND mtc.template_guid = p_template_guid
           AND (created_date) 
           IN
              (SELECT MAX(created_date)
              FROM mgmt_template_copies
              WHERE template_guid= p_template_guid
              AND target_guid = NO_GUID);         
    END get_last_applied_targets;

END em_comparison_ui;
/

show errors;