The Weird and The Wonderful

GoTo Error_Trap
            End Try

            Me.LBL_status.Text = ""
        Else
            Me.DV_Search.Visible = True
            Me.DV_Results.Attributes("style") = "display:none"
            Me.LBL_status.Text = "No Rows Returned."
        End If

        Exit Sub

Error_Trap:
        ShowError()

Dim tbl As DataTable

        tbl = Common.GetDataTable(SQLScripts.GetBaseEmpSql(UserID))

Public Shared Function getSQL(ByVal aryPVIDs() As String) As String
        'aryPVIDs must be an array of pvids 
        Dim sql, strSelect, strFrom, strWhere As String
        Dim strPVIDs As String

        strPVIDs = Join(aryPVIDs, "','")

        strSelect = "Select p.ID, p.Ps_Project_NBr, " & vbCrLf & _
        " p.Project_Desc_txt, pt.Project_type_txt, p.project_type_cd, " & _
        " p.spread_method_cd, sm.spread_method_txt, p.project_mgr_id, pm.last_nm||', '||pm.first_nm ProjectMgrFullName, " & vbCrLf & _
        " p.asset_mgr_id, am.last_nm||', '||am.first_nm AssetMgrFullName, " & vbCrLf & _
        " p.lead_eng_id, le.last_nm||', '||le.first_nm LeadEngineerFullName, " & vbCrLf & _
        " p.start_dt, p.revised_start_dt, " & vbCrLf & _
        " p.orig_serv_dt, p.revised_serv_dt, p.projected_serv_dt, " & vbCrLf & _
        " p.host_flg, p.engineer_group_cd, eg.engineer_group_txt, " & vbCrLf & _
        " p.Trans_region_cd, tr.transmission_region_txt, p.comments_txt, " & vbCrLf & _
        " p.Distr_region_cd, dr.distr_region_txt, " & vbCrLf & _
        " p.project_status_cd, priority_cd , archive_flg, " & vbCrLf & _
        " ps.cpp, p.glbu, ps.ci_status_nbr ci_status, capital_flg, p.funding_type_cd, " & vbCrLf & _
        " ps.funding_id, ft.funding_type_desc, " & vbCrLf & _
        " p.outsourced_flg, p.asset_type_cd, at.asset_type_desc, p.asset_class_cd, ac.asset_class_desc, " & vbCrLf & _
        " p.voltage_class_cd, vc.voltage_class_desc, p.outsourced_cd, o.outsourced_desc, " & vbCrLf & _
        " p.budget_ind, pstat.project_status_txt, p.super_project, p.last_updated_by, p.last_updated_dt, " & vbCrLf & _
        " (Select count(1) from xxx.yyy where id = p.ID) as mn_est_cnt, " & vbCrLf & _
        " (Select count(1) from xxx.yyy where id = p.ID) as yr_est_cnt, " & vbCrLf & _
        " p.created_by, cb.first_nm||' '||cb.last_nm CreatedByFullName, pf.last_updated_dt forecast_upd_dt, pf.last_updated_by forecast_upd_by, " & vbCrLf & _
        " (select decode(count(*),0,0,1)  from xxx.zzz where id = p.id) HasReimbursable, " & vbCrLf & _
        " (SELECT first_nm || ' ' || last_nm FROM aaa WHERE employee_id = p.last_updated_by) lastUpdatedName, " & vbCrLf & _
        " (SELECT first_nm || ' ' || last_nm FROM aaa WHERE employee_id = p.created_by) createdByName, " & vbCrLf & _
        " get_states_by_project(p.id) as state_cd " & vbCrLf


        strFrom = " from xxx.aaa p, xxx.bbb PT, xxx.aaa pm, " & vbCrLf & _
        " xxx.aaa le, xxx.ccc eg, xxx.ddd tr, " & vbCrLf & _
        " xxx.aaa am, xxx.aaa cb, xxx.eee sm, " & vbCrLf & _
        " xxx.fff ps, xxx.ggg dr, " & vbCrLf & _
        " xxx.hhh ac, xxx.iii at, xxx.ccc vc, " & vbCrLf & _
        " xxx.jjj o, xxx.kkk ft, " & vbCrLf & _
        " xxx.lll pstat, " & _
          " (SELECT DISTINCT t1.id, t1.last_updated_dt, e.first_nm||' '||e.last_nm last_updated_by " & _
         " FROM xxx.project_forecast t1, xxx.aaa e " & _
         " WHERE e.employee_id = t1.last_updated_by and (t1.id, t1.last_updated_dt) IN " & _
         " (SELECT id, max(last_updated_dt) FROM mmm" & _
         " WHERE id IN ('" & strPVIDs & "') GROUP BY id   ) ) pf "


        strWhere = " where pf.id(+) = p.id and p.id in ('" & strPVIDs & "') and pt.project_type_cd(+) = p.project_type_cd " & vbCrLf & _
        " and p.Project_Mgr_Id = pm.Employee_ID(+) and p.created_by = cb.Employee_Id(+) " & vbCrLf & _
        " and p.Asset_Mgr_Id = am.Employee_Id(+) and p.spread_Method_cd = sm.spread_Method_cd(+) " & vbCrLf & _
        " and p.Lead_Eng_ID =  le.Employee_Id(+)  and p.trans_region_cd = tr.transmission_region_cd(+) " & vbCrLf & _
        " and p.engineer_group_cd = eg.engineer_group_cd(+) " & vbCrLf & _
        " and p.ps_project_nbr = ps.ps_project_nbr(+)  and p.distr_region_cd = dr.distr_region_cd(+) " & vbCrLf & _
        " and p.asset_type_cd = at.asset_type_cd(+) and p.asset_class_cd = ac.asset_class_cd(+) " & vbCrLf & _
        " and p.voltage_class_cd = vc.voltage_class_cd(+) " & vbCrLf & _
        " and p.outsourced_cd = o.outsourced_cd(+) " & vbCrLf & _
        " and p.funding_type_cd = ft.funding_type_cd(+) " & vbCrLf & _
        " and p.project_status_cd = pstat.project_status_cd(+) "

        sql = strSelect & strFrom & strWhere

        Return sql
    End Function