Concurrent Programs Queries

--concurrent program details 

SELECT CPTL.USER_CONCURRENT_PROGRAM_NAME,
       CPA.APPLICATION_SHORT_NAME CP_APPL,      
       CPTL.CREATION_DATE,
       CP.CONCURRENT_PROGRAM_NAME,
       CP.LAST_UPDATED_BY,
       CP.LAST_UPDATE_DATE,
       CP.CONCURRENT_PROGRAM_ID,
       CP.ENABLED_FLAG,
       CP.EXECUTION_METHOD_CODE,
       EC.LOOKUP_TYPE,
       EC.MEANING,
       E.EXECUTABLE_NAME,
       E.EXECUTION_FILE_NAME,
       CP.OUTPUT_FILE_TYPE,
       E.EXECUTION_FILE_PATH,
       EA.APPLICATION_SHORT_NAME EXE_APPL
  FROM APPS.FND_CONCURRENT_PROGRAMS_TL CPTL,
       APPS.FND_CONCURRENT_PROGRAMS    CP,
       APPS.FND_EXECUTABLES            E,
       APPS.FND_LOOKUP_VALUES          EC,
       APPS.FND_APPLICATION            CPA,
       APPS.FND_APPLICATION            EA
 WHERE EA.APPLICATION_ID = E.APPLICATION_ID
   AND CPA.APPLICATION_ID = CP.APPLICATION_ID
   AND CP.CONCURRENT_PROGRAM_ID = CPTL.CONCURRENT_PROGRAM_ID
   AND E.EXECUTABLE_ID = CP.EXECUTABLE_ID
   AND EC.LOOKUP_CODE = E.EXECUTION_METHOD_CODE
   AND EC.LANGUAGE = CPTL.LANGUAGE
   AND ('&&conc_prog_name' IS NULL OR UPPER(CPTL.USER_CONCURRENT_PROGRAM_NAME) LIKE
       UPPER('&conc_prog_name') || '%')
   AND ('&&conc_prog_short_name' IS NULL OR
       UPPER(CP.CONCURRENT_PROGRAM_NAME) LIKE
       '%' || ('&conc_prog_short_name') || '%')
   AND CPTL.LANGUAGE = 'US'
   AND EC.LOOKUP_TYPE = 'CP_EXECUTION_METHOD_CODE'
   AND ('&&exec_file_name' IS NULL OR
       UPPER(E.EXECUTION_FILE_NAME) LIKE
       '%' || UPPER('&exec_file_name') || '%')
 ORDER BY CPTL.USER_CONCURRENT_PROGRAM_NAME;

SELECT CPTL.USER_CONCURRENT_PROGRAM_NAME,
       CR.DESCRIPTION,
       CR.REQUEST_DATE,
       CR.REQUESTED_BY,
       (SELECT USER_NAME 
          FROM APPS.FND_USER 
         WHERE USER_ID = CR.REQUESTED_BY) REQUEST_BY_USER,
       (SELECT RESPONSIBILITY_NAME
          FROM FND_RESPONSIBILITY_TL
         WHERE RESPONSIBILITY_ID = CR.RESPONSIBILITY_ID
           AND LANGUAGE = 'US'
       ) RESPONSIBILITY,
       CR.RESUBMIT_INTERVAL,
       CR.PHASE_CODE,
       CR.STATUS_CODE,
       CR.HOLD_FLAG,
       CR.REQUESTED_START_DATE,
       CR.REQUEST_ID,
       CR.ARGUMENT_TEXT
  FROM FND_CONCURRENT_REQUESTS CR, 
       FND_CONCURRENT_PROGRAMS_TL CPTL
 WHERE CR.STATUS_CODE IN ('Q', 'I') AND 
       CR.REQUESTED_START_DATE > SYSDATE AND
       CPTL.CONCURRENT_PROGRAM_ID = CR.CONCURRENT_PROGRAM_ID AND
       CPTL.LANGUAGE = 'US'
 ORDER BY CPTL.USER_CONCURRENT_PROGRAM_NAME, CR.REQUESTED_START_DATE DESC;

Query to find valid categories in a Category Set:

SELECT mc.concatenated_segments,mc.description
  FROM apps.mtl_category_set_valid_cats mcsvc
      ,apps.mtl_category_sets mcs
      ,apps.mtl_categories_kfv mc
 WHERE mcsvc.category_set_id = mcs.category_set_id
   AND mc.category_id = mcsvc.category_id
   AND mcs.category_set_name = '<category_set_name>';

Query to find manufacturer part number for an item:

SELECT mm.manufacturer_name,
       mp.mfg_part_num,
       mp.description,
       msi.segment1 inv_item,
       msi.description item_desc,
       mp.approval_status
  FROM apps.mtl_system_items msi,
       apps.mtl_mfg_part_numbers mp,
       apps.mtl_manufacturers mm,
       apps.org_organization_definitions o
WHERE mm.manufacturer_id = mp.manufacturer_id
  AND mp.inventory_item_id = msi.inventory_item_id
  AND mp.organization_id   = msi.organization_id
  AND msi.segment1 = '<item_number>'
  AND msi.organization_id = o.organization_id
  AND o.organization_code = '<org_code>';