Oracle EBS WEBADI集成器与内部名称及实体名称信息查询SQL

Oracle EBS WEBADI集成器与内部名称及实体名称信息查询SQL

select  

 intgb.INTEGRATOR_CODE 内部编码

            ,intgb.user_name AS 集成器名称

            ,intgb.application_id AS application_id

            ,fav.application_name AS 应用名称

          --  ,intgb.integrator_code AS integrator_code

            ,CASE

               WHEN (instr(intgb.integrator_code

                        ,'_XINTG'

                        ,1) > 0) THEN

               REPLACE(intgb.integrator_code

                     ,'_XINTG'

                     ,'')

               ELSE

               intgb.integrator_code

             END AS 内部名称

,bneinterfacesbeo.application_id   应用ID

     ,bneinterfacesbeo.interface_code    接口编码

     ,bneinterfacesbeo.integrator_code  内部编码

     ,bneinterfacesbeo.interface_name  接口名称

     ,(CASE bneinterfacesbeo.upload_type

        WHEN 0 THEN

         'Custom'

        WHEN 1 THEN

         'Table'

        WHEN 2 THEN

        initcap(bneattributeseo.attribute1)

        WHEN 3 THEN

         'Validation Only'

        WHEN 4 THEN

         'None'

        WHEN 5 THEN

         'Reference PL/SQL API'

        ELSE

         'Invalid Type'

      END) AS 类型     

  ,(CASE bneinterfacesbeo.upload_type

        WHEN 0 THEN

        bneinterfacesbeo.upload_obj_name

        WHEN 2 THEN

        bneattributeseo.attribute2

        ELSE

        bneinterfacesbeo.interface_name

      END) AS 实体名称   

  FROM bne_integrators_tl intgb

     ,fnd_application_vl  fav

     ,bne_interfaces_vl  bneinterfacesbeo

     ,bne_attributes    bneattributeseo

     ,bne_param_lists_vl bneparamlistsbeo

 WHERE 1=1 

 AND intgb.application_id = fav.APPLICATION_ID

   AND intgb.application_id = bneinterfacesbeo.application_id 

   AND intgb.integrator_code  = bneinterfacesbeo.integrator_code

   AND bneparamlistsbeo.param_list_code(+) = bneinterfacesbeo.upload_param_list_code

   AND bneparamlistsbeo.application_id(+) = bneinterfacesbeo.upload_param_list_app_id

   AND bneattributeseo.attribute_code(+) = bneparamlistsbeo.attribute_code

   AND bneattributeseo.application_id(+) = bneparamlistsbeo.attribute_app_id

   AND intgb.LANGUAGE = 'ZHS'

  AND intgb.user_name LIKE 'CUX%'

--刘轶鹤