/* $Id$ */
/*============================================================================*
 |                 Copyright (c) 2015 Applaud Solutions UK Ltd                |
 |                             All rights reserved                            |
 *===========================================================================*/

SET VERIFY OFF
SET FEEDBACK OFF
SET SCAN OFF
WHENEVER OSERROR  EXIT FAILURE ROLLBACK
WHENEVER SQLERROR EXIT FAILURE ROLLBACK

DECLARE
  l_user_id number;

  CURSOR csr_user
  IS
    SELECT usr.user_id
    FROM   fnd_user usr
    WHERE  usr.user_name = 'XXAS';
    
  CURSOR csr_approved_goals
  IS
    SELECT *
      FROM xxas_goals_v xg
     WHERE xxas_perf_util_pkg.was_goal_visible(xg.goal_id) = 'Y' -- approved atleast once before
       AND NOT EXISTS
              (SELECT xcda.data_action_details
               FROM xxas_com_data xcd,
                    xxas_com_data_actions xcda
              WHERE xcd.data_id = xg.goal_id
                AND xcd.data_type = xxas_com_data_type_pkg.get_data_type_goal
                AND xcd.internal_data_id = xcda.internal_data_id
                AND xcda.data_action_details IS NOT NULL
                AND xcda.data_action_details.extract('/APPROVED_GOAL') IS NOT NULL );    
BEGIN

  OPEN  csr_user;
  FETCH csr_user into l_user_id;
  CLOSE csr_user;

  fnd_global.apps_initialize(l_user_id, -1, -1, 0);
  
  FOR i IN csr_approved_goals        
  LOOP
    xxas_goals_pkg.goal_approve
      (p_goal_id  => i.goal_id
      ,p_comments => 'Script to approve goals that were approved atleast once before.'
      ,p_changes  => null
      );
  END LOOP;

END;
/
COMMIT;
EXIT;
