SELECT to_char(GLTRACE.TRAN_DATE,'DD-MON-YY') TRAN_DATE, 
GLTRACE.REF_SER, 
GLTRACE.REF_ID, 
GLTRACE.ACCT_CODE, 
GLTRACE.CCTR_CODE, 
GLTRACE.DR_AMT, 
GLTRACE.CR_AMT, 
GLTRACE.SITE_CODE,  
GLTRACE.FIN_ENTITY, 
GLTRACE.CURR_CODE, 
GLTRACE.EXCH_RATE,     
GLTRACE.ACCT_PRD, 
GLTRACE.PRD_CODE, 
GLTRACE.REMARKS, 
GLTRACE.PROJ_CODE,  
to_char(GLTRACE.CHG_DATE,'DD-MON-YY') CHG_DATE, 
GLTRACE.sundrytype, 
GLTRACE.sundrycode, 
GLTRACE.comp_ref, 
GLTRACE.SH_NAME, 
GLTRACE.SUNDRY_NAME, 
GLTRACE.SUNDRY_DETAILS, 
GLTRACE.ORG_LOCN, 
GLTRACE.GP_NO, 
to_char(GLTRACE.GP_DATE,'DD-MON-YY') GP_DATE, 
GLTRACE.BL_NO, 
to_char(GLTRACE.BL_DATE,'DD-MON-YY') BL_DATE FROM ( 

SELECT GLTRACE.TRAN_DATE,GLTRACE.REF_SER, GLTRACE.REF_ID,      
GLTRACE.ACCT_CODE, GLTRACE.CCTR_CODE, GLTRACE.DR_AMT AS DR_AMT, 
GLTRACE.CR_AMT AS CR_AMT,GLTRACE.SITE_CODE, GLTRACE.FIN_ENTITY, 
GLTRACE.CURR_CODE,GLTRACE.EXCH_RATE, 
GLTRACE.ACCT_PRD,GLTRACE.PRD_CODE,GLTRACE.REMARKS, 
GLTRACE.PROJ_CODE, GLTRACE.CHG_DATE, 
CASE WHEN SUNDRY_TYPE='O' THEN 
rtrim(ddf_get_sundrytype(nvl(GLTRACE.SUNDRY_TYPE,'O'),GLTRACE.SUNDRY_CODE,GLTRACE.REF_ID,GLTRACE.REF_SER,'A')) ELSE SUNDRY_TYPE END sundrytype, 
CASE WHEN SUNDRY_TYPE='O' THEN ddf_get_sundrytype(nvl(GLTRACE.SUNDRY_TYPE,'O'),GLTRACE.SUNDRY_CODE,GLTRACE.REF_ID,GLTRACE.REF_SER,'B') ELSE SUNDRY_CODE END sundrycode, 
NVL(rtrim(ddf_get_bill_no( gltrace.ref_id , gltrace.ref_ser,gltrace.sundry_code ,gltrace.tran_id)),'-NA') as comp_ref, 
CASE WHEN SUNDRY_TYPE='O' THEN rtrim(DDF_SUNDRY_SH_NAME 
(ddf_get_sundrytype(nvl(GLTRACE.SUNDRY_TYPE,'O'),GLTRACE.SUNDRY_CODE,GLTRACE.REF_ID,GLTRACE.REF_SER,'A'),ddf_get_sundrytype(nvl(GLTRACE.SUNDRY_TYPE,'O'),GLTRACE.SUNDRY_CODE,GLTRACE.REF_ID,GLTRACE.REF_SER,'B'))) 

ELSE rtrim(DDF_SUNDRY_SH_NAME (SUNDRY_TYPE,SUNDRY_CODE)) END AS SH_NAME, 
CASE WHEN SUNDRY_TYPE='O' THEN DDF_SUNDRY_NAME 
(ddf_get_sundrytype(nvl(GLTRACE.SUNDRY_TYPE,'O'),GLTRACE.SUNDRY_CODE,GLTRACE.REF_ID,GLTRACE.REF_SER,'A'),ddf_get_sundrytype(nvl(GLTRACE.SUNDRY_TYPE,'O'),GLTRACE.SUNDRY_CODE,GLTRACE.REF_ID,GLTRACE.REF_SER,'B'),'D') 

ELSE DDF_SUNDRY_NAME (SUNDRY_TYPE,SUNDRY_CODE,'D') END  AS SUNDRY_NAME,CASE WHEN SUNDRY_TYPE='O' THEN FDF_GET_SUNDRY_DETAILS 
(ddf_get_sundrytype(nvl(GLTRACE.SUNDRY_TYPE,'O'),GLTRACE.SUNDRY_CODE,GLTRACE.REF_ID,GLTRACE.REF_SER,'A'),ddf_get_sundrytype(nvl(GLTRACE.SUNDRY_TYPE,'O'),GLTRACE.SUNDRY_CODE,GLTRACE.REF_ID,GLTRACE.REF_SER,'B'),'D') 

ELSE FDF_GET_SUNDRY_DETAILS (SUNDRY_TYPE,SUNDRY_CODE,'D') END AS 
SUNDRY_DETAILS,
'@location@' AS ORG_LOCN,
INVOICE.GP_NO,
INVOICE.GP_DATE,     
INVOICE.BL_NO,
INVOICE.BL_DATE FROM GLTRACE,INVOICE 
WHERE GLTRACE.REF_ID IN (SELECT REF_ID FROM GLTRACE 
WHERE  GLTRACE.TRAN_DATE >= TO_DATE('@trandatefrom@','dd/mm/yy') AND 
GLTRACE.TRAN_DATE <= TO_DATE('@trandateupto@','dd/mm/yy') AND 
GLTRACE.SITE_CODE in ('@sitecodefrom@','@sitecodeto@')  AND GLTRACE.SUNDRY_TYPE in 
(select distinct UDF_STR2 from gencodes where  MOD_NAME='RELT_CO') ) 
AND GLTRACE.TRAN_DATE >= TO_DATE('@trandatefrom@','dd/mm/yy') 
AND GLTRACE.TRAN_DATE <= TO_DATE('@trandateupto@','dd/mm/yy') 
AND GLTRACE.SITE_CODE in ('@sitecodefrom@','@sitecodeto@') 
AND INVOICE.INVOICE_ID= GLTRACE.REF_ID 
AND NVL(GLTRACE.SUNDRY_TYPE,'O') <> 'O' 
) GLTRACE 
WHERE GLTRACE.REF_ID IN ( CASE WHEN (SELECT COUNT(1) FROM 
INTERCOMP_RECO_TRACE WHERE (GLTRACE.REF_ID = REF_ID OR GLTRACE.REF_ID = REF_ID__TO) AND (GLTRACE.DR_AMT + GLTRACE.CR_AMT) = AMOUNT AND 
GLTRACE.TRAN_DATE = TRAN_DATE) > 0 THEN '' ELSE REF_ID END ) ORDER BY 
GLTRACE.TRAN_DATE,GLTRACE.REF_ID,CCTR_CODE,case when DR_AMT > 0 then 
DR_AMT else CR_AMT end
