rem
rem NAME
rem   ckupddom.hpb - Updates columns/attributes in a domain.
rem DESCRIPTION
rem   
rem PUBLIC
rem   
rem PRIVATE
rem   
rem NOTES
rem   
rem MODIFIED   (MM/DD/YY)
rem  stlyon     24/Oct/02 Fix B2224187
rem  HDodsworth 20/Apr/00 Updates columns/attributes where the user has access to the
rem                       container. Fixed bug 1256915 and 1010470.
rem  Spratt     04/Mar/98 Update all Columns in APP for Domain Fixed bug 612646
rem  ALoevold   10/28/96  Changed use of sdd_ views to ci_. Fixed bug 407182.

PROMPT
PROMPT Creating Stored Procedure CKOUPDDOMAIN

CREATE OR REPLACE PACKAGE BODY ckoupddomain Is

  procedure cleanup(seq IN number);

/*
 * Update All Columns within a Domain 
 * Input Folder provides context folder for domain
 * Only attempts to update columns which are not checked in
 */

procedure col (seq IN number,
	       app_id IN number default CDAPI.app_sys_ref)

IS
begin

UPDATE ci_COLUMNS scol
SET (scol.datatype, 
     scol.maximum_length, 
     scol.average_length,
     scol.default_value,
     scol.decimal_places,
     scol.soft_lov) =                    -- Bug 407182 
    (SELECT dom1.datatype, 
	        dom1.maximum_column_length, 
	        dom1.average_column_length,
	        NVL(scol.default_value, dom1.default_value),
	        dom1.column_precision,
          dom1.soft_lov                  -- Bug 407182
     FROM   SDD_FOLDER_MEMBERS fm1
     ,      ci_DOMAINS dom1
     WHERE  fm1.folder_reference = app_id
     AND    fm1.member_object  = dom1.id
     AND    dom1.id	         = scol.domain_reference)
WHERE scol.domain_reference IN (SELECT dom2.id
                                FROM   ci_DOMAINS dom2
                                ,      cdi_temp_rpt_tables t
                                WHERE  t.tmp_seq = seq
                                AND    dom2.id
				       = to_number(t.tmp_paramvalue))
AND EXISTS ( SELECT NULL 
             FROM   sdd_object_versions   ov
             WHERE  ov.irid = scol.pac_reference
             AND    ov.state <> 'I' )
AND EXISTS (SELECT NULL                                                                 -- Bug 1256915
            FROM   sdd_folder_members fm
            WHERE  (   fm.member_object = scol.table_reference
                    OR fm.member_object = scol.oracle_object_type_reference  )          -- B2224187
            AND    jr_acc_rights.user_has_access(fm.folder_reference, USER, 'UPD') = 'Y')
;


 cleanup(seq);

end;

/*
 * Update All Attributes within a Domain 
 * Input Folder provides context folder for domain
 * Only attempts to update attributes which are not checked in
 */
	   
procedure att (seq IN number,
	       app_id IN number default CDAPI.app_sys_ref)
IS
begin

UPDATE ci_ATTRIBUTES att
SET (att.FORMAT,
     att.maximum_length,
     att.average_length,
     att.DEFAULT_VALUE, 
     att.PRECISION,
     att.unit_of_measure, 
     att.null_value, 
     att.DERIVATION ) =
     (SELECT dom1.FORMAT,
	     dom1.maximum_attribute_length, 
	     dom1.average_attribute_length,
	     NVL(att.DEFAULT_VALUE, dom1.DEFAULT_VALUE), 
	     dom1.attribute_precision,
             dom1.UNIT_OF_MEASURE, 
	     dom1.NULL_VALUE, 
	     dom1.DERIVATION
      FROM   SDD_FOLDER_MEMBERS fm1
      ,      ci_DOMAINS dom1
      WHERE  fm1.folder_reference = app_id
      AND    fm1.member_object    = dom1.id
      AND    dom1.id              = att.domain_reference
      )
WHERE att.domain_reference IN 
			(SELECT dom2.id
             FROM   ci_DOMAINS dom2
             ,      cdi_temp_rpt_tables t
             WHERE  t.tmp_seq = seq
             AND    dom2.id   = to_number(t.tmp_paramvalue))
AND EXISTS ( SELECT NULL 
             FROM   sdd_object_versions   ov
             WHERE  ov.irid = att.pac_reference
             AND    ov.state <> 'I' )
AND EXISTS (SELECT NULL                  -- Bug 1010470
            FROM   sdd_folder_members fm
            WHERE  fm.member_object = att.entity_reference
            AND    jr_acc_rights.user_has_access(fm.folder_reference, USER, 'UPD') = 'Y')
;

cleanup(seq);

end;

procedure cleanup(seq IN number) is
begin
  delete from cdi_temp_rpt_tables where tmp_seq = seq;

  commit;
  
end;
end;
/