2015/03/06|16:26:05|a6222|663|NAPI|MSSW|SR_ASMT_PKG.FIND_SR_NEEDS|449|DEBUG|Debu
g Msg|enamem_id: 611643
i_responsible_orgnbr :
i_lah_cd             : LAH
i_rv_cd              : RVO
i_comp_pri_id        :
i_asgloc_txt         :
i_comp_txt           :
i_has_notes_yn       :
i_need               :
i_pct_filled         :
i_need_mo_low        : -2
i_need_mo_high       : 4
i_max_cost_usd       :
i_min_med_id         :
i_req_rcmd_yn        :
i_hide_car_rqd_yn    :
i_6mo_cd             :
i_within_msn_txt     :
i_constraints_txt    :
2015/03/06|16:26:05|a6222|663|NAPI|MSSW|SR_ASMT_PKG.FIND_SR_NEEDS|515|DEBUG|Debu
g Msg|misstyp_id: 3
msny_home_country_id: 799
msny_ins_rtg: 2
msny_ins_cost:
 180
spouse_ins_rtg: 2
spouse_ins_cost: 180
avail_dt: 2015Aug01
cand_citz_country_id: 799
spouse_citz_country_id: 799
mail_days: 3
msny_pport_days: 45
spouse_pport_days: 45
2015/03/06|16:26:05|a6222|663|NAPI|MSSW|SR_ASMT_PKG.FIND_SR_NEEDS|702|DEBUG|Debu
g Msg|SELECT n.*
      ,n.month_cost + NVL(n.ins_cost,0) AS total_month_cost

  FROM (
SELECT sanv.*
      ,ROUND((sanv.compl_num -
 sanv.need)/sanv.compl_num,2)*100 AS pct_filled
      ,CASE WHEN 2 IN (0,1,4,5)
 THEN 0
            WHEN 2 = 2 THEN 180
            WHEN 2 IN (3,6) THEN

         CASE WHEN 799 IN (sanv.asgloc_country_id, sanv.asgloc_poldiv_id) THEN
 0
                    ELSE 180
               END
       END +
       CASE
 WHEN 2 IN (0,1,4,5) THEN 0
            WHEN 2 = 2 THEN 180
            WHEN 2
 IN (3,6) THEN
               CASE WHEN 799 IN (sanv.asgloc_country_id,
 sanv.asgloc_poldiv_id) THEN 0
                    ELSE 180
               END

       ELSE 0
       END AS ins_cost
      ,need_dt - (3
                  +
 GREATEST(DECODE(ntnv.ignore_passport_days_yn,'Y',0,45)

     ,DECODE(ntnvw.ignore_passport_days_yn,'Y',0,45))
                  +
 GREATEST(ntnv.long_term_visa_days_num,NVL(ntnvw.long_term_visa_days_num,0))

                + sanv.train_days) AS call_by_dt
  FROM sr_asmt_needs_vw sanv

 JOIN (SELECT DISTINCT orig_id
          FROM poldiv_origins
         WHERE
 poldivid_country IN (799, 799)
       ) pd ON pd.orig_id = sanv.orig_id
  JOIN
 nation_to_nation_params ntnv ON ntnv.from_country_poldivid = 799

                      AND ntnv.to_country_poldivid = sanv.asgloc_country_id

 LEFT JOIN nation_to_nation_params ntnvw ON ntnvw.from_country_poldivid = 799

                                        AND ntnvw.to_country_poldivid =
 sanv.asgloc_country_id
       ) n -- needs
  LEFT JOIN napi_asg_recs nar ON
 nar.enamem_id = :i_emem_id AND nar.comp_id = n.comp_id
 WHERE 1 = 1
   AND
 n.misstyp_id = :l_misstyp_id
   AND nar.comp_id IS NULL
   AND (1=1 OR
 :i_responsible_orgnbr IS NULL OR :i_responsible_orgnbr IS NULL)
   AND
 n.comp_housing_id = 2
   AND (1=1 OR :i_comp_pri_id IS NULL)
   AND (1=1 OR
 :i_asgloc_txt IS NULL)
   AND (1=1 OR :i_comp_txt IS NULL OR :i_comp_txt IS
 NULL OR :i_comp_txt IS NULL)
   AND (1=1 OR :i_need IS NULL)
   AND (1=1 OR
 :i_pct_filled IS NULL)
   AND n.need_dt >= ADD_MONTHS(:l_avail_dt,
 :i_need_mo_low)
   AND n.need_dt <= ADD_MONTHS(:l_avail_dt, :i_need_mo_high)

  AND (1=1 OR :i_max_cost_usd IS NULL)
   AND (1=1 OR :i_min_med_id IS NULL)

 AND (1=1 OR :i_within_msn_txt IS NULL OR :i_within_msn_txt IS NULL)
   AND
 (1=1 OR :i_constraints_txt IS NULL)
ORDER BY comp_priority_id, pct_filled,
 asgloc_nm