rem    Author 	   : VIPS DESAI
rem    Filename    : ckrptrt.sql
rem    Title    	   : RESTORE XT_CK.. TABLES TO CK REPOSITORY REPORTS TABLES
rem    Date   	   : SEPTEMBER 1996
rem    Commants  : New in Designer 2000 Release 2.0
rem
rem
rem
rem   Purpose      : This script is used to copy user defined meta data held in temporary
rem		         XT_CK.... tables to CK Repository Reports Tables
rem		
rem 		         The script is invoked within the Repository Reports Interface when
rem                  loading user meta data and is called by the RAU on upgrade
rem		         or when the user re-installs the repository user data
rem
rem  XT Tables    :  XT_CK_REPORTS
rem 		    	   XT_CK_NLS_REPORTS
rem	               XT_CK_REPORT_GROUPS
rem 		    	   XT_CK_NLS_REPORT_GROUPS
rem		         XT_CK_REPORT_PARAMETERS
rem		         XT_CK_NLS_PARAMETERS
rem 		         XT_CK_REPORT_GROUP_USAGES
rem 		         XT_CK_REPORT_PARAMETER_USAGES
rem
rem  Procedures :  CKRPT_CLEANUP    - delete rows from ck_temp_el_ids
rem		    CKRPT_RESTORE   - restore user defined data in XT_.. tables to 
rem				             CK Repository Reports tables
rem
rem  Function     :  CKRPT_GET_TRANS_REF	- get the next transaction number
rem   		    CKRPT_GET_NEW_ID	- lookup to CK_TEMP_EL_IDS 
rem		    CKRPT_RPT_USR_DEF	- check if report is user defined, if shipped rem						  return existing report id
rem		    CKRPT_GRP_USR_DEF	- check if group is user defined, if shippped rem						  return existing group id
rem		    CKRPT_PRM_USR_DEF	- check if parameter is user defined, if 
rem						  return parameter id 


DECLARE

procedure CKRPT_CLEANUP (trans_seq in number) is
   /* Delete rows from ck_temp_el_ids */
begin
   delete from CK_TEMP_EL_IDS where trans_ref = trans_seq;
end CKRPT_CLEANUP;


function CKRPT_GET_TRANS_REF return number IS

trans_seq  number;

begin
   
   -- select back sequence number for transaction
    
   select CDI_TMP_RPT_SEQ.nextval
   into   trans_seq
   from dual;

   ckrpt_cleanup(trans_seq);
   return (trans_seq);

end;

function CKRPT_GET_NEW_ID  (trans_seq in number , lookupid in out number) return number is

  cursor getnewid IS
  select new_id
  from   ck_temp_el_ids
  where  trans_ref = trans_seq
  and    el_id     = lookupid;
  rtnval   number;
 begin

  if (lookupid is not null) then
     open getnewid;
     fetch getnewid into rtnval;

     if (getnewid%FOUND) then
            return rtnval;
     end if;

     close getnewid;
  end if;

  return 0;

END CKRPT_GET_NEW_ID;


function CKRPT_RPT_USR_DEF  (rpt_id in out number) return boolean is

  cursor getreports IS
  select id
  from   ci_reports
  where  id = rpt_id;
  rtnval   number;
 
  begin
    if (rpt_id is not null) then
      open getreports;
      fetch getreports into rtnval;

      if (getreports%FOUND) then
         rpt_id := rtnval;
         return FALSE;  /* report is shipped, return existing report id */
      end if;
    end if;
    close getreports;
  return TRUE;  /* report is user defined */

END CKRPT_RPT_USR_DEF;

function CKRPT_GRP_USR_DEF  (grp_id in out number) return boolean is

  cursor getgroups IS
  select id
  from   ci_rpt_groups
  where  id = grp_id;
  rtnval   number;
 
  begin
    if (grp_id is not null) then
      open getgroups;
      fetch getgroups into rtnval;

      if (getgroups%FOUND) then
         grp_id := rtnval;
         return FALSE;  /* group is shipped, return existing report id */
      end if;
    end if;
  
    close getgroups;
 
  return TRUE;  /* group is user defined */

END CKRPT_GRP_USR_DEF;

function CKRPT_PRM_USR_DEF  (prm_id in out number) return boolean is

  cursor getparams IS
  select id
  from   ci_parameters
  where  id = prm_id;
  rtnval   number;
 
  begin
    if (prm_id is not null) then
      open getparams;
      fetch getparams into rtnval;

      if (getparams%FOUND) then
         prm_id := rtnval;
         return FALSE;  /* parameter is shipped, return existing report id */
      end if;
    end if;
    close getparams;
 
  return TRUE;  /* parameter is user defined */

END CKRPT_PRM_USR_DEF;


procedure CKRPT_RESTORE is

-- Procedure to restore  user defined meta data from xt_ck tables to back to ck_.. tables
-- Cursor to get all reports

   cursor GET_REPORTS is
   select rp.RPT_ID 	    		rpt_id,
             rp.RPT_HELP_ID 	    	rpt_helpid,
             nrp.RPT_ORIENTATION 	rpt_orientation,
       	 nrp.RPT_NAME 		rpt_name,
       	 nrp.RPT_TITLE	 	rpt_title,
             nrp.RPT_PURPOSE 	    	rpt_purpose,
             nrp.RPT_NEEDAPP     	    	rpt_needapp,
             nrp.RPT_STATUS  	   	rpt_status,     
             rp.CK_USER                   	rpt_user,
             rp.CK_USER_DEFINED 	rpt_user_defined
   from   XT_CK_REPORTS rp, XT_CK_NLS_REPORTS nrp
   where rp.CK_USER_DEFINED ='Y'
   and nrp.RPT_ID = rp.RPT_ID
   and not exists ( select 1
	               from ck_nls_reports rp
		   where rp.rpt_name = nrp.RPT_NAME
		   and rp.ck_user = user); 

-- Cursor to get all parameters

   cursor GET_PARAMETERS is
   select   p.PARAM_ID		   prm_id,
	   p.PARAM_NAME               prm_name,
               p.PARAM_LOV_ID        	   prm_lov_id,
               p.PARAM_DATATYPE        prm_datatype,
              np.PARAM_PROMPT           prm_prompt,
	  np.PARAM_HINT                  prm_hint,
	  np.PARAM_DOMAIN            prm_domain,
	  np.PARAM_CASE  	   prm_casemode,
	  np.PARAM_WILD                 prm_wild,
              p.CK_USER                          prm_user,
	  p.CK_USER_DEFINED prm_user_defined
   from  XT_CK_REPORT_PARAMETERS p, XT_CK_NLS_PARAMETERS np
   where p.CK_USER_DEFINED ='Y'
   and p.PARAM_ID = np.PARAM_ID
   and not exists ( select 1
		   from ck_report_parameters pr 
		   where pr.param_name = p.PARAM_NAME
		   and pr.ck_user = user);

-- Cursor to get all report groups

   cursor GET_RPT_GROUPS is
   select g.RPG_ID   		    grp_id,
	 g.RPG_SEQUENCE         grp_sequence,        	
	 g.RPG_HELP_ID                grp_helpid,
             ng.RPG_NAME                  grp_name,
	 ng.RPG_TYPE                   grp_type,
             g.CK_USER                       grp_user,
             g.CK_USER_DEFINED   grp_user_defined
   from   XT_CK_REPORT_GROUPS g, XT_CK_NLS_REPORT_GROUPS ng
   where g.CK_USER_DEFINED ='Y'
   and ng.RPG_ID = g.RPG_ID
   and not exists ( select 1
            	   	  from ck_nls_report_groups gp
		  where gp.rpg_name = ng.RPG_NAME
		  and gp.ck_user = user);

   -- Cursor to get all report group usages
   
   cursor GET_RPT_GRP_USGS is
   select RGU_RPT_ID		rgu_rpt_id
	,RGU_RPG_ID		rgu_rpg_id
	,RGU_SEQUENCE	rgu_sequence
            ,CK_USER_DEFINED	rgu_user_defined
	,CK_USER		rgu_user
   from   XT_CK_REPORT_GROUP_USAGES
   where CK_USER_DEFINED = 'Y';


   -- Cursor to get all report parameter usages

   cursor GET_RPT_PRM_USGS is
   select RPU_RPT_ID			rpu_rpt_id
	,RPU_PARAM_ID		rpu_prm_id
	,RPU_SEQUENCE		rpu_sequence
	,RPU_MANDATORY		rpu_mand
	,RPU_LOVENFORCED		rpu_lovenforced
	,RPU_DEFAULT		rpu_dflt
	,RPU_ELETYPE		rpu_eletype
	,RPU_SDD_COLUMN		rpu_sdd_col	
	,RPU_EL_OCCUR_TYPE	rpu_el_occur_type
	,RPU_PAC_PARAM		rpu_pac_param
	,CK_USER_DEFINED		rpu_user_defined
	,CK_USER			rpu_user
   from   XT_CK_REPORT_PARAMETER_USAGES
   where CK_USER_DEFINED = 'Y';
  
      nls_lang    varchar2(3);
   nxt_rpt_seq number; 		-- holds the next report sequence number
   nxt_prm_seq number; 		-- holds the next parameter sequence number
   nxt_grp_seq number; 		-- holds the next group sequence number
   rpt_trans_ref number;                   --  current transaction number
   prm_trans_ref number ;    
   grp_trans_ref number;    
   new_rpt_id number;
   new_grp_id number;
   new_prm_id number;

begin

   new_prm_id := 0;
   new_rpt_id:= 0;
   new_grp_id:= 0;

-- Query current nls language and place in variable nls_lang
   select nls_language into nls_lang from rm_repositories;

-- Get transaction reference number
   rpt_trans_ref := ckrpt_get_trans_ref;
   prm_trans_ref := ckrpt_get_trans_ref;
   grp_trans_ref := ckrpt_get_trans_ref;

   DBMS_OUTPUT.PUT_LINE('Report trans ref ..... ' || rpt_trans_ref);
   DBMS_OUTPUT.PUT_LINE('Parameter trans ref ..... ' || prm_trans_ref);
   DBMS_OUTPUT.PUT_LINE('Group trans ref ..... ' || grp_trans_ref);

 -- For each group

 for CURR_GRP in GET_RPT_GROUPS loop

   DBMS_OUTPUT.PUT_LINE(' Adding report groups....');   

   -- select next group sequence number from dual
   select CK_RPG_ID_SEQUENCE.nextval into nxt_grp_seq from dual;

   -- insert into CK_RPT_GROUPS
  
   DBMS_OUTPUT.PUT_LINE('Inserting Group  Id ... ' ||  nxt_grp_seq);
   DBMS_OUTPUT.PUT_LINE('I             Group  Name ... ' ||  CURR_GRP.grp_name);

   insert into CK_REPORT_GROUPS
   values (  nxt_grp_seq
	   ,CURR_GRP.grp_sequence
	   ,CURR_GRP.grp_helpid
               ,user
	   ,CURR_GRP.grp_user_defined );

   -- insert into CK_NLS_REPORT_GROUPS
	
   insert into CK_NLS_REPORT_GROUPS
   values (  nls_lang
               ,nxt_grp_seq
	   ,CURR_GRP.grp_name
	   ,CURR_GRP.grp_type
	   ,CURR_GRP.grp_user_defined
	   ,user );
   
   -- insert into CK_TEMP_EL_IDS saving old id and new id 

   insert into CK_TEMP_EL_IDS ( TRANS_REF, EL_ID,  NEW_ID )
   values ( grp_trans_ref, CURR_GRP.grp_id, nxt_grp_seq );      

  commit;

 end loop; -- insert in GET_RPT_GROUPS


-- For each report

   for CURR_RPT in GET_REPORTS loop

      DBMS_OUTPUT.PUT_LINE(' Adding reports....');   

     -- select next report sequence number from dual
     select CK_RPT_ID_SEQUENCE.nextval into nxt_rpt_seq from dual;

     -- insert into CK_REPORTS

     DBMS_OUTPUT.PUT_LINE('Inserting  Report  Id ... ' ||  nxt_rpt_seq);
     DBMS_OUTPUT.PUT_LINE('              Report  Name ... ' ||  CURR_RPT.rpt_name);

     insert into CK_REPORTS
     values ( nxt_rpt_seq
	    ,CURR_RPT.rpt_helpid
	    ,user
                ,CURR_RPT.rpt_user_defined);
     commit;

    -- insert into CK_NLS_REPORTS

    insert into CK_NLS_REPORTS
    values ( nls_lang
               ,nxt_rpt_seq
 	  ,CURR_RPT.rpt_orientation
        	  ,CURR_RPT.rpt_name
	  ,CURR_RPT.rpt_title
              ,CURR_RPT.rpt_needapp
	  ,CURR_RPT.rpt_purpose	
	  ,CURR_RPT.rpt_status
	  ,CURR_RPT.rpt_user_defined
 	  ,user);
    
    -- insert into CK_TEMP_EL_IDS saving old id and new id 

   insert into CK_TEMP_EL_IDS ( TRANS_REF, EL_ID, NEW_ID )
   values ( rpt_trans_ref, CURR_RPT.rpt_id, nxt_rpt_seq );      
	
   commit;
 
end loop;


 -- For each parameter

 for CURR_PRM in GET_PARAMETERS loop
 
   DBMS_OUTPUT.PUT_LINE(' Adding parameters....');   

  -- select next parameter sequence number from dual
 select CK_PARAM_ID_SEQUENCE.nextval into nxt_prm_seq from dual;

 -- insert into CK_REPORT_PARAMETERS

 DBMS_OUTPUT.PUT_LINE('Inserting Parameter Id ... ' ||  nxt_prm_seq);
 DBMS_OUTPUT.PUT_LINE('             Parameter Name ... ' ||  CURR_PRM.prm_name);

 insert into CK_REPORT_PARAMETERS
 values (    nxt_prm_seq
   	   ,CURR_PRM.prm_name
 	   ,CURR_PRM.prm_lov_id
	   ,CURR_PRM.prm_datatype
	   ,user
	   ,CURR_PRM.prm_user_defined );
 commit;

 insert into CK_NLS_PARAMETERS
 values (    nls_lang
	   ,nxt_prm_seq
	   ,CURR_PRM.prm_prompt
	   ,CURR_PRM.prm_hint
	   ,CURR_PRM.prm_domain
	   ,CURR_PRM.prm_casemode
	   ,CURR_PRM.prm_wild
	   ,CURR_PRM.prm_user_defined
	   ,user );
  
  -- insert into CK_TEMP_EL_IDS saving old id and new id 
         
  insert into CK_TEMP_EL_IDS ( TRANS_REF, EL_ID,  NEW_ID )
  values ( prm_trans_ref, CURR_PRM.prm_id, nxt_prm_seq );      

  commit;

 end loop;  -- insert in GET_PARAMETERS				


 --- For each report group usage

for CURR_RGU in GET_RPT_GRP_USGS loop

   DBMS_OUTPUT.PUT_LINE(' Adding report group usages....');   

  --- insert into CK_REPORT_GROUP_USAGES

  -- if report is user defined
  if  (CKRPT_RPT_USR_DEF(CURR_RGU.rgu_rpt_id)) 
  then
    new_rpt_id := CKRPT_GET_NEW_ID ( rpt_trans_ref, CURR_RGU.rgu_rpt_id );
  else
    new_rpt_id := CURR_RGU.rgu_rpt_id;   
  end if;

   -- if group is user defined 
  if  (CKRPT_GRP_USR_DEF(CURR_RGU.rgu_rpt_id)) 
  then
    new_grp_id := CKRPT_GET_NEW_ID ( grp_trans_ref, CURR_RGU.rgu_rpg_id ); 
  else
    new_grp_id := CURR_RGU.rgu_rpg_id;   
  end if;

  DBMS_OUTPUT.PUT_LINE('Inserting Report Group Usage,  Group  Id ... ' || new_grp_id);
  DBMS_OUTPUT.PUT_LINE('           			      Report Id..... ' || new_rpt_id);

  if (new_grp_id != 0) then
    if (new_rpt_id != 0) then
    
      insert into CK_REPORT_GROUP_USAGES
      values ( new_rpt_id 
  	      ,new_grp_id
  	      ,CURR_RGU.rgu_sequence
  	      ,CURR_RGU.rgu_user_defined
  	      ,user);
      commit; 
    end if;
  end if;

end loop;

--- For each report parameter usage

for CURR_RPU in GET_RPT_PRM_USGS loop

    DBMS_OUTPUT.PUT_LINE(' Adding report parameter usages....');   

   -- insert into CK_REPORT_PARAMETER_USAGES

   -- if report is user defined
   if  (CKRPT_RPT_USR_DEF(CURR_RPU.rpu_rpt_id)) 
   then
     new_rpt_id := CKRPT_GET_NEW_ID ( rpt_trans_ref, CURR_RPU.rpu_rpt_id );
   else
     new_rpt_id:= CURR_RPU.rpu_rpt_id;
   end if;
   
   -- if parameter is user defined
   if  (CKRPT_PRM_USR_DEF(CURR_RPU.rpu_prm_id)) 
   then
     new_prm_id := CKRPT_GET_NEW_ID ( prm_trans_ref, CURR_RPU.rpu_prm_id );
   else
     new_prm_id:= CURR_RPU.rpu_prm_id;
   end if;

   DBMS_OUTPUT.PUT_LINE('Inserting Report Parameter Usage ,    Parameter Id..... ' || new_prm_id);
   DBMS_OUTPUT.PUT_LINE('             Report Id..... ' || new_rpt_id);
   DBMS_OUTPUT.PUT_LINE('             Parameter Id..... ' || new_prm_id);

   if (new_rpt_id != 0) then
     if (new_prm_id != 0) then

     insert into CK_REPORT_PARAMETER_USAGES
     values (new_rpt_id 
 	  ,new_prm_id
 	  ,CURR_RPU.rpu_sequence
 	  ,CURR_RPU.rpu_mand
 	  ,CURR_RPU.rpu_lovenforced
 	  ,CURR_RPU.rpu_dflt
    	  ,CURR_RPU.rpu_eletype
 	  ,CURR_RPU.rpu_sdd_col
 	  ,CURR_RPU.rpu_el_occur_type
 	  ,CURR_RPU.rpu_pac_param
 	  ,CURR_RPU.rpu_user_defined
   	  ,user);
     commit;
  
   end if;
  end if;

end loop;

-- Tidy up CK_TEMP_EL_IDS

ckrpt_cleanup (rpt_trans_ref);
ckrpt_cleanup(grp_trans_ref);
ckrpt_cleanup(prm_trans_ref);

end CKRPT_RESTORE;

begin

  DBMS_OUTPUT.PUT_LINE('Restoring from XT_CK..... tables to CK Repository Report tables ....'); 

  ckrpt_restore;

end;

/

