select 
  pom.facility_number,
  case when pom.revol =2 then
    case when pom.qzdate::text < pom.draw_down_date::text then 
      pom.Total_OS + pom.AVAILABLE_LIMIT
    else 
      pom.Total_OS 
    end
  else 
    pom.EXISTING_LIMIT
  end as push ,
  case when pom.revol =2 then
    case  when pom.qzdate::text < pom.draw_down_date::text then 
      pom.Total_OS + pom.AVAILABLE_LIMIT + pom.change_limit
    else 
      pom.Total_OS + pom.change_limit
    end
  else
    pom.PROPOSED_LIMIT
  end as pull

from
(SELECT DISTINCT
  f.facility_number,
  ( select qz_dt as qzdatee from qz_dates where ou_id = 'G') qzdate,
  COALESCE( FDTL.PND_AVAIL_END_DATE, FDTL.MATURITY_DATE ) draw_down_date,
  COALESCE( f.LOAN_TYPE, ba_prod.MOF_FLAG ) revol,
  trunc(LIM.PNDG_LIMIT_AMOUNT,2) PROPOSED_LIMIT,
  app.type::bigint app_type,
  trunc(LIM.PNDG_LIMIT_BASE_AMOUNT,2) EXISTING_LIMIT,
  trunc(COALESCE( COALESCE( LIM.PNDG_LIMIT_BASE_AMOUNT, 0 )  - COALESCE( LIM.limit_base_amount, 0 ), 0 ),2) change_limit,
  trunc(COALESCE(prod.principal_outstanding,0)+COALESCE(prod.profit_outstanding,0),2) Total_OS,
  trunc(COALESCE( COALESCE( LIM.PNDG_RES_LIMIT_AMOUNT, 0 ) - COALESCE( LIM.GROSS_EXP_AMOUNT, 0 ) - COALESCE( LIM.HOLD_AMOUNT, 0 ), 0 ),2) AVAILABLE_LIMIT
FROM
 
  bus_clos_facility         f left join BUS_CLOS_CONDITION con
    on( con.owner_id = f.facility_number
and con.app_number=0
and con.internal_cond = 3 AND con.DELETE_FLAG != 1)
left join mst_cb_ba_product_type ba_prod on (ba_prod.ba_prodtype_id    = f.facility_type_id)
left join bus_clos_fac_product_dtls prod on (prod.facility_id  = f.facility_number AND prod.app_number  = f.app_number  )
left join BUS_CLSE_ACCOUNT_MAPPING mapp on (f.facility_number = mapp.facility_id),
    bus_clos_limit            lim,
    bus_clos_application      app,    
    bus_clos_facility_details fdtl,
    BUS_CLOS_MDR MDR,
    BUS_CLOS_MDR_DETAILS MDR_DTLS,
    bus_clos_cp cor
    
WHERE 
  fdtl.facility_number = f.facility_number
    AND f.PNDG_MDR_ID    = MDR.MDR_ID
AND f.APP_NUMBER     = MDR.APP_NUMBER
    and f.org_number = 100003280
--    and f.facility_number = inp_fac
    AND fdtl.app_number = f.app_number
    AND f.PNDG_MDR_ID    = MDR.MDR_ID
    AND f.APP_NUMBER     = MDR.APP_NUMBER    
    AND lim.owner_id  = f.facility_number
    AND lim.app_number  = f.app_number
    AND lim.owner_type  = 2 
    AND f.frf                   = '6'
    AND MDR.MDR_STATUS    IN (  2, 4, 8, 9, 12, 24 )
    AND mdr_dtls.APP_NUMBER= mdr.APP_NUMBER
    AND MDR.MDR_ID         = MDR_DTLS.MDR_ID
    AND COR.CIF_NUMBER     = MDR_DTLS.CIF_NUMBER
    and cor.app_number  =0
    and f.app_number = cor.app_number
    
    AND COR.CIF_NUMBER     = app.cif_number
    )pom