Some common query which i used in my work

 BEGIN

 DBMS_APPLICATION_INFO.set_client_info (<OU ID>);

 END;

 BEGIN

  fnd_global.apps_initialize (<v_num_user_id>,

                                        <v_num_resp_id>,

                                        <v_num_resp_appl_id>

                                       );
END;

--fetching value set value

SELECT TO_DATE (ffvt.description, 'MM/DD/YYYY HH:MI:SS AM'),

                ffvs.flex_value_set_id

           INTO v_dte_req_run_dte,

                v_num_value_set_id

           FROM fnd_flex_value_sets ffvs,

                fnd_flex_values ffv,

                fnd_flex_values_tl ffvt

          WHERE ffvs.flex_value_set_name = v_chr_run_dte_vset_name

            AND ffvs.flex_value_set_id = ffv.flex_value_set_id

            AND NVL (ffv.enabled_flag, 'N') = 'Y'

            AND TRUNC (v_dte_sys_date) BETWEEN NVL (ffv.start_date_active,

                                                    TRUNC (v_dte_sys_date - 1)

                                                   )

                                           AND NVL (ffv.end_date_active,

                                                    TRUNC (v_dte_sys_date + 1)

                                                   )

            AND ffv.flex_value = v_chr_run_dte_vset_value

            AND ffvt.LANGUAGE = USERENV ('LANG')

            AND ffvt.flex_value_id = ffv.flex_value_id;

-- checking the bug or patch was applied or not

  select patch_name                                                       

                  from ad_applied_patches           

                where patch_name LIKE '%7599031%'         

                union           

                select bug_number           

                  from ad_bugs           

                     where bug_number LIKE '%7599031%'

--DBMS_UTILITY.FORMAT_ERROR_STACK

--Checking all of profile value setup for one profile

SELECT pro.profile_option_name,

       pro.user_profile_option_name,

       lev.level_type TYPE,

       --lev.level_code,

       lev.level_name,

       prv.profile_option_value

FROM apps.fnd_profile_options_vl pro,

     applsys.fnd_profile_option_values prv,

     (SELECT 10001 level_id,

             'Site' level_type,

             0 level_value,

             'Site' level_code,

             'Site' level_name

      FROM dual

      UNION ALL

      SELECT 10002 level_id,

             'App' level_type,

             app.application_id level_value,

             app.application_short_name level_code,

             app.application_name level_name

      FROM apps.fnd_application_vl app

      UNION ALL

      SELECT 10003 level_id,

             'Resp' level_type,

             resp.responsibility_id level_value,

             resp.responsibility_key level_code,

             resp.responsibility_name level_name

      FROM apps.fnd_responsibility_vl resp

      UNION ALL

      SELECT 10004 level_id,

             'User' level_type,

             usr.user_id level_value,

             usr.user_name level_code,

             usr.user_name level_name

      FROM applsys.fnd_user usr) lev

WHERE pro.profile_option_id = prv.profile_option_id(+)

AND prv.level_id = lev.level_id(+)

AND prv.level_value = lev.level_value(+)

--AND pro.user_profile_option_name LIKE 'CUX%' --Profile name

AND pro.profile_option_name LIKE 'CUX%' --Profile short name

ORDER BY pro.profile_option_name, lev.level_type, lev.level_name;

--Cheking program details. which responsibility be used when submitted this request id.

SELECT fr.responsibility_name, app.application_name, fcr.oracle_process_id,

       fcp.user_concurrent_program_name, fcr.*

  FROM apps.fnd_concurrent_requests fcr,

       apps.fnd_responsibility_vl fr,

       apps.fnd_application_vl app,

       apps.fnd_concurrent_programs_vl fcp

 WHERE fcr.request_id = 287157452                        --<Your request id>

   AND fcr.responsibility_id = fr.responsibility_id

   AND fcr.responsibility_application_id = app.application_id

   AND fcr.concurrent_program_id = fcp.concurrent_program_id;

As we know, we can attach documents in different formats like WEBPAGE, FILE, LONG_TEXT and SHORT_TEXT.Hence, i had given queries to find the attached documents FOR EACH format separately.

-- WEBPAGE ATTACHMENT

 SELECT DISTINCT

  AD.SEQ_NUM                  ,

  DCT.USER_NAME               , 

  DAT.USER_NAME               ,

  D.STORAGE_TYPE              ,

  D.FILE_NAME                 ,

  D.IMAGE_TYPE                ,

  D.USAGE_TYPE                ,

  AD.AUTOMATICALLY_ADDED_FLAG ,

  AD.ATTACHED_DOCUMENT_ID     ,

  DET.USER_ENTITY_NAME        ,

  D.DATATYPE_ID               ,

  DAT.NAME                    ,

  D.DOCUMENT_ID               , 

  D.START_DATE_ACTIVE         ,

  D.END_DATE_ACTIVE           ,

  D.SECURITY_TYPE             ,

  D.SECURITY_ID               ,

  D.PUBLISH_FLAG              ,

  AD.CREATED_BY               ,

  DET.USER_ENTITY_PROMPT      ,

  AD.ENTITY_NAME              ,

  AD.COLUMN1                  ,

  AD.PK1_VALUE                , 

  D.MEDIA_ID                  ,

  D.CATEGORY_ID               ,

  D.URL                       ,

  DT.TITLE

FROM FND_DOCUMENT_DATATYPES DAT,

  FND_DOCUMENT_ENTITIES_TL DET    ,

  FND_DOCUMENTS_TL DT             ,

  FND_DOCUMENTS D                 ,

  FND_DOCUMENT_CATEGORIES_TL DCT  , 

  FND_ATTACHED_DOCUMENTS AD

WHERE D.DOCUMENT_ID        = AD.DOCUMENT_ID

AND DT.DOCUMENT_ID         = D.DOCUMENT_ID

AND DCT.CATEGORY_ID        = D.CATEGORY_ID

AND D.DATATYPE_ID          = DAT.DATATYPE_ID

AND AD.ENTITY_NAME         = DET.DATA_OBJECT_CODE

AND DAT.name               = 'WEB_PAGE';

 

-- LONG_TEXT ATTACHMENT

 SELECT

  AD.SEQ_NUM                  ,

  DCT.USER_NAME               , 

  DAT.USER_NAME               , 

  AD.ATTACHED_DOCUMENT_ID     ,

  DET.USER_ENTITY_NAME        , 

  DAT.NAME                    ,

  D.DOCUMENT_ID               , 

  AD.ENTITY_NAME              , 

  AD.PK1_VALUE                , 

  D.MEDIA_ID                  , 

  D.URL                       ,

  DT.TITLE                    ,

  DLT.LONG_TEXT

FROM FND_DOCUMENT_DATATYPES DAT,

  FND_DOCUMENT_ENTITIES_TL DET    ,

  FND_DOCUMENTS_TL DT             ,

  FND_DOCUMENTS D                 ,

  FND_DOCUMENT_CATEGORIES_TL DCT  , 

  FND_ATTACHED_DOCUMENTS AD       ,

  FND_DOCUMENTS_LONG_TEXT DLT    

WHERE D.DOCUMENT_ID       = AD.DOCUMENT_ID

AND DT.DOCUMENT_ID        = D.DOCUMENT_ID

AND DCT.CATEGORY_ID       = D.CATEGORY_ID

AND D.DATATYPE_ID         = DAT.DATATYPE_ID

AND AD.ENTITY_NAME        = DET.DATA_OBJECT_CODE

AND DLT.MEDIA_ID          = D.MEDIA_ID

AND DAT.NAME              = 'LONG_TEXT';

-- SHORT_TEXT ATTACHMENT

SELECT

  AD.SEQ_NUM                  ,

  DCT.USER_NAME               , 

  DAT.USER_NAME               , 

  AD.ATTACHED_DOCUMENT_ID     ,

  DET.USER_ENTITY_NAME        , 

  DAT.NAME                    ,

  D.DOCUMENT_ID               , 

  AD.ENTITY_NAME              , 

  AD.PK1_VALUE                , 

  D.MEDIA_ID                  , 

  D.URL                       ,

  DT.TITLE                    ,

  DST.SHORT_TEXT

FROM FND_DOCUMENT_DATATYPES DAT,

  FND_DOCUMENT_ENTITIES_TL DET    ,

  FND_DOCUMENTS_TL DT             ,

  FND_DOCUMENTS D                 ,

  FND_DOCUMENT_CATEGORIES_TL DCT  , 

  FND_ATTACHED_DOCUMENTS AD       ,

  FND_DOCUMENTS_SHORT_TEXT DST    

WHERE D.DOCUMENT_ID          = AD.DOCUMENT_ID

AND DT.DOCUMENT_ID           = D.DOCUMENT_ID

AND DCT.CATEGORY_ID          = D.CATEGORY_ID

AND D.DATATYPE_ID            = DAT.DATATYPE_ID

AND AD.ENTITY_NAME           = DET.DATA_OBJECT_CODE

AND DsT.MEDIA_ID             = D.MEDIA_ID

AND DAT.NAME                 = 'SHORT_TEXT';

-- FILE ATTACHMENT

SELECT

  AD.SEQ_NUM                  ,

  DCT.USER_NAME               , 

  DAT.USER_NAME               , 

  AD.ATTACHED_DOCUMENT_ID     ,

  DET.USER_ENTITY_NAME        , 

  DAT.NAME                    ,

  D.DOCUMENT_ID               , 

  AD.ENTITY_NAME              , 

  AD.PK1_VALUE                , 

  D.MEDIA_ID                  , 

  D.URL                       ,

  DT.TITLE                    ,

  DBMS_LOB.SUBSTR(L.file_data,1,10) file_data

FROM FND_DOCUMENT_DATATYPES DAT,

  FND_DOCUMENT_ENTITIES_TL DET    ,

  FND_DOCUMENTS_TL DT             ,

  FND_DOCUMENTS D                 ,

  FND_DOCUMENT_CATEGORIES_TL DCT  , 

  FND_ATTACHED_DOCUMENTS AD       ,

  FND_LOBS  L                         

WHERE D.DOCUMENT_ID          = AD.DOCUMENT_ID

AND DT.DOCUMENT_ID           = D.DOCUMENT_ID

AND DCT.CATEGORY_ID          = D.CATEGORY_ID

AND D.DATATYPE_ID            = DAT.DATATYPE_ID

AND AD.ENTITY_NAME           = DET.DATA_OBJECT_CODE

AND L.FILE_ID                = D.MEDIA_ID

AND DAT.name                 = 'FILE';

-- This query will help you to find the functions using attachments

SELECT

  AD.SEQ_NUM                  ,

  DCT.USER_NAME               , 

  DAT.USER_NAME               , 

  AD.ATTACHED_DOCUMENT_ID     ,

  DET.USER_ENTITY_NAME        , 

  DAT.NAME                    ,

  D.DOCUMENT_ID               , 

  AD.ENTITY_NAME              , 

  AD.PK1_VALUE                , 

  D.MEDIA_ID                  , 

  D.URL                       ,

  DT.TITLE                    ,

  AF.FUNCTION_NAME            ,

  AF.FUNCTION_TYPE             

FROM FND_DOCUMENT_DATATYPES DAT,

  FND_DOCUMENT_ENTITIES_TL DET    ,

  FND_DOCUMENTS_TL DT             ,

  FND_DOCUMENTS D                 ,

  FND_DOCUMENT_CATEGORIES_TL DCT  ,

  FND_DOC_CATEGORY_USAGES DCU     ,

  FND_ATTACHMENT_FUNCTIONS AF     ,

  FND_ATTACHED_DOCUMENTS AD

WHERE 1 = 1

AND D.DOCUMENT_ID              = AD.DOCUMENT_ID

AND DT.DOCUMENT_ID             = D.DOCUMENT_ID

AND DCT.CATEGORY_ID            = D.CATEGORY_ID

AND DCU.CATEGORY_ID            = D.CATEGORY_ID

AND DCU.ATTACHMENT_FUNCTION_ID = AF.ATTACHMENT_FUNCTION_ID

AND D.DATATYPE_ID              = DAT.DATATYPE_ID

AND AD.ENTITY_NAME             = DET.DATA_OBJECT_CODE

AND DCU.ENABLED_FLAG           = 'Y';

--replace the junk data when we use PL/SQL to generate the XML file data source  for BI Publisher report
REPLACE

                  (REPLACE

                      (REPLACE

                          (REPLACE

                              (REPLACE

                                  (REPLACE

                                        (REPLACE (REPLACE (vc.email_address,

                                                           '&',

                                                           CHR (38) || 'amp;'

                                                          ),

                                                  '"',

                                                  CHR (38) || 'quot;'

                                                 ),

                                         '<',

                                         CHR (38) || 'lt;'

                                        ),

                                   '>',

                                   CHR (38) || 'gt;'

                                  ),

                               '''',

                               CHR (38) || '#39;'

                              ),

                           '°',

                           ''

                          ),

                       '?',

                       ''

                      ),

                   '?',

                   ''

                  )

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/13975809/viewspace-2131649/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/13975809/viewspace-2131649/