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;