CREATE OR REPLACE PACKAGE BW_SIMPLE_GEN AS
  --------------------------------------------------------------
  --Purpose: Module generates package body based on XML       --
  --         specification of mapping stored as comment       --
  --         in package specification.                        --
  --Author:  Bob Jankovsky, copyleft 2007                     --
  --Version: 1.0 /19-OCT-2007                                 --
  --Exceptions: 20006 ... Unsuccessfully compiled module      --
  --------------------------------------------------------------
Procedure GenPackageBody(
  p_Module in VARCHAR2  -- package name
);
END BW_SIMPLE_GEN;
/
CREATE OR REPLACE PACKAGE BODY BW_SIMPLE_GEN AS
-------------------------------------------------------------
Procedure GenPackageBody(
  p_Module in VARCHAR2  -- package name
 )is
    TYPE TAE is table of Varchar2(1000) index by Varchar2(100);
    TYPE TAI is table of Varchar2(2000) index by binary_integer;
    va_expr TAE;
    c_StdSourcePrefix Varchar2(10):='SRC$$';
    c_StdTargetPrefix Varchar2(10):='TRG$$';
    v_xml             XMLTYPE;
    v_Delim1          Varchar2(30);
    v_isDBLink        Boolean:=false;
    va_expr_replace   TAE;
    va_spec_functions TAI;
    va_spec_funcnames TAI;
    Cursor c_mod_attrs is SELECT * FROM XMLTABLE
      ( '/' PASSING v_xml 
        COLUMNS
         ORD for ordinality
        ,Pattern         Varchar2(100) PATH '@Pattern'
        ,TargetOwner     Varchar2(100) PATH '@TargetOwner'
        ,TargetTable     Varchar2(100) PATH '@TargetTable'
        ,TargetLink      Varchar2(100) PATH '@TargetLink'
        ,TargetPartition Varchar2(100) PATH '@TargetPartition'
        ,TargetHint      Varchar2(100) PATH '@TargetHint'
        ,SourceHint      Varchar2(100) PATH '@SourceHint'
      ); 
    Cursor c_expr is SELECT * FROM XMLTABLE 
      ( '//EXPR' PASSING v_xml 
        COLUMNS 
         Name  Varchar2(100) PATH '@Name'
        ,Expr  Varchar2(1000)PATH 'text()'
      );
    Cursor c_constant is SELECT * FROM XMLTABLE 
      ( '//CONSTANT' PASSING v_xml 
        COLUMNS 
         Name  Varchar2(100) PATH '@Name'
        ,Type  Varchar2(100) PATH '@Type'
        ,Value Varchar2(100) PATH '@Value'
        ,Query Varchar2(2000)PATH 'text()'
      );
    Cursor c_premappings is SELECT * FROM XMLTABLE 
      ( '//PREMAPPING' PASSING v_xml 
        COLUMNS 
         Name Varchar2(500) PATH '@Comment'
        ,Code    Varchar2(2000)PATH 'text()'
      );
    Cursor c_postmappings is SELECT * FROM XMLTABLE 
      ( '//POSTMAPPING' PASSING v_xml 
        COLUMNS 
         Name Varchar2(500) PATH '@Comment'
        ,Code    Varchar2(2000)PATH 'text()'
      );
    Cursor c_execConditions is SELECT * FROM XMLTABLE 
      ( '//EXECCONDITION' PASSING v_xml 
        COLUMNS 
         Name Varchar2(500) PATH '@Comment'
        ,Code    Varchar2(2000)PATH 'text()'
      );
    Cursor c_loop is SELECT * FROM XMLTABLE ('//LOOP' PASSING v_xml);
    Cursor c_loopVar (p_xml XMLTYPE)is SELECT * FROM XMLTABLE 
      ( '/*/VAR' PASSING p_xml 
        COLUMNS 
         Name    Varchar2(500) PATH '@Name'
        ,Value   Varchar2(2000)PATH 'text()'
      );
    Cursor c_srctab is SELECT * FROM XMLTABLE('//SRCTAB' PASSING v_xml); 
    Cursor c_tab_attrs (p_xml XMLTYPE)is SELECT * FROM XMLTABLE
      ( '/' PASSING p_xml 
         COLUMNS
          ORD for ordinality
         ,Alias     Varchar2(100) PATH '@Alias'
         ,Owner     Varchar2(100) PATH '@Owner'
         ,Name      Varchar2(100) PATH '@Name'
         ,Link      Varchar2(100) PATH '@Link'
         ,Partition Varchar2(100) PATH '@Partition'
         ,JoinType  Varchar2(100) PATH '@Type'
         ,EnclFlag  Varchar2(100) PATH '@EncloseFlag'
         ,NoIndex   Varchar2(100) PATH '@NoIndex'
      ); 
    Cursor c_tab_attrs2 (p_xml XMLTYPE)is SELECT * FROM XMLTABLE
      ( '/' PASSING p_xml 
         COLUMNS
          ORD for ordinality
         ,Alias     Varchar2(100) PATH '@Alias'
         ,Owner     Varchar2(100) PATH '@Owner'
         ,Name      Varchar2(100) PATH '@Name'
         ,Link      Varchar2(100) PATH '@Link'
         ,Partition Varchar2(100) PATH '@Partition'
         ,JoinType  Varchar2(100) PATH '@Type'
         ,EnclFlag  Varchar2(100) PATH '@EncloseFlag'
         ,NoIndex   Varchar2(100) PATH '@NoIndex'
      ); 
    Cursor c_tab_on (p_xml XMLTYPE)is SELECT * FROM XMLTABLE
      ( '/*/ON' PASSING p_xml 
        COLUMNS
         ORD for ordinality
        ,COND   Varchar2(2000) PATH 'text()'
      ); 
    Cursor c_tab_where (p_xml XMLTYPE)is SELECT * FROM XMLTABLE
      ( '//WHERE' PASSING p_xml 
        COLUMNS
         ORD for ordinality
        ,COND   Varchar2(2000) PATH 'text()'
      ); 
    Cursor c_tab_dedup_part (p_xml XMLTYPE)is SELECT * FROM XMLTABLE
      ( '//DEDUP_PARTITION' PASSING p_xml 
        COLUMNS
         ORD for ordinality
        ,COND   Varchar2(2000) PATH 'text()'
      ); 
    Cursor c_tab_dedup_ord (p_xml XMLTYPE)is SELECT * FROM XMLTABLE
      ( '//DEDUP_ORDER' PASSING p_xml 
        COLUMNS
         ORD for ordinality
        ,COND   Varchar2(2000) PATH 'text()'
      ); 
    Cursor c_tab_agg (p_xml XMLTYPE)is SELECT * FROM XMLTABLE
      ( '//AGG' PASSING p_xml 
        COLUMNS
         COLUMN_NAME  Varchar2(1000) PATH '@ColumnName'
        ,AGG_FUNCTION Varchar2(100) PATH '@AggFunction'
        ,DistinctFlag Varchar2(10)  PATH '@DistinctFlag'
        ,Alias        Varchar2(100) PATH '@Alias'
      ); 
    Cursor c_tab_subjoin   (p_xml XMLTYPE)is SELECT * FROM XMLTABLE ( '//SUBJOIN' PASSING p_xml); 
    Cursor c_subjoin_col  (p_xml XMLTYPE)is SELECT * FROM XMLTABLE
      ( '//COL' PASSING p_xml 
        COLUMNS
         COLUMN_NAME  Varchar2(100) PATH '@ColumnName'
        ,COLUMN_ALIAS Varchar2(100) PATH '@ColumnAlias'
      ); 
    Cursor c_filter is SELECT * FROM XMLTABLE 
     ( '//FILTER' PASSING v_xml 
      COLUMNS
       Type  Varchar2(100) PATH '@Type' 
       ,Cond  Varchar2(2000) PATH 'text()'
      );
    Cursor c_colmap is SELECT * FROM XMLTABLE
      ( '//COLMAP' PASSING v_xml 
        COLUMNS
         ORD for ordinality
        ,SourceAlias      Varchar2(100) PATH '@SourceAlias'
        ,SourceColumn     Varchar2(100) PATH '@SourceColumn'
        ,SourceExpression Varchar2(2000)PATH 'text()'
        ,TargetColumn     Varchar2(100) PATH '@TargetColumn'
        ,MatchFlag        Varchar2(10)  PATH '@MatchFlag'
        ,UpdateFlag       Varchar2(10)  PATH '@UpdateFlag'
        ,DirectFlag       Varchar2(10)  PATH '@DirectFlag'
        ,DummyFlag        Varchar2(10)  PATH '@DummyFlag'
        ,PartFlag         Varchar2(10)  PATH '@PartFlag'
        ,ValidFromFlag    Varchar2(10)  PATH '@ValidFromFlag'
        ,ValidToFlag      Varchar2(10)  PATH '@ValidToFlag'
        ,NullableFlag     Varchar2(10)  PATH '@NullableFlag'
        ,Expr             Varchar2(100) PATH '@Expr'
        ,UpdateExpr       Varchar2(100) PATH '@UpdateExpr'
        ,InsertExpr       Varchar2(100) PATH '@InsertExpr'
      ); 
    -------------------------------------------------------------------------------------------------------
    Procedure fillXML is
      v_is_metadata Boolean:=false;
      v_is_xml      Boolean:=false;
      v_Str         Varchar2(32767);
    Begin
      for r1 in (
        Select TEXT from USER_SOURCE where NAME=p_Module and TYPE='PACKAGE' order by line
      )LOOP
        if v_is_xml then --witin XML code
          if r1.TEXT like '%*/%' then --resolve end
            v_is_xml:=false;
            r1.TEXT:=regexp_replace(r1.TEXT,'(^.*)([*][/])(.*$)','\1');
          end if; 
		  if r1.TEXT not like '#%' then
            v_Str:=concat(v_str,r1.TEXT);
		  end if; 
        elsif upper(replace(r1.TEXT,' ')) like '/*METADATA%' then --find tag METADATA:
          v_is_metadata:=true;
        elsif v_is_metadata and upper(replace(r1.TEXT,' ')) like '<?XML%?>%' then --resolve start of XML
          v_is_xml:=true;
          v_Str:=r1.TEXT; 
        end if;
      End LOOP;
      v_xml:=XMLTYPE.CREATEXML(v_Str);
      for r1 in c_expr LOOP
        va_expr(r1.Name):=r1.Expr;    
      end LOOP;
    End fillXML;
    -------------------------------------------------------------------------------------------------------
    Function translateDBLink(p Varchar2) return Varchar2 is
      v_oldL Varchar2(100):=regexp_replace(regexp_substr(p,'@\w+(\W|$)'),'\W','',1,0);
      v_newL Varchar2(100);
    Begin
       if v_oldL is not null then
          v_isDBLink:=true; 
       end if;
       v_newL:= v_oldL; --possible translation 
       return regexp_replace(p,'(@'||v_oldL||')(\W|$)','@'||v_newL||'\2');
    End;
    -------------------------------------------------------------------------------------------------------
    Function getTableQual(p_name Varchar2,p_owner Varchar2:=null,p_link Varchar2:=null,p_partition Varchar2:=null) return Varchar2 is
       v_partition_suffix Varchar2(100):=case when p_partition is null then '' else ' PARTITION('||p_partition||')' end;
    Begin
       return translateDBLink(rtrim(ltrim(p_owner||'.'||p_name,'.')||'@'||p_link,'@')||v_partition_suffix);
    End getTableQual;
    -------------------------------------------------------------------------------------------------------
    Function dedupList(p_list Varchar2) return Varchar2 is
       v_word Varchar2(30);
       i pls_integer:=1;
       v_res Varchar2(8000):=p_list;
       v_res_old Varchar2(8000);
    Begin
      LOOP 
         v_word:=regexp_replace(regexp_substr(v_res,'(^|\W)\w+($|\W)',1,i),'\W');
         exit when v_word is null;
		 v_res_old:=v_res;
         v_res:=regexp_replace(v_res,'(^|\W+)('||v_word||')(\W+'||v_word||')($|\W)','\1\2\4');
         v_res:=regexp_replace(v_res,'(^|[[:punct:]])(\W*)('||v_word||')($|\W)','\4',1,2);
		 if v_res=v_res_old then
           i:=i+1;
		 end if;
      end LOOP;
      return v_res;
    End dedupList;
    -------------------------------------------------------------------------------------------------------
    Function getDedupAttrList(P_alias Varchar2, P_srctabXml XMLTYPE) return Varchar2 is
       v_res Varchar2(8000):='';
       v_delim Varchar2(20):='';
      Function getList (p Varchar2) return Varchar2 is
        v_str Varchar2(2000):=p;
      Begin
	    for i in 1..2 LOOP
          v_str:=regexp_replace(v_str,'(\W|^)('||p_alias||'[.])(\w+)(\W|$)','\1~\3;\4',1,0,'i');
		end LOOP;
        if v_str!=p then
          v_str:=regexp_replace(v_str,';[^~]*~',', ',1,0);   
          v_str:=regexp_replace(v_str,'^[^~]*~','',1,0);   
          v_str:=regexp_replace(v_str,';[^;]*$','',1,0);
          v_str:=concat(v_delim,v_str);
          v_delim:=', ';
        else
          v_str:='';
        end if;
        return v_str;
      End getList;
    Begin
      for r1 in c_colmap LOOP
        if r1.SourceAlias=P_ALIAS and r1.SourceColumn is not null then
          v_res:=concat(v_res,v_delim||r1.SourceColumn);
          v_delim:=', '; 
        elsif r1.SourceExpression is not null then
          v_res:=concat(v_res,getList(r1.SourceExpression));
        end if;
      End LOOP;
      for r1 in (SELECT * FROM XMLTABLE ( '/*/*/ON' PASSING v_Xml COLUMNS Cond  Varchar2(100) PATH 'text()'))LOOP
        v_res:=concat(v_res,getList(r1.cond));
      end LOOP;
      for r1 in c_filter LOOP
        v_res:=concat(v_res,getList(r1.cond));
      end LOOP;
      return deduplist(v_res);
    End getDedupAttrList;
    -------------------------------------------------------------------------------------------------------
    Function getSourceExpression(r1 c_colmap%rowtype) return Varchar2 is
      v_express Varchar2(2000);
    Begin
      if r1.SourceAlias is not null and r1.SourceColumn is not null then
         v_express:=r1.SourceAlias||'.'||r1.SourceColumn;
      else
         v_express:=translateDBLink(r1.SourceExpression);
      end if;
      if r1.Expr is not null then
        v_express:=replace(va_expr(r1.Expr),':1',v_express);
      end if;
      return v_express; 
    End getSourceExpression;
    -------------------------------------------------------------------------------------------------------
    Function getGeneratedSourceHints return Varchar2 is
      v_express Varchar2(8000):='';
    Begin
      for r1 in c_srctab LOOP
        for r2 in c_tab_attrs(r1.COLUMN_VALUE) LOOP
		   if NVL(r2.NoIndex,'N')='Y' then
		     v_express:=concat(v_express,'NO_INDEX('||r2.Alias||') ');
		   end if;
		end LOOP;--r2
	  end LOOP;--r1
      return v_express; 
    End getGeneratedSourceHints;
    -------------------------------------------------------------------------------------------------------
    Function isEnclosed(p_SRCTAB XMLTYPE, p_mask Varchar2:='%' ) return boolean  is
    Begin
        for r2 in c_tab_attrs2(p_SRCTAB) LOOP
          if (nvl(r2.EnclFlag,'N')='Y') and  ('ENCL' like p_mask) then
            return true;
          end if;
          for r3 in (SELECT * FROM XMLTABLE( '//AGG | //DEDUP_PARTITION | //DEDUP_ORDER | //SUBJOIN' PASSING p_SRCTAB)) LOOP 
             if r3.COLUMN_VALUE.getRootElement() like p_mask then
               return true;
             end if;  
		  end LOOP;--r3
		end LOOP;--r2
       return false; 
    End isEnclosed;
    -------------------------------------------------------------------------------------------------------
    -- STATEMENT PARTS PART
    -------------------------------------------------------------------------------------------------------
    Procedure addSelectPart (P_Source_hint Varchar2:=null)is
      v_delim          Varchar2(30):=' ';
      v_Source_hint varchar2(8000):=P_Source_hint;
    Begin
       for r1 in c_srctab LOOP
         if isEnclosed(r1.COLUMN_VALUE) then
           for r2 in c_tab_attrs2(r1.COLUMN_VALUE) LOOP
              v_Source_hint:=regexp_replace(v_Source_hint,'(\W)('||r2.alias||')(\W)','\1@'||r2.alias||' '||r2.alias||'\3');   
           end LOOP; --r2
         end if;
       end LOOP; --r1
       BW_BUFFER.AddLN('SELECT'||replace(' /*+'||v_Source_hint||'*/',' /*+*/'));
       BW_BUFFER.IND(3);
       for r1 in c_COLMAP LOOP
		   if NVL(r1.DirectFlag,'N')!='Y'  then
             BW_BUFFER.AddLN(v_delim||getSourceExpression(r1)||' as '||r1.TargetColumn);
             v_delim:=',';
		   end if;
       end LOOP;
       BW_BUFFER.IND(-3);
    End addSelectPart;
    -------------------------------------------------------------------------------------------------------
    Procedure addFromPart is
      v_Delim1     Varchar2(30);
      v_AnyEncl    boolean;
      v_Dedup      boolean;
      v_Agg        boolean;
      v_Encl       boolean;
      v_Subjoin    boolean;
      v_Dedup_part Varchar2(4000);
      v_Dedup_ord  Varchar2(4000);
	  v_AttrList   Varchar2(8000);
	  v_GrpList    Varchar2(8000);
	  v_Hint       Varchar2(2000):=null;
      Procedure QualifyList (P_list IN OUT Varchar2,P_alias Varchar2) is
	  Begin
         for i in 1..2 LOOP
	       P_List:=regexp_replace(P_List,'(^|[,(]|distinct)(\s*)(\w+)(\s*)([,)]|\s+[Aa][Ss]|$)','\1\2'||P_ALIAS||'.\3\4\5');
		 end LOOP;
	  End QualifyList;
    Begin
      for r1 in c_srctab LOOP
        v_AnyEncl  :=isEnclosed(r1.COLUMN_VALUE);
        v_Dedup    :=false;
        v_Agg      :=isEnclosed(r1.COLUMN_VALUE,'AGG');
        v_Encl     :=isEnclosed(r1.COLUMN_VALUE,'ENCL');
        v_Subjoin  :=isEnclosed(r1.COLUMN_VALUE,'SUBJOIN');
        v_Dedup_part:='';
        v_Dedup_ord :='';
        for r2 in c_tab_attrs(r1.COLUMN_VALUE) LOOP
          --BW_BUFFER.AddLN('--Source table : '||r2.Name);
          v_Encl:=isEnclosed(r1.COLUMN_VALUE,'ENCL');
          for r3 in c_tab_dedup_part(r1.COLUMN_VALUE) LOOP
            v_dedup:=true;
            v_dedup_part:='partition by '||r3.cond;   
          end LOOP;--r3 --dedup partition
          for r3 in c_tab_dedup_ord(r1.COLUMN_VALUE) LOOP
            v_dedup:=true;
            v_dedup_ord:='order by '||r3.cond;   
          end LOOP;--r3 --dedup order
          BW_BUFFER.AddCH(r2.JoinType||' ');
		  v_HINT:=null;
          if v_anyEncl then
			v_HINT:=regexp_replace(NVL(v_HINT,'/*+*/'),'[+]','+ QB_NAME('||r2.ALIAS||') ');
		    if NVL(r2.NoIndex,'N')='Y' then
			  v_HINT:=regexp_replace(NVL(v_HINT,'/*+*/'),'[+]','+ NO_INDEX('||r2.ALIAS||') ');
			end if;  
            BW_BUFFER.AddLN('-- '||getTableQual(r2.Name,r2.Owner,r2.Link,r2.Partition)||' '||r2.Alias||' ... '||case when v_dedup then 'deduplication' when v_agg then 'aggregation' when v_subjoin then 'subjoin' else 'enclosed' end);
            BW_BUFFER.IND(2);
			v_AttrList:=getDedupAttrList(r2.Alias,r1.COLUMN_VALUE);
			------------------------------------------------
			--aggregations
			if v_agg then
			  v_GrpList:=v_AttrList;
	          for r3 in c_tab_agg(r1.COLUMN_VALUE) LOOP
  	              v_AttrList:=regexp_replace(v_AttrList,'(\W|^)('||COALESCE(r3.ALIAS,r3.COLUMN_NAME,'#')||')(\W|$)','\1'||r3.AGG_FUNCTION||'('||case when r3.DistinctFlag='Y' then 'distinct ' else '' end||r3.COLUMN_NAME||') as '||NVL(r3.ALIAS,r3.COLUMN_NAME)||'\3');
	              v_GrpList:=regexp_replace(v_GrpList,'(\W|^)('||COALESCE(r3.ALIAS,r3.COLUMN_NAME,'#')||')(\W|$)','\3');
	          end LOOP;--r3 --aggregation
			  v_GrpList:=regexp_replace(v_GrpList,'(^)(\W*)(\w)','\1\3');
			  v_GrpList:=regexp_replace(v_GrpList,'(\w)(\W*)($)','\1\3');
			end if;
			------------------------------------------------
			--subjoins
			if v_subjoin then
	            for r3 in c_tab_subjoin(r1.COLUMN_VALUE) LOOP
	              for r4 in c_tab_attrs2(r3.COLUMN_VALUE) LOOP
				    for r5 in c_subjoin_col(r3.COLUMN_VALUE) LOOP
   	                    v_AttrList:=regexp_replace(v_AttrList,'(\W|^)([aA][sS] )('||r5.COLUMN_ALIAS||')(\W|$)','\1\2\3xxxxxxxx\4');
 	                    v_AttrList:=regexp_replace(v_AttrList,'([^([:alnum:]]|^)('||r5.COLUMN_ALIAS||')(\W|$)','\1'||r4.ALIAS||'.'||r5.COLUMN_NAME||' as '||r5.COLUMN_ALIAS||'xxxxxxxx\3');
 	                    v_AttrList:=regexp_replace(v_AttrList,'([(])('||r5.COLUMN_ALIAS||')(\W|$)','\1'||r4.ALIAS||'.'||r5.COLUMN_NAME||'\3');
   	                    v_AttrList:=regexp_replace(v_AttrList,'(\W|^)([aA][sS] '||r5.COLUMN_ALIAS||')(xxxxxxxx)(\W|$)','\1\2\4');
 	                    v_GrpList:=regexp_replace(v_GrpList,'([^([:alnum:]]|^)('||r5.COLUMN_ALIAS||')(\W|$)','\1'||r4.ALIAS||'.'||r5.COLUMN_NAME||'\3');
					end LOOP;--r5
				    if NVL(r4.NoIndex,'N')='Y' then
					  v_HINT:=regexp_replace(NVL(v_HINT,'/*+*/'),'[+]','+ NO_INDEX('||r4.ALIAS||') ');
					end if;  
			      end LOOP;--r4
		        end LOOP;--r3 --subjoin process
				QualifyList(v_AttrList,r2.Alias);
				QualifyList(v_GrpList,r2.Alias);
			end if;
			------------------------------------------------
            BW_BUFFER.AddLN('(Select '||v_Hint||v_AttrList);
            BW_BUFFER.IND(2);
			if v_dedup then
              BW_BUFFER.AddLN(', row_number()over('||v_dedup_part||' '||v_dedup_ord||') as rn$');
			end if;
            BW_BUFFER.AddLN('From '||getTableQual(r2.Name,r2.Owner,r2.Link,r2.Partition)||' '||r2.alias);
			------------------------------------------------
			--subjoins
			if v_subjoin then
	            for r3 in c_tab_subjoin(r1.COLUMN_VALUE) LOOP
	              for r4 in c_tab_attrs2(r3.COLUMN_VALUE) LOOP
	                BW_BUFFER.AddLN(r4.JoinType||' '||getTableQual(r4.Name,r4.Owner,r4.Link,r4.Partition)||' '||r4.alias);
			        BW_BUFFER.IND(2);
		            v_Delim1:='ON ';   
	                for r5 in c_tab_on(r3.COLUMN_VALUE) LOOP
			          BW_BUFFER.AddLN(v_Delim1||'('||r5.COND||')');   
			          v_Delim1:='AND ';   
				    end LOOP;--r5  
			        BW_BUFFER.IND(-2);
			      end LOOP;--r4
		        end LOOP;--r3 --subjoin process
			end if;
			------------------------------------------------
            v_Delim1:='Where ';   
            for r3 in c_tab_where(r1.COLUMN_VALUE) LOOP
              BW_BUFFER.AddLN(v_Delim1||'('||r3.COND||')');   
              v_Delim1:='and ';   
            end LOOP;--r3
			if v_agg then
              BW_BUFFER.AddLN('Group by '||v_GrpList);   
			end if;
            BW_BUFFER.IND(-2);
            BW_BUFFER.AddLN(') '||r2.alias);
            BW_BUFFER.IND(-2);
          else
            BW_BUFFER.AddLN(getTableQual(r2.Name,r2.Owner,r2.Link,r2.Partition)||' '||r2.Alias);
          end if;
          BW_BUFFER.IND(2);
          v_Delim1:='ON ';   
          for r3 in c_tab_on(r1.COLUMN_VALUE) LOOP
            BW_BUFFER.AddLN(v_Delim1||'('||r3.COND||')');   
            v_Delim1:='AND ';   
          end LOOP;--r3
          if upper(r2.JoinType) not in ('FROM') then
            if v_dedup then
              BW_BUFFER.AddLN(v_Delim1||'('||r2.Alias||'.rn$=1)');   
              v_Delim1:='AND ';   
            end if;
            if (not v_anyEncl)  then 
              for r3 in c_tab_where(r1.COLUMN_VALUE) LOOP
                BW_BUFFER.AddLN(v_Delim1||'('||r3.COND||')');   
                v_Delim1:='AND ';   
              end LOOP;--r3
            end if;
          end if;
          BW_BUFFER.IND(-2);
        end LOOP;--r2
      End LOOP;--r1
    End addFromPart;
    -------------------------------------------------------------------------------------------------------
    Procedure addFilterPart is
      v_delim Varchar2(30):='WHERE ';
      v_encl boolean:=false;
    Begin
      for r1 in c_filter LOOP
        if NVL(r1.type,'SOURCE')='SOURCE' then 
          BW_BUFFER.AddLN(v_delim||r1.cond);
          v_delim:='AND '; 
        end if;
      end LOOP;
      for r1 in c_srctab LOOP
        for r2 in c_tab_attrs(r1.COLUMN_VALUE) LOOP
          if upper(r2.JoinType) in('FROM') then 
            if not isEnclosed(r1.COLUMN_VALUE) then
              for r3 in c_tab_where(r1.COLUMN_VALUE) LOOP
                BW_BUFFER.AddLN(v_delim||r3.cond);
                v_delim:='AND '; 
              end LOOP;--r3 --dedup order
            elsif isEnclosed(r1.COLUMN_VALUE,'DEDUP%') then 
                BW_BUFFER.AddLN(v_delim||r2.Alias||'.rn$=1');
                v_delim:='AND '; 
            end if;
          end if;
        end LOOP;
      end LOOP;
    End addFilterPart;
    -------------------------------------------------------------------------------------------------------
    Procedure addColumnList(P_Prefix Varchar2,p_use_direct Boolean:=false,p_incl_direct Boolean:=true) is
      v_delim Varchar2(30):=' ';
    Begin
      for r1 in c_COLMAP LOOP
        if NVL(r1.DummyFlag,'N')!='Y' then
          if p_use_direct and r1.InsertExpr is not null then
             BW_BUFFER.AddLN(v_delim||replace(va_expr(r1.InsertExpr),':1',P_Prefix||'.'||r1.TargetColumn));
          elsif p_use_direct and r1.DirectFlag='Y' then
            if va_expr_replace.exists(r1.TargetColumn) then
              BW_BUFFER.AddLN(v_delim||va_expr_replace(r1.TargetColumn));
            else
              BW_BUFFER.AddLN(v_delim||getSourceExpression(r1));
            end if;
            v_delim:=','; 
          elsif p_incl_direct or NVL(r1.DirectFlag,'N')='N' then
            BW_BUFFER.AddLN(v_delim||P_Prefix||'.'||r1.TargetColumn);
            v_delim:=','; 
          end if;
        end if;
      end LOOP;
    End addColumnList;
    -------------------------------------------------------------------------------------------------------
    Procedure addSetList(P_AllDirect boolean:=false) is
      v_delim Varchar2(30):=' ';
    Begin
       for r1 in c_COLMAP LOOP
         if NVL(r1.UpdateFlag,'Y')!='N' and NVL(r1.MatchFlag,'N')!='Y' and NVL(r1.DummyFlag,'N')!='Y' 
		   and getSourceExpression(r1) is not null then
           BW_BUFFER.AddCH(v_delim||c_StdTargetPrefix||'.'||r1.TargetColumn||'=');
           if r1.UpdateExpr is not null then
             BW_BUFFER.AddLN(replace(va_expr(r1.UpdateExpr),':1',c_StdSourcePrefix||'.'||r1.TargetColumn));
           elsif NVL(r1.DirectFlag,'N')='Y' or P_AllDirect then 
             BW_BUFFER.AddLN(getSourceExpression(r1));
           else
             BW_BUFFER.AddLN(c_StdSourcePrefix||'.'||r1.TargetColumn);
           end if;
           v_delim:=',';
        end if; 
      end LOOP;
    End addSetList;
    -------------------------------------------------------------------------------------------------------
    Procedure addOnList is
      v_delim Varchar2(30):='';
    Begin
      for r1 in c_COLMAP LOOP
        if r1.MatchFlag='Y' then
           if NVL(r1.DirectFlag,'N')='N' then
             BW_BUFFER.AddLN(v_delim||c_StdTargetPrefix||'.'||r1.TargetColumn||'='||c_StdSourcePrefix||'.'||r1.TargetColumn);
           else
             BW_BUFFER.AddLN(v_delim||c_StdTargetPrefix||'.'||r1.TargetColumn||'='||r1.SourceExpression);
           end if;  
           v_delim:='AND ';
        end if; 
      end LOOP;
      for r1 in c_filter LOOP
        if NVL(r1.type,'SOURCE') in ('TARGET','SOURCE_OUTER','ON') then 
          BW_BUFFER.AddLN(v_delim||r1.cond);
          v_delim:='AND '; 
        end if;
      end LOOP;
    End addOnList;
    -------------------------------------------------------------------------------------------------------
    Procedure addInsertWhere is
      v_delim Varchar2(30):='WHERE ';
    Begin
      for r1 in c_filter LOOP
        if NVL(r1.type,'SOURCE') in ('INSERT','SOURCE_OUTER') then 
          BW_BUFFER.AddLN(v_delim||r1.cond);
          v_delim:='AND '; 
        end if;
      end LOOP;
    End addInsertWhere;
    -------------------------------------------------------------------------------------------------------
    Procedure addUpdateWhere (p_diff boolean:=false,p_AllDirect boolean:=false) is
      v_delim Varchar2(30):='WHERE ';
    Begin
      if p_diff then
        v_delim:=v_delim||'('; 
        for r1 in c_COLMAP LOOP
          if nvl(r1.updateFlag,'Y')!='N' and nvl(r1.dummyFlag,'N')!='Y' and NVL(r1.MatchFlag,'N')!='Y' and NVL(r1.DirectFlag,'N')!='Y' then
            if not p_AllDirect then
              BW_BUFFER.AddLN(v_delim||c_StdTargetPrefix||'.'||r1.TargetColumn||'!='||c_StdSourcePrefix||'.'||r1.TargetColumn);
            else
              BW_BUFFER.AddLN(v_delim||c_StdTargetPrefix||'.'||r1.TargetColumn||'!='||getSourceExpression(r1));
            end if;
            v_delim:='OR ';
          end if; 
        end LOOP;
        Case 
          when v_delim='OR ' then
            BW_BUFFER.AddLN(')--/differential part');
            v_delim:='AND ';
          when v_delim like '%(' then
            v_delim:=rtrim(v_delim,'(');
          else null;
        end Case;
      end if;
      for r1 in c_filter LOOP
        if NVL(r1.type,'SOURCE') in ('UPDATE') then 
          BW_BUFFER.AddLN(v_delim||r1.cond);
          v_delim:='AND '; 
        end if;
      end LOOP;
    End addUpdateWhere;
    -------------------------------------------------------------------------------------------------------
    Procedure addLinkDummyMergeInsert(r1 c_mod_attrs%rowtype ) is
      v_delim Varchar2(2):=' ';
    Begin
       if v_isDBLink then 
         BW_BUFFER.AddLN('WHEN NOT MATCHED THEN INSERT');
         BW_BUFFER.IND(2);
         BW_BUFFER.AddLN('(');
         BW_BUFFER.IND(2);
         for r2 in c_COLMAP LOOP
            if NVL(r2.MatchFlag,'N')='Y' then
              BW_BUFFER.AddLN(v_delim||c_StdTargetPrefix||'.'||r2.TargetColumn);
              v_delim:=','; 
            end if;
         end LOOP;
         BW_BUFFER.IND(-1);
         BW_BUFFER.AddLN(')VALUES(');
         BW_BUFFER.IND(1);
         v_delim:=' '; 
         for r2 in c_COLMAP LOOP
            if NVL(r2.MatchFlag,'N')='Y' then
              BW_BUFFER.AddLN(v_delim||c_StdSourcePrefix||'.'||r2.TargetColumn);
              v_delim:=','; 
            end if;
         end LOOP;
         BW_BUFFER.IND(-2);
         BW_BUFFER.AddLN(')');
         BW_BUFFER.AddLN('WHERE 1=0');
         BW_BUFFER.IND(-2);
       end if;
    End addLinkDummyMergeInsert;
    -------------------------------------------------------------------------------------------------------
    -- Patterns Part
    -------------------------------------------------------------------------------------------------------
    Procedure addMergePattern( r1 c_mod_attrs%rowtype ) is
    Begin
         BW_BUFFER.AddLN('MERGE'||replace(' /*+'||r1.TargetHint||'*/',' /*+*/')||' INTO '
           ||getTableQual(r1.TargetTable,r1.TargetOwner,r1.TargetLink,r1.TargetPartition)||' '||c_StdTargetPrefix);
	     BW_BUFFER.AddLN('USING(');
	     BW_BUFFER.IND(2);
	     AddSelectPart(getGeneratedSourceHints||r1.SourceHint);
	     AddFromPart;
	     AddFilterPart;
	     BW_BUFFER.IND(-2);
	     BW_BUFFER.AddLN(') '||c_StdSourcePrefix);
	     BW_BUFFER.AddLN('ON(');
	     BW_BUFFER.IND(2);
	     AddOnList;
	     BW_BUFFER.IND(-2);
	     BW_BUFFER.AddLN(')');
		 if r1.pattern in ('MERGE','DIFF_MERGE','UPDATE','DIFF_UPDATE') then
		     BW_BUFFER.AddLN('WHEN MATCHED THEN UPDATE SET');
		     BW_BUFFER.IND(2);
		     AddSetList;
		     addUpdateWhere(r1.pattern in ('DIFF_MERGE','DIFF_UPDATE'));
		     BW_BUFFER.IND(-2);
         end if;
		 if r1.pattern in ('MERGE','DIFF_MERGE','DIFF_INSERT') then   
             BW_BUFFER.AddLN('WHEN NOT MATCHED THEN INSERT');
		     BW_BUFFER.IND(2);
		     BW_BUFFER.AddLN('(');
		     BW_BUFFER.IND(2);
		     AddColumnList(c_StdTargetPrefix,false);
		     BW_BUFFER.IND(-1);
		     BW_BUFFER.AddLN(')VALUES(');
		     BW_BUFFER.IND(1);
		     AddColumnList(c_StdSourcePrefix,true);
		     BW_BUFFER.IND(-2);
		     BW_BUFFER.AddLN(')');
	         addInsertWhere;
		     BW_BUFFER.IND(-2);
		 end if;
         If r1.pattern in ('UPDATE','DIFF_UPDATE')  then
           addLinkDummyMergeInsert(r1);
		 end if;
	     BW_BUFFER.AddLN(';');
	     BW_BUFFER.AddLN('v_processed_rows:=v_processed_rows+SQL%ROWCOUNT;');
    End addMergePattern;
    -------------------------------------------------------------------------------------------------------
    Procedure addInsertPattern(r1 c_mod_attrs%rowtype) is
      v_delim Varchar2(30):='WHERE ';
    Begin
         BW_BUFFER.AddLN('INSERT'||replace(' /*+'||r1.TargetHint||'*/',' /*+*/')||' INTO '
           ||getTableQual(r1.TargetTable,r1.TargetOwner,r1.TargetLink,r1.TargetPartition)||' '||c_StdTargetPrefix);
	     BW_BUFFER.IND(2);
	     BW_BUFFER.AddLN('(');
	     BW_BUFFER.IND(2);
	     AddColumnList(c_StdTargetPrefix,false);
	     BW_BUFFER.IND(-1);
	     BW_BUFFER.AddLN(')');
	     BW_BUFFER.AddLN('Select');
	     BW_BUFFER.IND(1);
		 AddColumnList(c_StdSourcePrefix,true);
	     BW_BUFFER.IND(-1);
		 BW_BUFFER.AddLN('from(');
	     BW_BUFFER.IND(2);
	     AddSelectPart(getGeneratedSourceHints||r1.SourceHint);
	     AddFromPart;
	     AddFilterPart;
	         if r1.pattern in ('DIFF_INSERT(MINUS)') then
 	       BW_BUFFER.AddLN('MINUS');
	       BW_BUFFER.IND(2);
 	       BW_BUFFER.AddLN('SELECT');
		   AddColumnList(c_StdTargetPrefix,false,false);
 	       BW_BUFFER.AddLN('FROM '
            ||getTableQual(r1.TargetTable,r1.TargetOwner,r1.TargetLink,r1.TargetPartition)||' '||c_StdTargetPrefix);
           for r1 in c_filter LOOP
             if NVL(r1.type,'SOURCE')='TARGET' then 
               BW_BUFFER.AddLN(v_delim||r1.cond);
               v_delim:='AND '; 
             end if;
           end LOOP;
	       BW_BUFFER.IND(-2);
         end if;
         BW_BUFFER.IND(-2);
	     BW_BUFFER.AddLN(') '||c_StdSourcePrefix);
		 addInsertWhere;
         BW_BUFFER.IND(-3);
	     BW_BUFFER.AddLN(';');
	     BW_BUFFER.AddLN('v_processed_rows:=v_processed_rows+SQL%ROWCOUNT;');
    End addInsertPattern;
    -------------------------------------------------------------------------------------------------------
    Procedure addSelfUpdatePattern( r1 c_mod_attrs%rowtype ) is
    Begin
         BW_BUFFER.AddLN('UPDATE'||replace(' /*+'||r1.TargetHint||'*/',' /*+*/')||' '
           ||getTableQual(r1.TargetTable,r1.TargetOwner,r1.TargetLink,r1.TargetPartition)||' '||c_StdTargetPrefix);
         BW_BUFFER.AddLN('SET');
         BW_BUFFER.IND(2);
         AddSetList(true);
         addUpdateWhere(true,true);
         BW_BUFFER.IND(-2);
	     BW_BUFFER.AddLN(';');
	     BW_BUFFER.AddLN('v_processed_rows:=v_processed_rows+SQL%ROWCOUNT;');
    End addSelfUpdatePattern;
    -------------------------------------------------------------------------------------------------------
    Procedure addFRDeletePattern( r1 c_mod_attrs%rowtype ) is
      v_delim   Varchar2(30):=' ';
    Begin
         BW_BUFFER.AddLN('MERGE'||replace(' /*+'||r1.TargetHint||'*/',' /*+*/')||' INTO '
           ||getTableQual(r1.TargetTable,r1.TargetOwner,r1.TargetLink,r1.TargetPartition)||' '||c_StdTargetPrefix);
	     BW_BUFFER.AddLN('USING(');
	     BW_BUFFER.IND(2);
         BW_BUFFER.AddLN('SELECT');
         BW_BUFFER.IND(3);
         for r1 in c_COLMAP LOOP
		   if NVL(r1.DirectFlag,'N')!='Y'  then
             BW_BUFFER.AddLN(v_delim||r1.TargetColumn);
             v_delim:=',';
		   end if;
         end LOOP;
         BW_BUFFER.IND(-3);
         BW_BUFFER.AddLN('FROM '||getTableQual(r1.TargetTable,r1.TargetOwner,r1.TargetLink,r1.TargetPartition));
         BW_BUFFER.IND(-1);
         BW_BUFFER.AddLN('MINUS');
         BW_BUFFER.IND(1);
	     AddSelectPart(getGeneratedSourceHints||r1.SourceHint);
	     AddFromPart;
	     AddFilterPart;
	     BW_BUFFER.IND(-2);
	     BW_BUFFER.AddLN(') '||c_StdSourcePrefix);
	     BW_BUFFER.AddLN('ON(');
	     BW_BUFFER.IND(2);
	     AddOnList;
	     BW_BUFFER.IND(-2);
	     BW_BUFFER.AddLN(')');
         BW_BUFFER.AddLN('WHEN MATCHED THEN UPDATE SET');
         BW_BUFFER.IND(2);
         AddSetList;
         addUpdateWhere(r1.pattern in ('DIFF_MERGE','DIFF_UPDATE'));
         BW_BUFFER.IND(-2);
         addLinkDummyMergeInsert(r1);
	     BW_BUFFER.AddLN(';');
	     BW_BUFFER.AddLN('v_processed_rows:=v_processed_rows+SQL%ROWCOUNT;');
    End addFRDeletePattern;
    -------------------------------------------------------------------------------------------------------
    Procedure addCreepingDeathPattern(r1 c_mod_attrs%rowtype) is
	    Procedure addSelectPartCreepingDeath (P_Source_hint Varchar2:=null)is
	      v_delim   Varchar2(30):=' ';
		  v_partitionBy    Varchar2(8000):='';
		  v_validFrom        Varchar2(8000):='';
		  v_partitionDelim Varchar2(30):=' ';
	    Begin
	       BW_BUFFER.AddLN('SELECT'||replace(' /*+'||P_Source_hint||'*/',' /*+*/'));
	       BW_BUFFER.IND(3);
	       for r1 in c_COLMAP LOOP
			   if NVL(r1.MatchFlag,'N')='Y' or NVL(r1.ValidToFlag,'N')='Y' then
	             BW_BUFFER.AddLN(v_delim||r1.TargetColumn);
	             v_delim:=',';
			   end if;
			   if NVL(r1.PartFlag,'N')='Y'  then
	             v_partitionBy:=concat(v_partitionBy,v_partitionDelim||r1.TargetColumn);
	             v_partitionDelim:=',';
			   end if;
			   if NVL(r1.ValidFromFlag,'N')='Y'  then
	             v_validFrom:=r1.TargetColumn;
			   end if;
	       end LOOP;
	       BW_BUFFER.AddLN(v_delim||'Lead('||v_validFrom||')over(partition by '||v_partitionBy||' order by '||v_validFrom||') as NEXT$FROM');
	       BW_BUFFER.IND(-3);
	    End addSelectPartCreepingDeath ;
    Begin
         BW_BUFFER.AddLN('MERGE'||replace(' /*+'||r1.TargetHint||'*/',' /*+*/')||' INTO '
           ||getTableQual(r1.TargetTable,r1.TargetOwner,r1.TargetLink,r1.TargetPartition)||' '||c_StdTargetPrefix);
	     BW_BUFFER.AddLN('USING(');
	     BW_BUFFER.IND(2);
	     AddSelectPartCreepingDeath(getGeneratedSourceHints||r1.SourceHint);
	     BW_BUFFER.AddLN('From '||getTableQual(r1.TargetTable,r1.TargetOwner,r1.TargetLink,r1.TargetPartition));
	     AddFilterPart;
	     BW_BUFFER.IND(-2);
	     BW_BUFFER.AddLN(') '||c_StdSourcePrefix);
	     BW_BUFFER.AddLN('ON(');
	     BW_BUFFER.IND(2);
	     AddOnList;
	     BW_BUFFER.AddLN('AND '||c_StdSourcePrefix||'.NEXT$FROM is not null');
	     BW_BUFFER.IND(-2);
	     BW_BUFFER.AddLN(')');
         addLinkDummyMergeInsert(r1);
	     BW_BUFFER.AddLN('WHEN MATCHED THEN UPDATE SET');
	     BW_BUFFER.IND(2);
	     AddSetList(true);
	     for r1 in c_COLMAP LOOP
	       if r1.ValidToFlag='Y' then
	           BW_BUFFER.AddLN(','||c_StdTargetPrefix||'.'||r1.TargetColumn||'='||c_StdSourcePrefix||'.NEXT$FROM-1');
	       end if; 
	     end LOOP;
		 BW_BUFFER.AddLN('');
	     addUpdateWhere(false);
	     BW_BUFFER.IND(-2);
	     BW_BUFFER.AddLN(';');
	     BW_BUFFER.AddLN('v_processed_rows:=v_processed_rows+SQL%ROWCOUNT;');
    End addCreepingDeathPattern;
    -------------------------------------------------------------------------------------------------------
    -- Package Part
    -------------------------------------------------------------------------------------------------------
	Procedure addLogProcedure is
	Begin
      BW_BUFFER.AddLN('--Log info -- some logging procedure should be added here');
	End addLogProcedure;
    -------------------------------------------------------------------------------------------------------
    Procedure addSeqWasteIssueFunctions is
      v_seqNm        Varchar2(100);
      v_delim        Varchar2(30):='';
      v_cond         Varchar2(9000):='';
    Begin
      for r1 in c_filter LOOP
        if NVL(r1.type,'SOURCE') in ('INSERT','SOURCE_OUTER') then 
          v_cond:=concat(v_cond,v_delim||r1.cond);
          v_delim:=chr(10)||'and '; 
        end if;
      end LOOP;
      if v_cond is not null then
        for r2 in c_colmap LOOP
          if upper(r2.SourceExpression) like '%.NEXTVAL%' then
             v_seqNM:=regexp_replace(regexp_substr(upper(r2.SourceExpression),'(^|\W)\w+([.]NEXTVAL)'),'\W|NEXTVAL');
             BW_BUFFER.AddLN('--sequence '||v_seqNM);
             BW_BUFFER.AddLN('Function get_'||v_seqNM||'(isit Integer) return Integer is');
             BW_BUFFER.AddLN('  i integer:=0;');
             BW_BUFFER.AddLN('Begin');
             BW_BUFFER.IND(2);
             BW_BUFFER.AddLN('  if isit=1 then');
             BW_BUFFER.AddLN('    select '||r2.SourceExpression||' into i from dual;');
             BW_BUFFER.AddLN('  end if;');
             BW_BUFFER.AddLN('  return i;');
             BW_BUFFER.IND(-2);
             BW_BUFFER.AddLN('End get_'||v_seqNM||';');
             va_expr_replace(r2.targetColumn):=p_module||'.get_'||v_seqNM||'(case when '||v_cond||' then 1 else 0 end)';
             va_spec_functions(nvl(va_spec_functions.last,0)+1):='Function get_'||v_seqNM||'(isit Integer) return Integer;';
             va_spec_funcnames(nvl(va_spec_funcnames.last,0)+1):='get_'||v_seqNM;
          end if;
        end LOOP; --r2
      end if;
	End addSeqWasteIssueFunctions ;
    -------------------------------------------------------------------------------------------------------
    Procedure addMainProcHead is
      v_is_head      Boolean:=false;
      v_tbdConst     Boolean:=true;
    Begin
      for r1 in (
         Select TEXT from USER_SOURCE where NAME=p_Module and TYPE='PACKAGE' order by line
      )LOOP
        if not v_is_head and upper(replace(r1.TEXT,' ')) like 'PROCEDUREMAIN%' then 
          v_is_head:=true;
        end if;
        if v_is_head then --witin header code
          if r1.TEXT like '%;%' then --resolve end
            v_is_head:=false;
            r1.TEXT:=rtrim(regexp_replace(r1.TEXT,'(^.*)(;)(.*$)','\1'),chr(10)||chr(13))||' is';
            BW_BUFFER.AddLN(r1.TEXT);
          else 
            BW_BUFFER.AddCH(r1.TEXT);
          end if; 
        end if; 
      End LOOP;
      BW_BUFFER.IND(2);
      BW_BUFFER.AddLN('--Audit variables');
      BW_BUFFER.AddLN('v_processed_rows Integer:=0;');
      BW_BUFFER.AddLN('v_Module_name Varchar2(100):='''||p_Module||''';');
      for r1 in c_constant LOOP
        BW_BUFFER.AddCH(r1.Name||' '||r1.Type);
        if r1.Value is not null then
          BW_BUFFER.AddCH(' := '||r1.Value);
        end if;
        BW_BUFFER.AddLN(';');
      end LOOP;
      BW_BUFFER.IND(-2);
      BW_BUFFER.AddLN('Begin');
      BW_BUFFER.IND(2);
      addLogProcedure;
      for r1 in c_constant LOOP
        if r1.query is not null then
          if v_tbdConst then
            BW_BUFFER.AddLN('--Constant queries');
            v_tbdConst:=false; 
          end if;  
          BW_BUFFER.AddLN('Begin');
          BW_BUFFER.AddLN('  '||translateDBLink(regexp_replace(r1.query,'(\W)(FROM)(\W)','\1INTO '||r1.Name||' \2\3',1,1,'i'))||';');
          BW_BUFFER.AddLN('  Exception when others then '||r1.name||':='||NVL(r1.value,'null')||';');
          BW_BUFFER.AddLN('End;');
        end if;
      end LOOP;
      BW_BUFFER.AddLN('--Mapping statement');
    End addMainProcHead;
    -------------------------------------------------------------------------------------------------------
    Procedure addMainProcTail is
    Begin
      addLogProcedure;
      BW_BUFFER.IND(-2);
      BW_BUFFER.AddLN('End Main;');
    End addMainProcTail;
    -------------------------------------------------------------------------------------------------------
    Procedure addPremappings is
    Begin
      for r1 in c_premappings LOOP
        BW_BUFFER.AddLN('-- Premapping: '||r1.name);
        BW_BUFFER.AddLN(translateDBLink(r1.code));
      end LOOP;
    End addPremappings;
    -------------------------------------------------------------------------------------------------------
    Procedure addPostmappings is
    Begin
      for r1 in c_postmappings LOOP
        BW_BUFFER.AddLN('-- Postmapping: '||r1.name);
        BW_BUFFER.AddLN(translateDBLink(r1.code));
      end LOOP;
    End addPostmappings;
    -------------------------------------------------------------------------------------------------------
    Procedure addExecConditionHead is
    Begin
      for r1 in c_execConditions LOOP
        BW_BUFFER.AddLN('if '||r1.code||' then -- Execute condition '||r1.name);
        BW_BUFFER.IND(2);
      end LOOP;
    End addExecConditionHead;
    -------------------------------------------------------------------------------------------------------
    Procedure addExecConditionTail is
    Begin
      for r1 in c_execConditions LOOP
        BW_BUFFER.IND(-2);
        BW_BUFFER.AddLN('end if;');
      end LOOP;
    End addExecConditionTail;
    -------------------------------------------------------------------------------------------------------
    Procedure addLoopHead is
      i integer:=0;
      v_case TAE;
      v_cs Varchar2(2000);
    Begin
      for r1 in c_loop LOOP
        i:=i+1;
        for r2 in c_loopVar(r1.COLUMN_VALUE) LOOP
           v_cs:=chr(10)||'  when '||to_char(i)||' then '||r2.value;
           if v_case.exists(r2.name) then
             v_case(r2.name):=concat(v_case(r2.name),v_cs);
           else
             v_case(r2.name):=r2.name||':=case i '||v_cs;
           end if;
        end LOOP;
      end LOOP;
      if i>0 then
        BW_BUFFER.AddLN('for i in 1..'||to_char(i)||' LOOP');
        BW_BUFFER.IND(2);
        v_cs:=v_case.first;
        while v_cs is not null LOOP
          BW_BUFFER.AddLN(concat(v_case(v_cs),chr(10)||'end;'));
          v_cs:=v_case.next(v_cs);
        end LOOP;
      end if;
    End addLoopHead;
    -------------------------------------------------------------------------------------------------------
    Procedure addLoopTail is
    Begin
      for r1 in c_loop LOOP
        BW_BUFFER.IND(-2);
        BW_BUFFER.AddLN('end LOOP;');
        exit;
      end LOOP;
    End addLoopTail;
    -------------------------------------------------------------------------------------------------------
    Procedure addPackageBodyHead is
    Begin
        BW_BUFFER.AddLN('Create or replace package body '||p_Module||' as');
        BW_BUFFER.IND(2);
        addSeqWasteIssueFunctions;  
        BW_BUFFER.AddLN('--Procedure MAIN');
        addMainProcHead;
        addLoopHead;
        addExecConditionHead;
        addPremappings;
    End addPackageBodyHead;
    -------------------------------------------------------------------------------------------------------
    Procedure addPackageBodyTail is
    Begin
        addPostmappings;
        addExecConditionTail;
        addLoopTail;
        addMainProcTail;
        BW_BUFFER.IND(-2);
        BW_BUFFER.AddLN('End '||p_Module||';');
    End addPackageBodyTail;
    -------------------------------------------------------------------------------------------------------
    procedure checkStatus is
      v_status Varchar2(30);
    Begin
      Select status into v_status from user_objects where object_type='PACKAGE BODY' and object_name=p_Module;
      if v_status='INVALID' then
        raise_application_error(-20006,'Module '||p_Module||' compiled unsuccessfully');
      end if; 
    End checkStatus;
    -------------------------------------------------------------------------------------------------------
    Procedure rewriteSpec is
      v_yetgen boolean:=false;
      v_ptg  boolean:=false;
      v_pt1  boolean:=false;
      v_pt2  boolean:=false;
      v_done boolean:=false;
      v_skip boolean:=false;
    Begin
        for r1 in (
          Select TEXT from USER_SOURCE where NAME=p_Module and TYPE='PACKAGE' order by line
        )LOOP
          if upper(replace(r1.TEXT,' ')) like '/*GENERATEDPART%' then 
            v_yetgen:=true;
            exit;
          end if;
        End LOOP;
        BW_BUFFER.Init;
        BW_BUFFER.AddCh(' CREATE OR REPLACE ');
        for r1 in (
          Select TEXT from USER_SOURCE where NAME=p_Module and TYPE='PACKAGE' order by line
        )LOOP
          if not v_pt1 and upper(replace(r1.TEXT,' ')) like 'PROCEDUREMAIN%' then 
            v_pt1:=true;
          end if;
          if v_pt1 and r1.TEXT like '%;%' then 
            v_pt2:=true;
          end if;
          if not v_ptg and upper(replace(r1.TEXT,' ')) like '/*GENERATEDPART%' then
            v_ptg:=true;
          end if;
          if v_skip and upper(replace(r1.TEXT,' ')) like '/*!GENERATEDPART%' then
            v_skip:=false;
          end if;
          if not v_skip then
            BW_BUFFER.AddLN(rtrim(r1.TEXT,chr(10)));
          end if;
          if not v_done and ((v_pt2 and not v_yetgen) or v_ptg) then
            if va_spec_functions.count>0 then
              if v_yetgen then
                v_skip:=true;
              else
                BW_BUFFER.AddLN('/* GENERATED PART */');
              end if;
              for i in 1..va_spec_functions.count LOOP
                BW_BUFFER.AddLN('  '||va_spec_functions(i));
                BW_BUFFER.AddLN('  PRAGMA RESTRICT_REFERENCES ('||va_spec_funcnames(i)||', WNPS, WNDS, RNPS);');
              end LOOP;
              if not v_yetgen then
                BW_BUFFER.AddLN('/* !GENERATED PART */');
              end if;
            elsif v_yetgen then
               v_skip:=true;
            end if;
            v_done:=true;
          end if;
        End LOOP;
        BW_BUFFER.BuffExec;
        BW_BUFFER.Init;
        BW_BUFFER.AddLN('ALTER PACKAGE '||p_Module||' COMPILE BODY');
        BW_BUFFER.BuffExec;
    End rewriteSpec;
    -------------------------------------------------------------------------------------------------------
 Begin
   DBMS_OUTPUT.ENABLE(1000000);
   BW_Buffer.Init;
   fillXML;
   addPackageBodyHead;
   for r1 in c_mod_attrs LOOP
     if r1.pattern in ('MERGE','DIFF_MERGE','UPDATE','DIFF_UPDATE','DIFF_INSERT') then
       addMergePattern(r1);
     elsif r1.pattern in ('INSERT','DIFF_INSERT(MINUS)') then
       addInsertPattern(r1);
     elsif r1.pattern in ('SELF_UPDATE') then
       addSelfUpdatePattern(r1);
     elsif r1.pattern in ('FR_DELETE') then
       addFRDeletePattern(r1);
     elsif r1.pattern in ('CREEPING_DEATH') then
       addCreepingDeathPattern(r1);
     elsif r1.pattern in ('NONE') then
       null;
	 end if;
   end LOOP;
   addPackageBodyTail;
   BW_Buffer.BuffExec;
   rewritespec;
   checkStatus; 
 End GenPackageBody;
END BW_SIMPLE_GEN;
/