This discussion is archived
6 Replies Latest reply: Jun 20, 2013 5:58 AM by jwellsnh RSS

Report Problem need to fix the code

1013278 Newbie
Currently Being Moderated

Hello Expert

 

I am new to apex, I was given a task to interpret the code and fix the problem.

 

This Apex application has a list of value where the user select the institution as a drop down list menu. After selecting the institution the main report below the drop down list will be populated based on the selection. The problem is that when the user insert record for standalone program, this insert don't appear on the report when the user select the instituion.

can someone look at the code below and explain to me line by line what it does and how can I twick it to solve this problem? I am cloue less and I need expert help

 

select dt.* 

         ,case when dt.delivery_location is null then null

                  else htf.anchor ('javascript:void(0);'

                                          ,'<img src=#APP_IMAGES#location.png

                                            title=''' ||dt.delivery_location || '''

                                            alt=''' ||dt.delivery_location || '''

                                            height=24 width=24/>'

                                         ) 

          end dl_hover

  from (

select

        CASE WHEN INDEP_DEGREE = 'Y' THEN

        CASE WHEN Dt.DEGREE_ACRONYM IN ('AACC','ASCC') then

          da.DESCRIPTION   || ' with an Emphasis Area of ' ||

                 NVL(Mt.DESCRIPTION,cC.DESCRIPTION)

            ELSE    

         nvl(dt.description, da.DESCRIPTION)

         END

         ELSE

            CASE WHEN Dt.DEGREE_LEVEL IN ('V','A') THEN

                 nvl(dt.description, da.DESCRIPTION )  || ' with an Option in ' ||

                 NVL(Mt.DESCRIPTION,Cc.DESCRIPTION)

            WHEN Dt.DEGREE_LEVEL IN ('E','C','Z','F')  THEN

                 nvl(dt.description, da.DESCRIPTION )  || ' in ' ||

                 NVL(Mt.DESCRIPTION,Cc.DESCRIPTION)

            ELSE    

                 nvl(dt.description, da.DESCRIPTION )  || ' with a Major in ' ||

                 NVL(Mt.DESCRIPTION,Cc.DESCRIPTION)

            END

    END

degree_name

--,'???' emphasis_area

,nvl(mt.cip_code,dt.cip_code) cip_code

,nvl(mt.hours, dt.hours) total_credit_hours

-- ,dt.deactivated status

,case when nvl(mt.deactivated,dt.deactivated)  = 'A' then 'Active'

          when nvl(mt.deactivated,dt.deactivated) = 'D' then 'Deactivated'

          when nvl(mt.deactivated,dt.deactivated) = 'T' then 'Terminated'

      WHEN NVL(mt.deactivated,dt.deactivated) = 'I'

      THEN 'Inactive'

          else null

   end status

--,dt.degree_level program_type

,dl.description program_type

,dt.coop_indicator coop_indicator

,nvl(mt.approval_date,dt.approval_date) approval_date

,nvl(mt.implemented,dt.implemented) implemented

,nvl(mt.implementation_date, dt.implementation_date) implementation_date

,nvl(mt.delivery_mode ,dt.delivery_mode) delivery_mode

,(select rtrim(replace(replace(xmlagg(xmlelement("C" ,c.cixxvext_name)).getclobval() ,'<C>' ,'') ,'</C>' ,'&#xD; ') ,'&#xD; ') C

  from degree_transaction_details dtd

         ,cixxvext c

  where (dtd.degree_transaction_id = case when INDEP_DEGREE= 'Y' then dt.degree_id else mt.major_id end )

      and c.cixxvext_ext_site_cd = dtd.detail_value

and dtd.record_type= case when INDEP_DEGREE = 'Y' then 'DEGREE' else 'MAJOR' end ) delivery_location

,dt.degree_id degree_id

,'Comparison Report' comparison_report

,apex_util.prepare_url ('f?p=&APP_ID.:2:&SESSION.::&DEBUG.:2:P2_FICE_CODE,P2_DEGREE_ID:&P1_FICE_CODE.,'||dt.degree_id) edit_link

,apex_util.prepare_url ('f?p=&APP_ID.:4:&SESSION.::&DEBUG.:RP,4:P4_DEGREE_ID:'||dt.degree_id) cr_link

,dt.description

,mt.major_id major_id

--

,nvl(mt.online_percentage,dt.online_percentage) online_percent

,nvl(mt.last_inst_review,dt.last_inst_review) last_inst_review

from degree_transactions dt,

    degree_acronyms da,

    major_transactions mt,

    degree_levels dl,

    cip_codes cc

where dt.degree_id = mt.degree_id

  and mt.cip_code = cc.cip_code

  and dl.degree_level = nvl(mt.degree_level,dt.degree_level)

  and dt.degree_acronym = da.degree_acronym

  and dt.Fice_code = da.fice_code

  and dt.degree_level = da.degree_level

  and dt.deactivated in ('A','D')

  and mt.deactivated in ('A','D')

  and dt.fice_code = :P1_FICE_CODE

  and dt.show_inst = 'Y'

) dt

order by dt.description nulls first