text: SELECT BENEFICIARY,
  category,
  DEBIT_AMOUNT,
  SHORT_AMOUNT,
  LONG_MEDIUM_AMOUNT,
  SIGN_AMT,
  TRIM(LPAD(DEBIT_AMOUNT+SHORT_AMOUNT+LONG_MEDIUM_AMOUNT+SIGN_AMT,8,'0')) TOTAL_AMT
  from (
  SELECT RPAD(BENEFICIARY,50,'*') BENEFICIARY,
  decode (BENEFICIARY,'IBD (AWANGA ZACHARIA)','2',
  'AZAM HOTEL (AWANGA ZACHARIA)','2',
  'AZAM HOTEL (AWANGA ZACHARIA)','2',
  'AZAM HOTEL (AWANGA ZACHARIA)','2',
  'CCSS (AWANGA ZACHARIA)','2',
  'METRO SECURITY (ROBERT ATAH AWANGA)','3',
  'METRO SECURITY (ROBERT ATAH AWANGA)','3','1') category,
  DEBIT_AMOUNT,
  SHORT_AMOUNT,
  LONG_MEDIUM_AMOUNT,
  SIGN_AMT
  from (
  SELECT BENEFICIARY,
  TRIM(LPAD(ROUND(ABS(SUM(NVL(DEBIT_AMOUNT,0))/1000000)),8,'0')) DEBIT_AMOUNT,
  TRIM(LPAD(ROUND(ABS(SUM(NVL(LONG_MEDIUM_AMOUNT,0))/1000000)),8,'0')) LONG_MEDIUM_AMOUNT,
  TRIM(LPAD(ROUND(ABS(SUM(NVL(SHORT_AMOUNT,0))/1000000)),8,'0')) SHORT_AMOUNT,
  TRIM(LPAD(ROUND(ABS(SUM(NVL(SIGN_AMT,0))/1000000)),8,'0')) SIGN_AMT
  FROM
  (
  SELECT EE.CUSTOMER_NO,EE.CUSTOMER_NAME1 beneficiary,
  CASE 
  WHEN ((DD.ACCOUNT_NUMBER NOT IN (SELECT FF.ACCOUNT_NUMBER FROM CLTB_ACCOUNT_MASTER FF)) AND AMOUNT < 0 ) THEN
  (SUM(nvl(AMOUNT,decode(glcode2,account_number,gl_closing_balance,0)))) 
  ELSE
  0
  END DEBIT_AMOUNT,
  CASE 
  WHEN (GLCODE2 LIKE '32%') THEN
  (SUM(nvl(AMOUNT,decode(glcode2,account_number,gl_closing_balance,0)))) 
  END SHORT_AMOUNT,        
  CASE 
  WHEN ((GLCODE2 LIKE '30%') OR (GLCODE2 LIKE '31%'))THEN
  (SUM(nvl(AMOUNT,decode(glcode2,account_number,gl_closing_balance,0)))) 
  END LONG_MEDIUM_AMOUNT,
  CASE 
  WHEN ((GLCODE2 LIKE '92%'))THEN
  (SUM(nvl(AMOUNT,decode(glcode2,account_number,gl_closing_balance,0)))) 
  END SIGN_AMT
  FROM VW_TRIAL_BALANCE DD , STTM_CUSTOMER EE
  WHERE DD.CUSTOMER_ID = EE.CUSTOMER_NO 
  
  --AND DD.ACCOUNT_NUMBER NOT IN (SELECT FF.ACCOUNT_NUMBER FROM CLTB_ACCOUNT_MASTER FF)
  AND EE.STAFF = 'Y'
  GROUP BY EE.CUSTOMER_NO,EE.CUSTOMER_NAME1,AMOUNT,DD.ACCOUNT_NUMBER,GLCODE2)
  GROUP BY beneficiary
   union all
   (
   SELECT BENEFICIARY,
  TRIM(LPAD(ROUND(ABS(SUM(NVL(DEBIT_AMOUNT,0))/1000000)),8,'0')) DEBIT_AMOUNT,
  TRIM(LPAD(ROUND(ABS(SUM(NVL(LONG_MEDIUM_AMOUNT,0))/1000000)),8,'0')) LONG_MEDIUM_AMOUNT,
  TRIM(LPAD(ROUND(ABS(SUM(NVL(SHORT_AMOUNT,0))/1000000)),8,'0')) SHORT_AMOUNT,
  TRIM(LPAD(ROUND(ABS(SUM(NVL(SIGN_AMT,0))/1000000)),8,'0')) SIGN_AMT
  FROM
  (
  SELECT 
  decode (CUSTOMER_NO,'014620','IBD (AWANGA ZACHARIA)',
  '006514','AZAM HOTEL (AWANGA ZACHARIA)',
  '024497','AZAM HOTEL (AWANGA ZACHARIA)',
  '024568','AZAM HOTEL (AWANGA ZACHARIA)',
  '013046','CCSS (AWANGA ZACHARIA)',
  '013224','MARIO (ASHIME MARGARET EKWAYA)',
  '037933','METRO SECURITY (ROBERT ATAH AWANGA)',
  '013866','METRO SECURITY (ROBERT ATAH AWANGA)',
  '016849','SAFARI INTERNATIONAL (ABEY JEROME ONGEH)',
  '012921','GAS ENGINEERING (SUNA GREGORY)')  beneficiary,
  CASE 
  WHEN ((DD.ACCOUNT_NUMBER NOT IN (SELECT FF.ACCOUNT_NUMBER FROM CLTB_ACCOUNT_MASTER FF)) AND AMOUNT < 0 ) THEN
  (SUM(nvl(AMOUNT,decode(glcode2,account_number,gl_closing_balance,0)))) 
  ELSE
  0
  END DEBIT_AMOUNT,
  CASE 
  WHEN (GLCODE2 LIKE '32%' ) THEN
  (SUM(nvl(AMOUNT,decode(glcode2,account_number,gl_closing_balance,0)))) 
  END SHORT_AMOUNT,        
  CASE
  WHEN (((GLCODE2 LIKE '30%') OR (GLCODE2 LIKE '31%')) OR 
    (GLCODE2 LIKE '34%' AND (DD.ACCOUNT_NUMBER IN (SELECT FF.ACCOUNT_NUMBER FROM CLTB_ACCOUNT_MASTER FF)))) THEN
  (SUM(nvl(AMOUNT,decode(glcode2,account_number,gl_closing_balance,0)))) 
  END LONG_MEDIUM_AMOUNT,
  CASE 
  WHEN ((GLCODE2 LIKE '92%'))THEN
  (SUM(nvl(AMOUNT,decode(glcode2,account_number,gl_closing_balance,0)))) 
  END SIGN_AMT
  FROM VW_TRIAL_BALANCE DD , STTM_CUSTOMER EE
  WHERE DD.CUSTOMER_ID = EE.CUSTOMER_NO 
  and ee.customer_no in ('006514','024497','024568','014620','013046','013224','037933','013866','016849','012921')
  GROUP BY EE.CUSTOMER_NO,EE.CUSTOMER_NAME1,AMOUNT,DD.ACCOUNT_NUMBER,GLCODE2)
  GROUP BY beneficiary))
  where (DEBIT_AMOUNT > 0) or
  (LONG_MEDIUM_AMOUNT > 0) or 
  (SHORT_AMOUNT > 0) OR (SIGN_AMT>0))
  ORDER BY beneficiary