Paste: xf

Author:	dfgfh
Mode:	pl-sql
Date:	Mon, 23 Mar 2009 08:25:13

Plain Text |

Delete Paste

PROCEDURE arr13_prepare IS

DEF_WHR VARCHAR2(6000);

BEGIN

  DEF_WHR:=' ';

--  DEF_WHR:=DEF_WHR||' AND :P_LEVEL_1 <>  ''S''  '; 
  DEF_WHR:=DEF_WHR||BLD_OPR('=','T1.SL90_ID ','T2.SL90_ID','N');
  DEF_WHR:=DEF_WHR||' and (T1.GN03_ID = T2.GN03_ID)';
  DEF_WHR:=DEF_WHR||' AND T2.SC17_ID IS NULL' ;
  --Added by Alaa on 18-03-2009
  DEF_WHR:=DEF_WHR||' AND AR131.AR02_ID IN (SELECT AR02_ID FROM AR_TRS WHERE GN02_ID IN (SELECT GN02_ID FROM BUSINESS_UNITS GN02 WHERE (:P_BSNS_UNT_CD_FROM IS NULL OR :P_BSNS_UNT_CD_FROM <= GN02.BSNS_UNT_CD) AND (:P_BSNS_UNT_CD_TO IS NULL OR :P_BSNS_UNT_CD_TO >= GN02.BSNS_UNT_CD)))';
  --End by Alaa
  DEF_WHR:=DEF_WHR||BLD_OPR('=','AR131.AR02_ID ','T1.AR02_ID','N');
  DEF_WHR:=DEF_WHR||BLD_OPR('=','AR131.AR71_ID ','AR711.AR71_ID','N');
  DEF_WHR:=DEF_WHR||BLD_OPR('=','AR131.BSNS_YR ',:P_BSNS_YR,'N');
  DEF_WHR:=DEF_WHR||BLD_OPR('=','SL901.SL90_ID ','T1.SL90_ID','N');
  DEF_WHR:=DEF_WHR||BLD_OPR('=',' T1.GN02_ID ', 'GN02.GN02_ID','N');
  DEF_WHR:=DEF_WHR||BLD_OPR('=',' T1.GN01_ID ', :P_GN01_ID,'N');
  --Line Below commented by Alaa on 18-03-2009
--  DEF_WHR:=DEF_WHR||BLD_OPR('=',' T1.GN02_ID ', :P_GN02_ID,'N');
  
  
  DEF_WHR:=DEF_WHR||BLD_RNG('AR711.TRS_CLASS_CD',:P_TRS_CLASS_CD_FROM,'C',:P_TRS_CLASS_CD_TO,'C');
  
    DEF_WHR:=DEF_WHR||BLD_RNG('T1.CRNCY_CD',:P_CRNCY_CD_FROM,'C',:P_CRNCY_CD_TO,'C');
    DEF_WHR:=DEF_WHR||BLD_RNG('SL901.cust_cd',:P_cust_CD_FROM,'C',:P_cust_CD_TO,'C');
    DEF_WHR:=DEF_WHR||BLD_RNG('T1.BSNS_LN_CD',:P_BSNS_LN_CD_FROM,'C',:P_BSNS_LN_CD_TO,'C', :P_INCLUDE_BSNS_LN);
  
DEF_WHR:=DEF_WHR||' AND SL901.SLB0_ID = SLB01.SLB0_ID(+) ' ;
  
  
  DEF_WHR:=DEF_WHR||' AND ( ((:P_CUST_TYP_FROM IS NULL )
                        AND (:P_CUST_TYP_TO IS NULL      ))
                         OR((SL901.SLB0_ID IS NOT NULL)'; 
  DEF_WHR:=DEF_WHR||BLD_RNG('SLB01.CUST_TYP_CD',:P_CUST_TYP_FROM,'C',:P_CUST_TYP_TO,'C');
  DEF_WHR:=DEF_WHR||' ))  ';
--Line below commented by Alaa on 18-03-2009
--  DEF_WHR:=DEF_WHR||BLD_RNG('GN02.BSNS_UNT_CD',:P_BSNS_UNT_CD_FROM,'C', :P_BSNS_UNT_CD_TO,'C');

 

DEF_WHR:=DEF_WHR||' And ((SL901.CM06_ID IS NULL) OR 
	                         (SL901.CM06_ID IN ( SELECT CM06_ID	         
                                                   FROM   COUNTRY   CM06 
                                                    WHERE 1 = 1 '||
                       BLD_RNG('CM06.CNTRY_CD',:P_CNTRY_CD_FROM,'C',:P_CNTRY_CD_TO,'C')
                                         || ' AND (CM06.GN01_ID = '|| :P_GN01_ID ||' )))) ';

:Q3_where := def_whr ;
--------------------------------------------------------------------------
def_whr := ' ' ;

IF :P_EXCLD_NON_ACTIVE ='Y' THEN
  IF(:P_TRS_GDT_ACTIVE IS NOT NULL )THEN
     DEF_WHR:=DEF_WHR||' AND EXISTS (SELECT AR21_ID 
                             FROM AR_TRS   
                              WHERE SL90_ID =T1.SL90_ID 
           AND TRS_GDT >= TO_DATE('''||:P_TRS_GDT_ACTIVE||''',''DD-MM-YYYY'') ) ';
  END IF;
IF :P_TRS_NO_ACTIVE IS NOT NULL THEN
       DEF_WHR:=DEF_WHR||' AND ('||:P_TRS_NO_ACTIVE||' <= (SELECT COUNT(1)CNT
                                   	                 FROM AR_TRS
                                                   WHERE SL90_ID = T1.SL90_ID)) '; 
  END IF;
END IF;	

 
DEF_WHR:=DEF_WHR||' And  ((SL901.CM09_ID IS NULL) OR 
               (SL901.CM09_ID IN ( SELECT CM09_ID	         
                FROM   (SELECT CM09_ID	, LEVEL          
                         FROM   RGN
                          CONNECT BY PRIOR CM09_ID = CM09_ID_MAY_BELONGS_TO
                          START WITH  CM09_ID  IN ( SELECT CM09_ID
                                               FROM RGN CM09
                                                WHERE  1=1 '||
   BLD_RNG('CM09.RGN_CD ', :P_RGN_CD_FROM,'C', :P_RGN_CD_TO,'C');
DEF_WHR:=DEF_WHR||' AND CM09.GN01_ID = :P_GN01_ID '||'))';
DEF_WHR:=DEF_WHR||' Where 1=1 '||' And :P_ALL_cust_RGN = ''Y'' ' ;
DEF_WHR:=DEF_WHR||' or ( ( :P_ALL_cust_RGN= ''N'') AND (LEVEL = 1 ) )))) ' ;

 
IF :P_EXCLD_STSTCL_CUST = 'Y' THEN
     DEF_WHR := DEF_WHR||' AND (SL901.LEAF_FLG = ''Y'') ';

   END IF;
 
IF :P_EXCLD_NON_STSTCL_CUST = 'Y' THEN
     DEF_WHR:= DEF_WHR ||' AND (SL901.LEAF_FLG =''N'') ';
   END IF;
 

 
IF :P_EXCLD_ACTIVE ='Y' THEN
     IF(:P_TRS_GDT_ACTIVE IS NOT NULL )THEN
       DEF_WHR:=DEF_WHR||' AND NOT (EXISTS (SELECT AR21_ID 
                                   FROM AR_TRS  
                                   WHERE SL90_ID = T1.SL90_ID 
          AND TRS_GDT >= TO_DATE('''||:P_TRS_GDT_ACTIVE||''',''DD-MM-YYYY''))) ';
     END IF;

     IF :P_TRS_NO_ACTIVE IS NOT NULL THEN
       DEF_WHR:=DEF_WHR||' AND  ( '||:P_TRS_NO_ACTIVE||' > (SELECT COUNT(1)  CNT
                                          	     FROM AR_TRS
                                                 WHERE SL90_ID = T1.SL90_ID)) ';
     END IF;
END IF;
 

:Q5_where := def_whr ;
--------------------------------------------------------------------------
def_whr := ' ' ;

IF :P_EXCLD_NON_Aggregate_Post = 'Y' THEN 
     DEF_WHR:=DEF_WHR||' AND
        (((T1.STSTCL_ACC_FLG = ''Y'') AND(T1.SC17_ID IS NULL)) OR
	   (NOT((T1.STSTCL_ACC_FLG = ''Y'') AND(T1.SC17_ID IS NULL)) AND
	    NOT((T2.STSTCL_ACC_FLG = ''Y'') AND(T2.SC17_ID IS NULL))) ) ';
END IF;

 
IF :P_EXCLD_Aggregate_Post = 'Y' THEN
     DEF_WHR:=DEF_WHR||' AND NOT ((T1.STSTCL_ACC_FLG = ''Y'') AND(T1.SC17_ID IS NULL)) '; 	  
 END IF; 

  IF :P_EXCLD_ZERO_BAL_CUST = 'Y' THEN
    DEF_WHR := DEF_WHR || ' and (NVL(AR131.TOT_ACC_TRS_AMT,0)>0) ';
    DEF_WHR := DEF_WHR || ' and (NVL(AR131.TOT_LCL_TRS_AMT,0)>0) ';
  END IF;
  
  IF :P_EXCLD_AMT_OVER IS NOT NULL THEN
    DEF_WHR := DEF_WHR ||' and NVL(AR131.TOT_ACC_TRS_AMT,0)<='||:P_EXCLD_AMT_OVER||' ';
    DEF_WHR := DEF_WHR ||' and NVL(AR131.TOT_LCL_TRS_AMT,0)<='||:P_EXCLD_AMT_OVER||' ';
  END IF;
  
  IF :P_EXCLD_AMT_UNDR IS NOT NULL THEN
      DEF_WHR := DEF_WHR ||' and NVL(AR131.TOT_ACC_TRS_AMT,0)>='||:P_EXCLD_AMT_UNDR||' ';
      DEF_WHR := DEF_WHR ||' and NVL(AR131.TOT_LCL_TRS_AMT,0)>='||:P_EXCLD_AMT_UNDR||' ';

  END IF;
  
DEF_WHR:=DEF_WHR||' AND ((:P_SL_REP_CD_TO is null and :P_SL_REP_CD_FROM is null )';
DEF_WHR:=DEF_WHR||' OR (AR131.SL49_ID IN 
 	                                ( SELECT SL49_ID	         
 	                                  FROM   SL_REP SL49
 	                                  where 1=1 '|| BLD_RNG('SL49.SL_REP_CD',:P_SL_REP_CD_FROM,'C',:P_SL_REP_CD_TO,'C');
 DEF_WHR:=DEF_WHR||' AND (SL49.GN01_ID = :P_GN01_ID ) )))' ;	                                  
 
:q1_where := DEF_WHR ;
 
---------------------------------Query 2 -----------------------------

 DEF_WHR:= ' ';
  
 -- DEF_WHR:=DEF_WHR||' AND :P_LEVEL_1 = ''S'' ' ; 

----------Added by Amira 21/03/2004
 DEF_WHR:=DEF_WHR||BLD_OPR('=',' SL49.gn01_id ', :P_GN01_ID,'N');
 DEF_WHR:=DEF_WHR||BLD_OPR('=',' SL49.gn02_id', :P_GN02_ID,'N');
 --------Ended by Amira 
 
 DEF_WHR:=DEF_WHR||BLD_OPR('=','T1.SL90_ID ','T2.SL90_ID','N');
 DEF_WHR:=DEF_WHR||BLD_OPR('=','T1.GN03_ID ','T2.GN03_ID','N');
 DEF_WHR:=DEF_WHR||' AND T2.SC17_ID IS NULL' ;
 DEF_WHR:=DEF_WHR||BLD_OPR('=','AR131.AR02_ID ','T1.AR02_ID','N');
 DEF_WHR:=DEF_WHR||BLD_OPR('=','AR131.AR71_ID ','AR711.AR71_ID','N');
 DEF_WHR:=DEF_WHR||BLD_OPR('=','AR131.BSNS_YR ',:P_BSNS_YR,'N');
 DEF_WHR:=DEF_WHR||BLD_OPR('=','SL901.SL90_ID ','T1.SL90_ID','N');
 DEF_WHR:=DEF_WHR||BLD_OPR('=',' T1.GN02_ID ', 'GN02.GN02_ID','N');
 DEF_WHR:=DEF_WHR||BLD_OPR('=',' T1.GN01_ID ', :P_GN01_ID,'N');
 DEF_WHR:=DEF_WHR||BLD_OPR('=',' T1.GN02_ID ', :P_GN02_ID,'N');
 DEF_WHR:=DEF_WHR||BLD_RNG('AR711.TRS_CLASS_CD',:P_TRS_CLASS_CD_FROM,'C',:P_TRS_CLASS_CD_TO,'C');
 DEF_WHR:=DEF_WHR||BLD_RNG('T1.CRNCY_CD',:P_CRNCY_CD_FROM,'C',:P_CRNCY_CD_TO,'C');  
 DEF_WHR:=DEF_WHR||BLD_RNG('SL49.SL_REP_CD',:P_SL_REP_CD_FROM,'C',:P_SL_REP_CD_TO,'C');
 
 DEF_WHR:=DEF_WHR||BLD_RNG('SL901.cust_cd',:P_cust_CD_FROM,'C',:P_cust_CD_TO,'C');   
 DEF_WHR:=DEF_WHR||BLD_RNG('T1.BSNS_LN_CD',:P_BSNS_LN_CD_FROM,'C',:P_BSNS_LN_CD_TO,'C', :P_INCLUDE_BSNS_LN);
 DEF_WHR:=DEF_WHR||' AND SL901.SLB0_ID = SLB01.SLB0_ID(+) ' ;
 
 DEF_WHR:=DEF_WHR||' AND ( ((:P_CUST_TYP_FROM IS NULL )
                       AND (:P_CUST_TYP_TO IS NULL      ))
                        OR((SL901.SLB0_ID IS NOT NULL)'; 
 DEF_WHR:=DEF_WHR||BLD_RNG('SLB01.CUST_TYP_CD',:P_CUST_TYP_FROM,'C',:P_CUST_TYP_TO,'C');
 DEF_WHR:=DEF_WHR||' ))  ';
 DEF_WHR:=DEF_WHR||BLD_RNG('GN02.BSNS_UNT_CD',:P_BSNS_UNT_CD_FROM,'C', :P_BSNS_UNT_CD_TO,'C');

 DEF_WHR:=DEF_WHR ||' And ((SL901.CM06_ID IS NULL) OR 
	                         (SL901.CM06_ID IN ( SELECT CM06_ID	         
                                                   FROM   COUNTRY   CM06 
                                                    WHERE 1 = 1 '||
                       BLD_RNG('CM06.CNTRY_CD',:P_CNTRY_CD_FROM,'C',:P_CNTRY_CD_TO,'C')
                                      || ' AND (CM06.GN01_ID = '||:P_GN01_ID||' )))) ';

:Q4_where := def_whr ;
--------------------------
def_whr := ' ' ;
  
IF :P_EXCLD_NON_ACTIVE ='Y' THEN
   IF(:P_TRS_GDT_ACTIVE IS NOT NULL )THEN
     DEF_WHR:=DEF_WHR ||' AND EXISTS (SELECT AR21_ID 
                             FROM AR_TRS   
                              WHERE SL90_ID =T1.SL90_ID 
           AND TRS_GDT >= TO_DATE('''||:P_TRS_GDT_ACTIVE||''',''DD-MM-YYYY'') ) ';
   END IF;

IF :P_TRS_NO_ACTIVE IS NOT NULL THEN
       DEF_WHR:=DEF_WHR ||' AND ('||:P_TRS_NO_ACTIVE||' <= (SELECT COUNT(1)CNT
                                   	                 FROM AR_TRS
                                                   WHERE SL90_ID = T1.SL90_ID)) ';
END IF;
End if;
  


 DEF_WHR:=DEF_WHR||' And  ((SL901.CM09_ID IS NULL) OR 
                (SL901.CM09_ID IN ( SELECT CM09_ID	         
                 FROM   (SELECT CM09_ID	, LEVEL          
                          FROM   RGN
                           CONNECT BY PRIOR CM09_ID = CM09_ID_MAY_BELONGS_TO
                           START WITH  CM09_ID  IN ( SELECT CM09_ID
                                                FROM RGN CM09
                                                 WHERE  1=1 '||
    BLD_RNG('CM09.RGN_CD ', :P_RGN_CD_FROM,'C', :P_RGN_CD_TO,'C');
 DEF_WHR:=DEF_WHR||' AND CM09.GN01_ID = :P_GN01_ID '||'))';
 DEF_WHR:=DEF_WHR||' Where 1=1 '||' And :P_ALL_cust_RGN = ''Y'' ' ;
 DEF_WHR:=DEF_WHR||' or ( ( :P_ALL_cust_RGN= ''N'') AND (LEVEL = 1 ) )))) ' ;       

IF :P_EXCLD_STSTCL_CUST = 'Y' THEN
     DEF_WHR := DEF_WHR||' AND (SL901.LEAF_FLG = ''Y'') ';

   END IF;

   IF :P_EXCLD_NON_STSTCL_CUST = 'Y' THEN
     DEF_WHR:= DEF_WHR ||' AND (SL901.LEAF_FLG =''N'') ';
   END IF;
 

  IF :P_EXCLD_ZERO_BAL_CUST = 'Y' THEN
    DEF_WHR := DEF_WHR || ' and (NVL(AR131.TOT_ACC_TRS_AMT,0)>0) ';
    DEF_WHR := DEF_WHR || ' and (NVL(AR131.TOT_LCL_TRS_AMT,0)>0) ';
  END IF;
  
  IF :P_EXCLD_AMT_OVER IS NOT NULL THEN
    DEF_WHR := DEF_WHR ||' and NVL(AR131.TOT_ACC_TRS_AMT,0)<='||:P_EXCLD_AMT_OVER||' ';
    DEF_WHR := DEF_WHR ||' and NVL(AR131.TOT_LCL_TRS_AMT,0)<='||:P_EXCLD_AMT_OVER||' ';
  END IF;
  
  IF :P_EXCLD_AMT_UNDR IS NOT NULL THEN
      DEF_WHR := DEF_WHR ||' and NVL(AR131.TOT_ACC_TRS_AMT,0)>='||:P_EXCLD_AMT_UNDR||' ';
      DEF_WHR := DEF_WHR ||' and NVL(AR131.TOT_LCL_TRS_AMT,0)>='||:P_EXCLD_AMT_UNDR||' ';
  END IF;

:Q6_where := def_whr ;
--------------------------
def_whr := ' ' ;

IF :P_EXCLD_ACTIVE ='Y' THEN
     IF(:P_TRS_GDT_ACTIVE IS NOT NULL )THEN
       DEF_WHR:=DEF_WHR ||' AND NOT (EXISTS (SELECT AR21_ID 
                                   FROM AR_TRS  
                                   WHERE SL90_ID = T1.SL90_ID 
          AND TRS_GDT >= TO_DATE('''||:P_TRS_GDT_ACTIVE||''',''DD-MM-YYYY''))) ';
     END IF;
 
     IF :P_TRS_NO_ACTIVE IS NOT NULL THEN
       DEF_WHR:=DEF_WHR ||' AND  ( '||:P_TRS_NO_ACTIVE||' > (SELECT COUNT(1)  CNT
                                          	     FROM AR_TRS
                                                 WHERE SL90_ID = T1.SL90_ID)) ';

     END IF;
END IF;
  

  IF :P_EXCLD_NON_Aggregate_Post = 'Y' THEN 
     DEF_WHR:=DEF_WHR ||' AND
        (((T1.STSTCL_ACC_FLG = ''Y'') AND(T1.SC17_ID IS NULL)) OR
	   (NOT((T1.STSTCL_ACC_FLG = ''Y'') AND(T1.SC17_ID IS NULL)) AND
	    NOT((T2.STSTCL_ACC_FLG = ''Y'') AND(T2.SC17_ID IS NULL))) ) ';
   END IF;

  IF :P_EXCLD_Aggregate_Post = 'Y' THEN
     DEF_WHR:=DEF_WHR ||' AND NOT ((T1.STSTCL_ACC_FLG = ''Y'') AND(T1.SC17_ID IS NULL)) '; 	  
   END IF;
 
 /* DEF_WHR:=DEF_WHR||' AND ((:P_SL_REP_CD_TO is null and :P_SL_REP_CD_FROM is null )';
  DEF_WHR:=DEF_WHR||' OR (AR131.SL49_ID IN 
   	                                ( SELECT SL49_ID	         
   	                                  FROM   SL_REP SL49
   	                                  where 1=1 '|| BLD_RNG('SL49.SL_REP_CD',:P_SL_REP_CD_FROM,'C',:P_SL_REP_CD_TO,'C');
  DEF_WHR:=DEF_WHR||' AND (SL49.GN01_ID = :P_GN01_ID ) )))' ;	   
   
 */

:Q2_where :=  DEF_WHR;
 
--------------------------------Ended Query 2 -------------------------------------------------------------  
END;

New Annotation

Summary:
Author:
Mode:	textPostgreSQLPowerCenter Parameter Fileactionscriptadaada95ans-forth94antantlrantlr4apacheconfapdlapplescriptaspaspect-jassembly-agcassembly-agsassembly-arm32assembly-m68kassembly-macro32assembly-mcs51assembly-parrotassembly-r2000assembly-x86avroawkbbatchbbjbcelbeanshellbibtexbinsource-agccc#c++cfscriptchillcilclipsclojurecmakecobolcoffeescriptcoldfusioncplex-lpcsscsvcvs-commitddartdjangodockerfiledotdoxygendrawj2ddsssleiffelembperlerlangfactorfhtmlforthfortranfortran90foxprofreemarkergcbasicgettextgherkingnuplotgogradlegroovygsphaskellhaxehexhl7v2hlslhtaccesshtmlhxmli4glicalendariconidlinforminiinno-setupinterlisiojamonjavajava module-infojavaccjavafxjavascriptjcljedit-actionsjflexjhtmljmkjsonjspkotlinlatexlexlilypondlispliterate-haskelllogslogtalklotosluam4macroschedulermailmakefilemaplemarkdownmavenmavscriptmetapostmlmodula3moinmqscmustache-templatemxmlmyghtymysqln3netrexxnqcnsis2objective-cobjectrexxoccamomnimarkopensipsoutlinepascalpatchperlphppikepl-sqlpl-sql9pl1plaintexplantumlpop11postscriptpovraypowerdynamopowershellprogressprologpropertiesprotobufpspptlpurepvwavepyrexpythonqdocrakefilercprdrebolredcoderelax-ng-compactrenderman-ribrestrfcrhtmlroffrpm-specrtfrubyrustrviews#s+sassbtscalaschemesdl/prsgmlshellscriptshtmlsipsippslateslaxsmalltalksmartysmi-mibsparqlsql-loadersqrsquidconfstatasvn-commitswiftswigtcltemplate-toolkittextexinfotldtransact-sqltsptwikityposcripturluscriptvalavbscriptvelocityverilogvhdlvisualbasicvrml2wellknowntextxmlxqxslyabyamlzpt
Body:

Done