Query to find Patches and bugs for R12

 

Query to find Patches and bugs for R12

Two tables to check if the patch is applied or not:

This table includes the defined bugs on the system:
SELECT   bug_number
FROM     apps.ad_bugs
WHERE   bug_number LIKE '%' ||15880118|| '%';

This table includes patches applied on the system:
SELECT patch_name
FROM   apps.ad_applied_patches
WHERE patch_name LIKE '%' ||15880118|| '%'

Query to Get AVG, MAX , MIN Time of Concurren program.

select
b.user_name username,
a.USER_CONCURRENT_PROGRAM_NAME  as PNAME,
avg(((nvl(ACTUAL_COMPLETION_DATE,sysdate)-a.REQUESTED_START_DATE)*24)*60) avg_Hrs_running, 
max(((nvl(ACTUAL_COMPLETION_DATE,sysdate)-a.REQUESTED_START_DATE)*24)*60) Max_Hrs_running,
min((nvl(ACTUAL_COMPLETION_DATE,sysdate)-a.REQUESTED_START_DATE)*24) Min_Hrs_running
from
apps.fnd_conc_req_summary_v a,
apps.fnd_user  b
where
phase_code = 'C' and status_code = 'C' and
a.REQUESTED_START_DATE > sysdate-30 and
upper(a.USER_CONCURRENT_PROGRAM_NAME) like upper('concurrent program name%') and
a.REQUESTED_BY=b.user_id
and a.description is null
group by b.user_name,a.USER_CONCURRENT_PROGRAM_NAME;

Query to find request set and its Request group and Responsibility

SELECT FA.application_name,

       fr.responsibility_name program_attached_to,

       frg.request_group_name,

       fcp.request_set_name,

       fcp.user_request_set_name

  FROM apps.fnd_responsibility_vl   fr,

       apps.fnd_request_groups      frg,

       apps.fnd_request_group_units frgu,

       apps.fnd_request_Sets_vl     fcp,

       apps.fnd_application_vl      FA

 WHERE frg.request_group_id = fr.request_group_id

   AND frgu.request_group_id = frg.request_group_id

   AND fcp.request_set_id = frgu.request_unit_id

   AND fcp.application_id = FA.application_id

   AND upper(fcp.user_request_set_name) LIKE  UPPER('');

Query to get Order Types and its process flow

SELECT HAOU.NAME "Operating Unit",
         OTTT_H.NAME "Transaction Type",
         OTTT_H.DESCRIPTION "Description",
         DECODE (OTVL.SALES_DOCUMENT_TYPE_CODE,
                 'O', 'Sales Order',
                 OTVL.SALES_DOCUMENT_TYPE_CODE)
            "Sales Document Type",
         FLV_CAT.MEANING "Order Category",
         OTVL.TRANSACTION_TYPE_CODE "Transaction Type Code",
         WRPV_H.DISPLAY_NAME "Fulfillment Flow",
         NULL "Negotiation Flow",
            TO_CHAR (OTVL.START_DATE_ACTIVE, 'DD-MON-YYYY')
         || ' - '
         || TO_CHAR (OTVL.END_DATE_ACTIVE, 'DD-MON-YYYY')
            "Effective Dates",
         FLV_PHA.MEANING "Default Transaction Phase",
         LAYOUT_TEMPLATE_ID "Layout Template",
         CONTRACT_TEMPLATE_ID "Contract Template",
         OTVL.QUOTE_NUM_AS_ORD_NUM_FLAG "Retain Document Number",
         OTVL.AGREEMENT_TYPE_CODE "Agreement Type",
         OTVL.AGREEMENT_REQUIRED_FLAG "Agreement Required",
         OTVL.PO_REQUIRED_FLAG "Purchase Order Required",
         DECODE (OTVL.DEFAULT_INBOUND_LINE_TYPE_ID, NULL, NULL, OTTT_DEF.NAME)
            "Default Return Line Type",
         DECODE (OTVL.DEFAULT_OUTBOUND_LINE_TYPE_ID, NULL, NULL, OTTT_DEF.NAME)
            "Default Order Line Type",
         OTVL.ENFORCE_LINE_PRICES_FLAG "Enfore List Price",
         (SELECT NAME
            FROM APPS.QP_LIST_HEADERS_TL Q
           WHERE     LIST_HEADER_ID = OTVL.PRICE_LIST_ID
                 AND Q.LANGUAGE = OTTT_H.LANGUAGE)
            "Price List",
         OTVL.MIN_MARGIN_PERCENT "Min. Margin Percent",
         OTVL.ENTRY_CREDIT_CHECK_RULE_ID "Ordering Credit Check",
         OTVL.PICKING_CREDIT_CHECK_RULE_ID "Picking Credit Check",
         OTVL.PACKING_CREDIT_CHECK_RULE_ID "Packing Credit Check",
         OTVL.SHIPPING_CREDIT_CHECK_RULE_ID "Shipping Credit Check",
         (SELECT ORGANIZATION_CODE
            FROM APPS.MTL_PARAMETERS
           WHERE ORGANIZATION_ID = OTVL.WAREHOUSE_ID)
            "Warehouse",
         OTVL.SHIPPING_METHOD_CODE "Shipping Method",
         OTVL.SHIPMENT_PRIORITY_CODE "Shipment Priority",
         OTVL.FREIGHT_TERMS_CODE "Freight Terms",
         OTVL.FOB_POINT_CODE "FOB",
         OTVL.SHIP_SOURCE_TYPE_CODE "Shipping Source Type",
         OTVL.DEMAND_CLASS_CODE "Demand Class",
         OTVL.SCHEDULING_LEVEL_CODE "Scheduling Level",
         OTVL.INSPECTION_REQUIRED_FLAG "Inspection Required",
         OTVL.AUTO_SCHEDULING_FLAG "Auto Schedule",
         OTVL.DEFAULT_LINE_SET_CODE "Line Set",
         OTVL.DEFAULT_FULFILLMENT_SET "Fulfillment Set",
         OTVL.INVOICING_RULE_ID "Invoicing Rule",
         OTVL.ACCOUNTING_RULE_ID "Accouting Rule",
         (SELECT NAME
            FROM APPS.RA_BATCH_SOURCES_ALL
           WHERE     BATCH_SOURCE_ID = OTVL.INVOICE_SOURCE_ID
                 AND ORG_ID = OTVL.ORG_ID)
            "Invoice Source",
         (SELECT NAME
            FROM APPS.RA_BATCH_SOURCES_ALL
           WHERE     BATCH_SOURCE_ID = OTVL.NON_DELIVERY_INVOICE_SOURCE_ID
                 AND ORG_ID = OTVL.ORG_ID)
            "Non Delivery Invoice Source",
         OTVL.INVOICING_CREDIT_METHOD_CODE "Invoices With Rules",
         OTVL.ACCOUNTING_CREDIT_METHOD_CODE "Split Term Invoices",
         (SELECT NAME
            FROM APPS.RA_CUST_TRX_TYPES_ALL
           WHERE     CUST_TRX_TYPE_ID = OTVL.CUST_TRX_TYPE_ID
                 AND ORG_ID = OTVL.ORG_ID)
            "Recv. Trans. Type",
         OTVL.TAX_CALCULATION_EVENT_CODE "Tax Event",
         OTVL.COST_OF_GOODS_SOLD_ACCOUNT "COGS Account",
         OTVL.CURRENCY_CODE "Currency",
         (SELECT UsER_CONVERSION_TYPE
            FROM apps.GL_DAILY_CONVERSION_TYPES
           WHERE CONVERSION_TYPE = OTVL.CONVERSION_TYPE_CODE)
            "Conversion Type",
         OTTT_L.NAME "Line Type",
         OWA_L.ITEM_TYPE_CODE "Item Type",
         WRPV_L.DISPLAY_NAME "Process",
         TO_CHAR (OWA_L.START_DATE_ACTIVE, 'DD-MON-YYYY') "Start Date",
         TO_CHAR (OWA_L.END_DATE_ACTIVE, 'DD-MON-YYYY') "End Date"
    FROM APPS.OE_TRANSACTION_TYPES_ALL OTVL,
         APPS.HR_ALL_ORGANIZATION_UNITS HAOU,
         APPS.OE_TRANSACTION_TYPES_TL OTTT_H,
         APPS.FND_LOOKUP_VALUES FLV_CAT,
         APPS.OE_WORKFLOW_ASSIGNMENTS OWA_H,
         APPS.WF_RUNNABLE_PROCESSES_V WRPV_H,
         APPS.FND_LOOKUP_VALUES FLV_PHA,
         APPS.OE_TRANSACTION_TYPES_TL OTTT_DEF,
         APPS.OE_WORKFLOW_ASSIGNMENTS OWA_L,
         APPS.OE_TRANSACTION_TYPES_TL OTTT_L,
         APPS.WF_RUNNABLE_PROCESSES_V WRPV_L,
         DUAL
   WHERE     1 = 1
         AND OTVL.ORG_ID = HAOU.ORGANIZATION_ID
         AND OTVL.TRANSACTION_TYPE_CODE = 'ORDER'
         AND OTVL.TRANSACTION_TYPE_ID = OTTT_H.TRANSACTION_TYPE_ID
         AND (    OTVL.ORDER_CATEGORY_CODE = FLV_CAT.LOOKUP_CODE
              AND FLV_CAT.LOOKUP_TYPE IN ('ORDER_CATEGORY'))
         AND OTVL.TRANSACTION_TYPE_ID = OWA_H.ORDER_TYPE_ID
         AND (    OWA_H.PROCESS_NAME = WRPV_H.PROCESS_NAME
              AND OWA_H.WF_ITEM_TYPE = WRPV_H.ITEM_TYPE)
         AND (    OTVL.DEF_TRANSACTION_PHASE_CODE = FLV_PHA.LOOKUP_CODE(+)
              AND FLV_PHA.LOOKUP_TYPE(+) IN ('TRANSACTION_PHASE'))
         AND DECODE (
                OTVL.ORDER_CATEGORY_CODE,
                'ORDER', NVL (OTVL.DEFAULT_OUTBOUND_LINE_TYPE_ID,
                              OTVL.TRANSACTION_TYPE_ID),
                'RETURN', NVL (OTVL.DEFAULT_INBOUND_LINE_TYPE_ID,
                               OTVL.TRANSACTION_TYPE_ID)) =
                OTTT_DEF.TRANSACTION_TYPE_ID
         AND OTVL.TRANSACTION_TYPE_ID = OWA_L.ORDER_TYPE_ID
         AND OTTT_L.TRANSACTION_TYPE_ID = OWA_L.LINE_TYPE_ID
         AND (    OWA_L.PROCESS_NAME = WRPV_L.PROCESS_NAME
              AND WRPV_L.ITEM_TYPE = 'OEOL')
         AND OTTT_H.LANGUAGE = 'US'
         AND OTTT_H.LANGUAGE = FLV_CAT.LANGUAGE
         AND OTTT_H.LANGUAGE = OTTT_DEF.LANGUAGE
         AND OTTT_H.LANGUAGE = OTTT_L.LANGUAGE
         AND HAOU.NAME IN (SELECT DISTINCT NVL (PARAM_LIST, NAME)
                             FROM (    SELECT TRIM (REGEXP_SUBSTR (PARAMETER,
                                                                   '[^,]+',
                                                                   1,
                                                                   LEVEL))
                                                 PARAM_LIST,
                                              NULL
                                         FROM (SELECT TRIM (
                                                         DECODE ('ALL',
                                                                 'ALL', NULL,
                                                                 'ALL'))
                                                         PARAMETER
                                                 FROM DUAL) T
                                   CONNECT BY REGEXP_SUBSTR (PARAMETER,
                                                             '[^,]+',
                                                             1,
                                                             LEVEL)
                                                 IS NOT NULL) PARAM_TBL,
                                  (SELECT NULL, NAME
                                     FROM APPS.HR_ALL_ORGANIZATION_UNITS)
                                  BASE_TBL)
ORDER BY OTTT_H.NAME,
         OTTT_L.NAME,
         OWA_L.START_DATE_ACTIVE,
         OWA_L.ITEM_TYPE_CODE

Query to find Responsibilit and its Request group

SELECT DISTINCT frt.responsibility_name, frg.request_group_name,fr.application_id
    FROM fnd_Responsibility fr, fnd_responsibility_tl frt,
    fnd_request_groups frg
    WHERE frt.responsibility_id = fr.responsibility_id
    AND frg.request_group_id = fr.request_group_id
    AND frt.LANGUAGE = USERENV('LANG');

Query to Get Employee Timecard Details

SELECT nvl(ppf.employee_number,ppf.npw_number) "Person Number",
 ppf.full_name,
 htbb1.start_time week_start_date,
 htbb1.stop_time week_end_date,
 hts.submission_date submitted_date,
 day.start_time day,
 hta1.attribute_category,
 detail.measure hours,
 detail.creation_date,
 detail.last_update_date,
 ppa.project_number,
 pt.task_number,
 hta.attribute3 "Type",hta.attribute2,
 hts.approval_status,pt.task_name
 FROM apps.per_all_people_f ppf,
 apps.hxc_time_building_blocks htbb1,
 apps.hxc_time_building_blocks DAY,
 apps.hxc_time_building_blocks detail,
 apps.hxc_base_pa_online_projects_v ppa,
 apps.hxc_base_pa_online_tasks_v pt,
 apps.hxc_time_attribute_usages htau,
 apps.hxc_time_attribute_usages htau1,
 apps.hxc_time_attributes hta,
 apps.hxc_bld_blk_info_types hbbit,
 apps.hxc_time_attributes hta1,
 apps.hxc_timecard_summary hts
 WHERE 1 = 1
 AND htbb1.resource_id = ppf.person_id
 AND htbb1.SCOPE = 'TIMECARD'
 AND TRUNC (SYSDATE) BETWEEN ppf.effective_start_date
 AND NVL (ppf.effective_end_date,
 TRUNC (SYSDATE))
 AND DAY.parent_building_block_id = htbb1.time_building_block_id
 AND DAY.parent_building_block_ovn = htbb1.object_version_number
 AND DAY.SCOPE = 'DAY'
 AND DAY.date_to = TO_DATE ('31/12/4712', 'DD/MM/YYYY')
 AND detail.date_to = TO_DATE ('31/12/4712', 'DD/MM/YYYY')
 AND detail.SCOPE = 'DETAIL'
 AND detail.parent_building_block_id = DAY.time_building_block_id
 AND detail.parent_building_block_ovn = DAY.object_version_number
 AND hta.attribute1 = ppa.project_id
 AND hta.attribute2 = pt.task_id
 AND ppa.project_id = pt.project_id
 AND htau.time_building_block_id = detail.time_building_block_id
 AND htau.time_building_block_ovn = detail.object_version_number
 AND htau1.time_building_block_id = detail.time_building_block_id
 AND htau1.time_building_block_ovn = detail.object_version_number
 AND htau.time_attribute_id = hta.time_attribute_id
 AND hta.bld_blk_info_type_id = hbbit.bld_blk_info_type_id
 AND hbbit.bld_blk_info_type = 'PROJECTS'
 AND htau1.time_attribute_id = hta1.time_attribute_id
 AND hta1.attribute_category like 'ELEMENT%'--'SECURITY'
 AND hts.timecard_id = htbb1.time_building_block_id
 AND NVL (hts.timecard_ovn, htbb1.object_version_number) = htbb1.object_version_number
 and nvl(ppf.employee_number, ppf.npw_number) = ':empno' -- Change your employee number here of comment this line for all resources
 and project_number='Aftermarket'
 and  hts.approval_status ='WORKING'
ORDER BY day.start_time asc;

Query to find Responsibility and its Operating Unit

Get Responsibility Name  from Org id
--------------------------------------------------------
SELECT   frv.responsibility_name,
         fpov.profile_option_value org_id,
         hou.NAME
    FROM apps.fnd_profile_options_vl fpo,
         apps.fnd_responsibility_vl frv,
         apps.fnd_profile_option_values fpov,
         apps.hr_organization_units hou
   WHERE hou.NAME = :P_Operating_unit_name
     AND fpov.profile_option_value = TO_CHAR (hou.organization_id)
     AND fpo.profile_option_id = fpov.profile_option_id
     AND fpo.user_profile_option_name = 'MO: Operating Unit'
     AND frv.responsibility_id = fpov.level_value
ORDER BY frv.responsibility_name

Query to Find Concurrent Program and its Parameters List

SELECT cp.user_concurrent_program_name ,
cp.concurrent_program_name ,
ap.application_name ,
cp.description,
cp.enabled_flag,
cp.output_file_type ,
cx.executable_name ,
lv.meaning ,
cx.user_executable_name ,
df.column_seq_num ,
df.end_user_column_name ,
df.description,
df.enabled_flag,
df.required_flag ,
df.security_enabled_flag ,
df.display_flag ,
fvs.flex_value_set_name ,
df.default_type ,
df.default_value
FROM apps.fnd_concurrent_programs_vl cp,
apps.fnd_executables_form_v cx,
apps.fnd_application_vl ap,
apps.fnd_descr_flex_col_usage_vl df,
apps.fnd_flex_value_sets fvs,
apps.fnd_lookup_values lv
WHERE cp.executable_id=cx.executable_id
AND cp.application_id=ap.application_id
AND fvs.flex_value_set_id=df.flex_value_set_id
AND lv.lookup_type = 'CP_EXECUTION_METHOD_CODE'
AND lv.lookup_code = cx.execution_method_code
AND cp.user_concurrent_program_name like 'Concurrent Program Name'
AND df.descriptive_flexfield_name = '$SRS$.'||cp.concurrent_program_name
AND lv.language='US'
ORDER BY df.column_seq_num

Query to find Profile Options and its values

SELECT DISTINCT

pro1.profile_option_name,

pov.profile_option_id,

    substr(pro1.user_profile_option_name,1,35) profile,

    DECODE(pov.level_id,10001,'Site',10002,'Application',10003,'Resp',10004,'User') option_level,    DECODE(pov.level_id,10001,'Site',10002,appl.application_short_name,10003,resp.responsibility_name,10004,u.user_name) level_value,

    nvl(pov.profile_option_value,'Is Null') profile_option_value

FROM

    fnd_profile_option_values pov,

    fnd_responsibility_tl resp,

    fnd_application appl,

    fnd_user u,

    fnd_profile_options pro,

    fnd_profile_options_tl pro1

WHERE

    pro.profile_option_name = pro1.profile_option_name

    AND   pro.profile_option_id = pov.profile_option_id

    AND   pov.level_value = resp.responsibility_id (+)

    AND   pov.level_value = appl.application_id (+)

    AND   pov.level_value = u.user_id (+)

    AND pro1.profile_option_name IN ('profile_option_name')

ORDER BY

    1,

    2;

Query to Find Request groups for given concurrent Program

 SELECT cpt.user_concurrent_program_name     "Concurrent Program Name",
       DECODE(rgu.request_unit_type,
              'P', 'Program',
              'S', 'Set',
              rgu.request_unit_type)        "Unit Type",
       cp.concurrent_program_name           "Concurrent Program Short Name",
       rg.application_id                    "Application ID",
       rg.request_group_name                "Request Group Name",
       fat.application_name                 "Application Name",
       fa.application_short_name            "Application Short Name",
       fa.basepath                          "Basepath"
  FROM fnd_request_groups          rg,
       fnd_request_group_units     rgu,
       fnd_concurrent_programs     cp,
       fnd_concurrent_programs_tl  cpt,
       fnd_application             fa,
       fnd_application_tl          fat
 WHERE rg.request_group_id       =  rgu.request_group_id
   AND rgu.request_unit_id       =  cp.concurrent_program_id
   AND cp.concurrent_program_id  =  cpt.concurrent_program_id
   AND rg.application_id         =  fat.application_id
   AND fa.application_id         =  fat.application_id
   AND cpt.language              =  USERENV('LANG')
   AND fat.language              =  USERENV('LANG')
   AND cpt.user_concurrent_program_name = '&coc prog name';

Query to find Workflow Inbound and Outbound Server Details.

SELECT fscpv.*

              FROM fnd_svc_comp_params_b   fscpb

                  ,fnd_svc_comp_param_vals fscpv

             WHERE fscpv.parameter_id = fscpb.parameter_id

               AND fscpb.component_type = 'WF_MAILER'

               AND fscpb.parameter_name = 'OUTBOUND_SERVER'

               AND EXISTS ( SELECT 1

                              FROM fnd_svc_comp_params_b   fscpb

                                  ,fnd_svc_comp_param_vals fscpv

                             WHERE UPPER(fscpv.parameter_value) = 'SMTP'

                               AND fscpv.parameter_id   = fscpb.parameter_id

                               AND fscpb.component_type = 'WF_MAILER'

                               AND fscpb.parameter_name = 'OUTBOUND_PROTOCOL' );

XML Publisher Template and Data Definition Details Query

SELECT distinct(XL.file_name )"File Name",
xl.last_update_date,
XDDV.data_source_code "Data Definition Code",
       XDDV.data_source_name "Data Definition",
       XDDV.description "Data Definition Description",
       XTB.template_code "Template Code",
       XTT.template_name "Template Name",
       XTT.description "Template Description",
       XTB.template_type_code "Type",
       XL.file_name "File Name",
       XTB.default_output_type "Default Output Type"
  FROM apps.XDO_DS_DEFINITIONS_VL XDDV,
       apps.XDO_TEMPLATES_B XTB,
       apps.XDO_TEMPLATES_TL XTT,
       apps.XDO_LOBS XL,
       apps.FND_APPLICATION_TL FAT,
       APPS.FND_APPLICATION FA
WHERE      XDDV.DATA_SOURCE_CODE like 'XX_FILE_NAME%'
       --AND FAT.application_name = '&Application_Name'
       AND XDDV.application_short_name = FA.application_short_name
       AND FAT.application_id = FA.application_id
       AND XTB.application_short_name = XDDV.application_short_name
       AND XDDV.data_source_code = XTB.data_source_code
       AND XTT.template_code = XTB.template_code
       AND XL.LOB_CODE = XTB.TEMPLATE_CODE   
       AND XL.XDO_FILE_TYPE = XTB.TEMPLATE_TYPE_CODE;

Query to Check Pending /Schedule Concurrent Programs or Request SETS.

SELECT fcr.request_id,
       DECODE(fcpt.user_concurrent_program_name,
              'Report Set',
              'Report Set:' || fcr.description,
              fcpt.user_concurrent_program_name) CONC_PROG_NAME,
       argument_text PARAMETERS,
       NVL2(fcr.resubmit_interval,
            'PERIODICALLY',
            NVL2(fcr.release_class_id, 'ON SPECIFIC DAYS', 'ONCE')) PROG_SCHEDULE_TYPE,
       DECODE(NVL2(fcr.resubmit_interval,
                   'PERIODICALLY',
                   NVL2(fcr.release_class_id, 'ON SPECIFIC DAYS', 'ONCE')),
              'PERIODICALLY',
              'EVERY ' || fcr.resubmit_interval || ' ' ||
              fcr.resubmit_interval_unit_code || ' FROM ' ||
              fcr.resubmit_interval_type_code || ' OF PREV RUN',
              'ONCE',
              'AT :' ||
              TO_CHAR(fcr.requested_start_date, 'DD-MON-RR HH24:MI'),
              'EVERY: ' || fcrc.class_info) PROG_SCHEDULE,
                CASE WHEN fcrc.class_type = 'S' AND instr(substr(fcrc.class_info, 33),'1',1) > 0 THEN
'Days of week: ' ||
decode(substr(fcrc.class_info, 33, 1), '1', 'Sun, ') ||
decode(substr(fcrc.class_info, 34, 1), '1', 'Mon, ') ||
decode(substr(fcrc.class_info, 35, 1), '1', 'Tue, ') ||
decode(substr(fcrc.class_info, 36, 1), '1', 'Wed, ') ||
decode(substr(fcrc.class_info, 37, 1), '1', 'Thu, ') ||
decode(substr(fcrc.class_info, 38, 1), '1', 'Fri, ') ||
decode(substr(fcrc.class_info, 39, 1), '1', 'Sat ')
ELSE
'n/a'
end days_of_week
,      fu.user_name USER_NAME,
       to_char(requested_start_date,'dd-mon-yyyy hh24:mi:ss') START_DATE
  FROM apps.fnd_concurrent_programs_tl fcpt,
       apps.fnd_concurrent_requests    fcr,
       apps.fnd_user                   fu,
       apps.fnd_conc_release_classes   fcrc
 WHERE fcpt.application_id = fcr.program_application_id
   AND fcpt.concurrent_program_id = fcr.concurrent_program_id
   AND fcr.requested_by = fu.user_id
   AND fcr.phase_code = 'P'
   AND fcr.requested_start_date > SYSDATE
   AND fcpt.LANGUAGE = 'US'
   AND fcrc.release_class_id(+) = fcr.release_class_id
   AND fcrc.application_id(+) = fcr.release_class_app_id
  /* and  DECODE(fcpt.user_concurrent_program_name,
              'Report Set',
              'Report Set:' || fcr.description,
              fcpt.user_concurrent_program_name) like '%Perform full cycle count%'*/

Query for Scheduled Concurrent Programs

SELECT fcr.request_id
, fcpt.user_concurrent_program_name|| NVL2(fcr.description, ' (' || fcr.description || ')', NULL) conc_prog
, CASE WHEN fcrc.class_type = 'P' THEN
'Repeat every ' ||
substr(fcrc.class_info, 1, instr(fcrc.class_info, ':') - 1) ||
decode(substr(fcrc.class_info, instr(fcrc.class_info, ':', 1, 1) + 1, 1),
'N', ' minutes',
'M', ' months',
'H', ' hours',
'D', ' days') ||
decode(substr(fcrc.class_info, instr(fcrc.class_info, ':', 1, 2) + 1, 1),
'S', ' from the start of the prior run',
'C', ' from the completion of the prior run')
ELSE
'n/a'
END set_days_of_week
,
NVL2 (fcr.resubmit_interval,'PERIODICALLY',NVL2 (fcr.release_class_id, 'ON SPECIFIC DAYS', 'ONCE')) schedule_type,
DECODE (NVL2 (fcr.resubmit_interval,'PERIODICALLY',NVL2 (fcr.release_class_id, 'ON SPECIFIC DAYS', 'ONCE')),
      'PERIODICALLY',fcr.resubmit_interval,fcr.resubmit_interval_unit_code
 ) schedule,fcr.resubmit_interval_unit_code
 , CASE WHEN fcrc.class_type = 'S' AND instr(substr(fcrc.class_info, 33),'1',1) > 0 THEN
'Days of week: ' ||
decode(substr(fcrc.class_info, 33, 1), '1', 'Sun, ') ||
decode(substr(fcrc.class_info, 34, 1), '1', 'Mon, ') ||
decode(substr(fcrc.class_info, 35, 1), '1', 'Tue, ') ||
decode(substr(fcrc.class_info, 36, 1), '1', 'Wed, ') ||
decode(substr(fcrc.class_info, 37, 1), '1', 'Thu, ') ||
decode(substr(fcrc.class_info, 38, 1), '1', 'Fri, ') ||
decode(substr(fcrc.class_info, 39, 1), '1', 'Sat ')
ELSE
'n/a'
end days_of_week
, CASE WHEN fcrc.class_type = 'S' AND instr(substr(fcrc.class_info, 1, 31),'1',1) > 0 THEN
'Set Days of Month: ' ||
decode(substr(fcrc.class_info, 1, 1), '1', '1st, ') ||
decode(substr(fcrc.class_info, 2, 1), '1', '2nd, ') ||
decode(substr(fcrc.class_info, 3, 1), '1', '3rd, ') ||
decode(substr(fcrc.class_info, 4, 1), '1', '4th, ') ||
decode(substr(fcrc.class_info, 5, 1), '1', '5th, ') ||
decode(substr(fcrc.class_info, 6, 1), '1', '6th, ') ||
decode(substr(fcrc.class_info, 7, 1), '1', '7th, ') ||
decode(substr(fcrc.class_info, 8, 1), '1', '8th, ') ||
decode(substr(fcrc.class_info, 9, 1), '1', '9th, ') ||
decode(substr(fcrc.class_info, 10, 1), '1', '10th, ') ||
decode(substr(fcrc.class_info, 11, 1), '1', '11th, ') ||
decode(substr(fcrc.class_info, 12, 1), '1', '12th, ') ||
decode(substr(fcrc.class_info, 13, 1), '1', '13th, ') ||
decode(substr(fcrc.class_info, 14, 1), '1', '14th, ') ||
decode(substr(fcrc.class_info, 15, 1), '1', '15th, ') ||
decode(substr(fcrc.class_info, 16, 1), '1', '16th, ') ||
decode(substr(fcrc.class_info, 17, 1), '1', '17th, ') ||
decode(substr(fcrc.class_info, 18, 1), '1', '18th, ') ||
decode(substr(fcrc.class_info, 19, 1), '1', '19th, ') ||
decode(substr(fcrc.class_info, 20, 1), '1', '20th, ') ||
decode(substr(fcrc.class_info, 21, 1), '1', '21st, ') ||
decode(substr(fcrc.class_info, 22, 1), '1', '22nd, ') ||
decode(substr(fcrc.class_info, 23, 1), '1', '23rd,' ) ||
decode(substr(fcrc.class_info, 24, 1), '1', '24th, ') ||
decode(substr(fcrc.class_info, 25, 1), '1', '25th, ') ||
decode(substr(fcrc.class_info, 26, 1), '1', '26th, ') ||
decode(substr(fcrc.class_info, 27, 1), '1', '27th, ') ||
decode(substr(fcrc.class_info, 28, 1), '1', '28th, ') ||
decode(substr(fcrc.class_info, 29, 1), '1', '29th, ') ||
decode(substr(fcrc.class_info, 30, 1), '1', '30th, ') ||
decode(substr(fcrc.class_info, 31, 1), '1', '31st. ')
ELSE
'n/a'
END days_of_month
, CASE WHEN fcrc.class_type = 'S' AND substr(fcrc.class_info, 32, 1) = '1' THEN
'Yes'
ELSE
'n/a'
END last_day_of_month_ticked
, fcrc.CLASS_INFO
 , fu.user_name requestor
, fu.description requested_by
, fu.email_address
, frt.responsibility_name requested_by_resp
, trim(fl.meaning) status
, fcr.phase_code
, fcr.status_code
, fcr.argument_text "PARAMETERS"
, '------>' dates
, TO_CHAR(fcr.request_date, 'DD-MON-YYYY HH24:MI:SS') requested
, TO_CHAR(fcr.requested_start_date, 'DD-MON-YYYY HH24:MI:SS') requested_start
, TO_CHAR((fcr.requested_start_date), 'HH24:MI:SS') start_time
, '------>' holds
, DECODE(fcr.hold_flag, 'Y', 'Yes', 'N', 'No') on_hold
, CASE
WHEN fcr.hold_flag = 'Y'
THEN SUBSTR(
u2.description
, 0
, 40
)
END last_update_by
, CASE
WHEN fcr.hold_flag = 'Y'
THEN fcr.last_update_date
END last_update_date
, '------>' prints
, fcr.number_of_copies print_count
, fcr.printer
, fcr.print_style
, '------>' schedule
, fcr.increment_dates
, CASE WHEN fcrc.CLASS_INFO IS NULL THEN
'Yes: ' || TO_CHAR(fcr.requested_start_date, 'DD-MON-YYYY HH24:MI:SS')
ELSE
'n/a'
END run_once

FROM applsys.fnd_concurrent_requests fcr
, applsys.fnd_user fu
, applsys.fnd_user u2
, applsys.fnd_concurrent_programs fcp
, applsys.fnd_concurrent_programs_tl fcpt
, applsys.fnd_printer_styles_tl fpst
, applsys.fnd_conc_release_classes fcrc
, applsys.fnd_responsibility_tl frt
, apps.fnd_lookups fl
WHERE fcp.application_id = fcpt.application_id
AND fcr.requested_by = fu.user_id
AND fcr.concurrent_program_id = fcp.concurrent_program_id
AND fcr.program_application_id = fcp.application_id
AND fcr.concurrent_program_id = fcpt.concurrent_program_id
AND fcr.responsibility_id = frt.responsibility_id
AND fcr.last_updated_by = u2.user_id
AND fcr.print_style = fpst.printer_style_name(+)
AND fcr.release_class_id = fcrc.release_class_id(+)
AND fcr.status_code = fl.lookup_code
AND fl.lookup_type = 'CP_STATUS_CODE'
AND fcr.phase_code = 'P'
AND fcpt.user_concurrent_program_name|| NVL2(fcr.description, ' (' || fcr.description || ')', NULL)
= 'OTL Missing Time Card Report'--conc program name
AND 1=1
ORDER BY fu.description, fcr.requested_start_date asc;

Finding the Responsibility for given Request Set

select frt.responsibility_name, frg.request_group_name,
frgu.request_unit_type,frgu.request_unit_id,
fcpt.user_request_set_name
From apps.fnd_Responsibility fr, apps.fnd_responsibility_tl frt,
apps.fnd_request_groups frg, apps.fnd_request_group_units frgu,
apps.fnd_request_Sets_tl fcpt
where frt.responsibility_id = fr.responsibility_id
and frg.request_group_id = fr.request_group_id
and frgu.request_group_id = frg.request_group_id
and fcpt.request_set_id = frgu.request_unit_id
and fcpt.user_request_set_name = '&conc_name'
order by 1,2,3,4 

Finding the Responsibility for given Concurrent Program

select frt.responsibility_name, frg.request_group_name,
frgu.request_unit_type,frgu.request_unit_id,
fcpt.user_concurrent_program_name
From fnd_Responsibility fr, fnd_responsibility_tl frt,
fnd_request_groups frg, fnd_request_group_units frgu,
fnd_concurrent_programs_tl fcpt
where frt.responsibility_id = fr.responsibility_id
and frg.request_group_id = fr.request_group_id
and frgu.request_group_id = frg.request_group_id
and fcpt.concurrent_program_id = frgu.request_unit_id
and fcpt.user_concurrent_program_name = '&conc_name'
order by 1,2,3,4 

Get all the Responsibilities for Specific User

select frv.responsibility_name, fu.user_name
from
apps.fnd_user_resp_groups_direct furgd
,apps.fnd_user fu
,apps.fnd_responsibility_vl frv
where
user_name = 'OPERATIONS'--NUMBER
and fu.user_id = furgd.user_id
and frv.responsibility_id = furgd.responsibility_id

How to convert number into words

select to_cahr(to_date(541,'j'),'jsp') from dual;