SELECT t."Fund" ,
  t."Cost Center" as "CostCenter",
  DECODE(t."Cost Center" ,'8620',DECODE(t."Location",'0001','8620',t."Location") ,t."Location" ) "Location",
  t."Account" ,
  t."Program" ,
  t."Categories" ,
  t."Session" ,
  t."Project",
  DECODE(SUBSTR(t."Account",1,1) ,'6', t."Data"  ,NULL ) as "Debit",
  DECODE(SUBSTR(t."Account",1,1) ,'4', t."Data" ,'3', t."Data",NULL ) as "Credit",
  ('JAN-'||SUBSTR(t.YEAR,-2)) as "Period",
  ('20'||SUBSTR(t.YEAR,-2)||' Upload') as "Line Description"
FROM
 (SELECT SUBSTR(FUND,                     -3) "Fund" ,
    SUBSTR(ENTITY,                         -4) "Cost Center" ,
    DECODE(SUBSTR(NVL(LOCATION,'LOC_0001'),-4) ,'0001',NVL(
      (SELECT b.flex_value segment_value
    FROM fnd_flex_value_sets a,
      fnd_flex_values_vl b
    WHERE a.flex_value_set_id = b.flex_value_set_id
    AND b.enabled_flag        = 'Y'
    AND a.flex_value_set_name = 'FAMIS_Location'
    AND b.flex_value          =SUBSTR(stg.ENTITY,-4)
    AND ROWNUM                =1
    ) ,'0001') ,SUBSTR(NVL(LOCATION,'LOC_0001'),-4) ) "Location" ,
    ACCOUNT "Account" ,
    '00001' "Program" ,
    '01' "Categories" ,
    SUBSTR(GL_SESSION,-2) "Session" ,
    '00001' "Project" ,
    UPPER(Period) "Period" ,
    BUDGET "Data" ,
     DECODE(UPPER(Period),'JAN',1,'FEB',2,'MAR',3,'APR',4,'MAY',5,'JUN',6,'JUL',7,'AUG',8,'SEP',9,'OCT',10,'NOV',11,'DEC',12,'YEARTOTAL',0 ,13) AS "MON_NUM" ,
    SCENARIO,
    VERSION,
    YEAR
  FROM CPD_GL_FROM_HYP_ADBF_STG stg--CPD_GL_FROM_HYP_CSCV_STG stg
  WHERE 1          =1
  AND UPPER(Period)='YEARTOTAL'
  ORDER BY 1,2,3,4,5,6,7,8,11
  )t
WHERE 1=1