function BeforeReport return boolean is
S NUMBER(4);
C NUMBER(4);
ln_Count NUMBER;
STR1 VARCHAR2(200);
STR2 VARCHAR2(200);
STR3 VARCHAR2(200);
STR4 VARCHAR2(200);
STR5 VARCHAR2(200);
STR5_1 VARCHAR2(200);
STR6 VARCHAR2(200);
STR7 VARCHAR2(200);
STR7_1 VARCHAR2(200);
STR7_2 NUMBER;
l_store VARCHAR2(500);

begin

srw.message('1001','Before Prog Id');

:P_PROG_ID := nvl(:P_PROG_ID,'AP040R');
:P_USER_ID := 'HQAPP002';

srw.message('1002','After Prog Id');

IF :P_USER_ID IS NOT NULL THEN
SELECT USER_STORE.STORE_CD
INTO S
FROM USER_STORE
WHERE USER_STORE.USR_ID = :P_USER_ID
AND DEFAULT_STORE= 'Y';
END IF;

srw.message('1003','After First IF');

IF S IS NOT NULL THEN
SELECT C.CTRL_CO_NAME
INTO :P_CO_NAME
FROM COMPANY_CONTROL C, STORE 
WHERE C.CTRL_COMPANY_NO = STORE.CTRL_COMPANY_NO
AND STORE.STORE_CD = S;
END IF; 

-- Check Branch Access Right 
l_store:='(0';
--For i In nvl(:P_BRANCH_FR,0)..nvl(:P_BRANCH_TO,0) 
FOR I IN (select distinct store_cd from user_store where USR_ID = :P_USER_ID
and store_cd between nvl(:P_BRANCH_FR,0) and nvl(:P_BRANCH_TO,0) )
LOOP

l_store:=l_store||','||TO_CHAR(I.STORE_CD);

END LOOP; 


IF (:P_BRANCH_FR IS NULL and :P_BRANCH_TO IS NULL) or (:P_BRANCH_FR IS not NULL and :P_BRANCH_TO IS NULL) THEN
STR1 := ' AND SUPPLIER_HIST.STORE_CD =0';
ELSIF (nvl(:P_BRANCH_FR,0)=0 and nvl(:P_BRANCH_to,0)>0 ) OR (nvl(:P_BRANCH_FR,0)>0 and nvl(:P_BRANCH_to,0)>0 ) THEN
STR1 := ' AND SUPPLIER_HIST.STORE_CD IN (select distinct store_cd from user_store where USR_ID = '''
||:P_USER_ID||''' and store_cd between '||nvl(:P_BRANCH_FR,0)||' and '||nvl(:P_BRANCH_TO,0) ||')';
END IF; 

IF :P_SUP_MAINCD_FR IS NOT NULL THEN
STR3 := ' AND SUPPLIER_HIST.SUP_MAINCD >= ''' || :P_SUP_MAINCD_FR || '''';
END IF; 

IF :P_SUP_MAINCD_TO IS NOT NULL THEN
STR4 := ' AND SUPPLIER_HIST.SUP_MAINCD <= ''' || :P_SUP_MAINCD_TO || '''';
END IF; 


IF :P_SUP_TYPE_FR IS NOT NULL AND :P_SUP_TYPE_FR<> 'ALL' THEN 
IF :P_SUP_TYPE_TO IS NOT NULL AND :P_SUP_TYPE_TO<> 'ALL' THEN
STR5 := ' AND SUPD_TYPE >= ''' || SUBSTR(:P_SUP_TYPE_FR,1,1) || '''';
STR5_1 := ' AND SUPD_TYPE <= ''' || SUBSTR(:P_SUP_TYPE_TO,1,1) || '''';
ELSE
STR5 := ' AND SUPD_TYPE = ''' || SUBSTR(:P_SUP_TYPE_FR,1,1) || '''';
END IF; 
END IF; 

IF :P_SECTION IS NOT NULL THEN
STR6 := ' AND SECT_CD = ''' || :P_SECTION || '''';
END IF;

IF :P_STATUS IS NOT NULL AND :P_STATUS <>'ALL' THEN
IF :P_STATUS IS NOT NULL AND :P_STATUS= 'DEBIT BALANCE' THEN
STR7 := 'AND nvl(SUPPLIER_HIST.SUPH_BF,0)+nvl(SUPPLIER_HIST.SUPH_PUR_AMT,0)-nvl(SUPPLIER_HIST.SUPH_RETN_AMT,0)+ nvl(SUPPLIER_HIST.SUPH_PUR_ADJ, 0) - nvl(SUPPLIER_HIST.SUPH_RETN_ADJ, 0)+ nvl(SUPPLIER_HIST.SUPH_CR_AMT,0)-nvl(SUPPLIER_HIST.SUPH_DR_AMT,0)+ nvl(SUPPLIER_HIST.SUPH_MISC_AMT,0) - 
nvl(SUPPLIER_HIST.SUPH_PMT_AMT,0)) < ''' || :P_STATUS || '''';
ELSIF :P_STATUS IS NOT NULL AND :P_STATUS = 'CREDIT BALANCE' THEN
STR7_1 := ' AND (nvl(SUPPLIER_HIST.SUPH_BF,0)+nvl(SUPPLIER_HIST.SUPH_PUR_AMT,0)-nvl(SUPPLIER_HIST.SUPH_RETN_AMT,0)+ nvl(SUPPLIER_HIST.SUPH_PUR_ADJ, 0) - nvl(SUPPLIER_HIST.SUPH_RETN_ADJ, 0)+ nvl(SUPPLIER_HIST.SUPH_CR_AMT,0)-nvl(SUPPLIER_HIST.SUPH_DR_AMT,0)+ nvl(SUPPLIER_HIST.SUPH_MISC_AMT,0) - 
nvl(SUPPLIER_HIST.SUPH_PMT_AMT,0)) >= )''' || :P_STATUS ||'''';
END IF;
END IF; 

:P_WHERE := STR1 || STR2 || STR3 || STR4 ||STR5||STR5_1||STR6||STR7||STR7_1;

return (true);
end; 

function BeforeReport return boolean is
S NUMBER(4);
C NUMBER(4);
ln_Count NUMBER;
STR1 VARCHAR2(200);
STR2 VARCHAR2(200);
STR3 VARCHAR2(200);
STR4 VARCHAR2(200);
STR5 VARCHAR2(200);
STR5_1 VARCHAR2(200);
STR6 VARCHAR2(200);
STR7 VARCHAR2(200);
STR7_1 VARCHAR2(200);
STR7_2 NUMBER;
STR8 VARCHAR2(200);
l_store VARCHAR2(500);

IF :P_NOTE_TYPE IS NOT NULL THEN
STR8 :=' AND NOTE_TYPE = ''' ||:P_NOTE_TYPE|| ''''
END IF; 

:P_WHERE := STR1 || STR2 || STR3 || STR4 ||STR5||STR5_1||STR6||STR7||STR7_1;

:P_WHERE := STR1 || STR2 || STR3 || STR4 ||STR5||STR5_1||STR6||STR7||STR7_1||STR8;