6 Replies Latest reply: Jun 20, 2013 7:58 AM by jwellsnh RSS

    Report Problem need to fix the code

    1013278

      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