Query to get details for Vacation Rule /Delegation in oracle apps

This query will give you details for user if any vacation rule is set.

select * from apps.WF_ROUTING_RULES
where role = &P_USER_NAME;

====================================
Below given query used to get details of notification send to delegates using vacation rules.

SELECT
    wit.display_name workflow_process,
    nvl(wi.user_key, (
        SELECT
            user_key
        FROM
            apps.wf_items w
        WHERE
            w.item_key = wi.parent_item_key
            AND   w.item_type = wi.item_type
    ) ) document_number,
    from_role,
    recipient_role,
    from_user,
    to_user,
    status,
    mail_status,
    original_recipient,    
    wias.activity_result_code notification_status,
    wn.subject,
    wn.begin_date notification_begin_date,
    wn.end_date notification_end_date,
    wrr.begin_date delegation_start_date,
    wrr.end_date delegation_end_date
FROM
    apps.wf_notifications wn,
    apps.wf_routing_rules wr,
    apps.wf_routing_rules wrr,
    apps.wf_item_activity_statuses wias,
    apps.wf_items wi,
    apps.wf_item_types_vl wit
WHERE
    wn.from_role =wr.role
    AND   wrr.role = wn.from_role
    AND   wrr.action_argument = wn.recipient_role
    AND   wias.item_key = wn.item_key                       
    AND   wias.notification_id = wn.notification_id
    AND   wn.item_key = wi.item_key
    AND   wn.message_type = wi.item_type
    AND   wit.name = wi.item_type
    AND   original_recipient =&P_USER_NAME
    AND   wn.begin_date > SYSDATE - 10
ORDER BY
    original_recipient,
    wn.begin_date DESC;

AR and OM link in oracle apps / AR transaction and Sales Order join query

SELECT
    rcta.customer_trx_id,
    rcta.trx_number,
    rcta.trx_date,
    ac.customer_name,
    rctla.line_number inv_line_number,
    rctla.unit_selling_price inv_unit_selling_price,
    ooha.header_id,
    ooha.order_number,
    oola.line_number so_line_number,
    oola.line_id,
    oola.ordered_item,
    oola.ordered_quantity * oola.unit_selling_price so_amount
FROM
    apps.ra_customer_trx_all rcta,
    apps.ra_customer_trx_lines_all rctla,
    apps.ar_customers ac,
    apps.oe_order_headers_all ooha,
    apps.oe_order_lines_all oola
WHERE
    1 = 1
    AND   rcta.customer_trx_id = rctla.customer_trx_id
    AND   rcta.bill_to_customer_id = ac.customer_id
    AND   ooha.header_id = oola.header_id
    AND   rctla.interface_line_attribute6 = TO_CHAR(oola.line_id)
    AND   rctla.interface_line_attribute1 = TO_CHAR(ooha.order_number)
    AND   rcta.trx_number = nvl(:p_trx_number,rcta.trx_number)
    AND   ooha.order_number = nvl(:p_order_number,ooha.order_number);

Query for responsibility list attached to user in oracle apps

SELECT
    fu.user_name,
    frt.responsibility_name,
    furg.start_date,
    furg.end_date,
    fr.responsibility_key,
    fa.application_short_name
FROM
    apps.fnd_user_resp_groups_direct furg,
    applsys.fnd_user fu,
    applsys.fnd_responsibility_tl frt,
    applsys.fnd_responsibility fr,
    applsys.fnd_application_tl fat,
    applsys.fnd_application fa
WHERE
    1 = 1
    AND   furg.user_id = fu.user_id
    AND   furg.responsibility_id = frt.responsibility_id
    AND   fr.responsibility_id = frt.responsibility_id
    AND   fa.application_id = fat.application_id
    AND   fr.application_id = fat.application_id
    AND   frt.language = userenv('LANG')
    AND   upper(fu.user_name) = upper(&P_USER_NAME)
    AND   SYSDATE BETWEEN nvl(furg.start_date,SYSDATE) AND nvl(furg.end_date,SYSDATE + 1)
ORDER BY  1,2;

Oracle apps Valueset details Query

SELECT
    ffvs.flex_value_set_id,
    ffvs.flex_value_set_name,
    ffvs.description set_description,
    ffvs.validation_type,
    ffvt.value_column_name,
    ffvt.meaning_column_name,
    ffvt.id_column_name,
    ffvt.application_table_name,
    ffvt.additional_where_clause
FROM
    apps.fnd_flex_value_sets ffvs,
    apps.fnd_flex_validation_tables ffvt
WHERE
    ffvs.flex_value_set_id = ffvt.flex_value_set_id
    AND   ffvs.flex_value_set_name LIKE 'IBY_YES_NO';

Query to get GL code combinations description

----------
1) Below given query can be used TO get concatenated Gl code combinations segment
AND its description USING API GL_FLEXFIELDS_PKG.get_concat_description
----------
SELECT
  gcc.CODE_COMBINATION_ID,
  gcc.CONCATENATED_SEGMENTS,
  APPS.GL_FLEXFIELDS_PKG.get_concat_description(gcc.chart_of_accounts_id,
  gcc.code_combination_id) acc_description
FROM
  gl_code_combinations_kfv gcc;
  
  
----------
2)Below given Query can be used
WHEN one dont have access TO GL_FLEXFIELDS_PKG.get_concat_description API.
  ----------
  SELECT
    gcc.CODE_COMBINATION_ID,
    gcc.CONCATENATED_SEGMENTS,
    acc_desc.acc_description
  FROM
    (
      SELECT
        gcc.code_combination_id,
        A1.DESCRIPTION
        ||'.'
        ||A2.DESCRIPTION
        ||'.'
        || A3.DESCRIPTION
        ||'.'
        || A4.DESCRIPTION
        ||'.'
        || A5.DESCRIPTION
        ||'.'
        || A6.DESCRIPTION
        ||'.'
        || A7.DESCRIPTION
        ||'.'
        || A8.DESCRIPTION acc_description
      FROM
        apps.fnd_flex_values_vl A1,
        apps.fnd_flex_values_vl A2,
        apps.fnd_flex_values_vl A3,
        apps.fnd_flex_values_vl A4,
        apps.fnd_flex_values_vl A5,
        apps.fnd_flex_values_vl A6,
        apps.fnd_flex_values_vl A7,
        apps.fnd_flex_values_vl A8,
        apps.gl_code_combinations gcc
      WHERE
        a1.flex_value           =gcc.segment1
      AND a1.FLEX_VALUE_SET_ID IN
        (
          SELECT
            FLEX_VALUE_SET_ID
          FROM
            apps.fnd_id_flex_segments
          WHERE
            application_id           = 101
          AND id_flex_code           = 'GL#'
          AND enabled_flag           = 'Y'
          AND application_column_name='SEGMENT1'
        )
      AND a2.flex_value         =gcc.segment2
      AND a2.FLEX_VALUE_SET_ID IN
        (
          SELECT
            FLEX_VALUE_SET_ID
          FROM
            apps.fnd_id_flex_segments
          WHERE
            application_id           = 101
          AND id_flex_code           = 'GL#'
          AND enabled_flag           = 'Y'
          AND application_column_name='SEGMENT2'
        )
      AND a3.flex_value         =gcc.segment3
      AND a3.FLEX_VALUE_SET_ID IN
        (
          SELECT
            FLEX_VALUE_SET_ID
          FROM
            apps.fnd_id_flex_segments
          WHERE
            application_id           = 101
          AND id_flex_code           = 'GL#'
          AND enabled_flag           = 'Y'
          AND application_column_name='SEGMENT3'
        )
      AND a4.flex_value         =gcc.segment4
      AND a4.FLEX_VALUE_SET_ID IN
        (
          SELECT
            FLEX_VALUE_SET_ID
          FROM
            apps.fnd_id_flex_segments
          WHERE
            application_id           = 101
          AND id_flex_code           = 'GL#'
          AND enabled_flag           = 'Y'
          AND application_column_name='SEGMENT4'
        )
      AND a5.flex_value         =gcc.segment5
      AND a5.FLEX_VALUE_SET_ID IN
        (
          SELECT
            FLEX_VALUE_SET_ID
          FROM
            apps.fnd_id_flex_segments
          WHERE
            application_id           = 101
          AND id_flex_code           = 'GL#'
          AND enabled_flag           = 'Y'
          AND application_column_name='SEGMENT5'
        )
      AND a6.flex_value         =gcc.segment6
      AND a6.FLEX_VALUE_SET_ID IN
        (
          SELECT
            FLEX_VALUE_SET_ID
          FROM
            apps.fnd_id_flex_segments
          WHERE
            application_id           = 101
          AND id_flex_code           = 'GL#'
          AND enabled_flag           = 'Y'
          AND application_column_name='SEGMENT6'
        )
      AND a7.flex_value         =gcc.segment7
      AND a7.FLEX_VALUE_SET_ID IN
        (
          SELECT
            FLEX_VALUE_SET_ID
          FROM
            apps.fnd_id_flex_segments
          WHERE
            application_id           = 101
          AND id_flex_code           = 'GL#'
          AND enabled_flag           = 'Y'
          AND application_column_name='SEGMENT7'
        )
      AND a8.flex_value         =gcc.segment8
      AND a8.FLEX_VALUE_SET_ID IN
        (
          SELECT
            FLEX_VALUE_SET_ID
          FROM
            apps.fnd_id_flex_segments
          WHERE
            application_id           = 101
          AND id_flex_code           = 'GL#'
          AND enabled_flag           = 'Y'
          AND application_column_name='SEGMENT8'
        )
    )
    acc_desc,
    gl_code_combinations_kfv gcc
  WHERE
    gcc.code_combination_id=acc_desc.code_combination_id;
    
    
  ----------
  3) Below given query can be used TO get EACH segment description seperately
  USING API gl_flexfields_pkg.get_description_sql
  ---------
  SELECT
    gcc.segment1,
    gl_flexfields_pkg.get_description_sql (gcc.chart_of_accounts_id,--- chart of account id
    1,          ----- Position of segment
    gcc.segment1 ---- Segment value
    ) Segment1_desc,
    gcc.segment2,
    gl_flexfields_pkg.get_description_sql (gcc.chart_of_accounts_id,--- chart of account id
    2,          ----- Position of segment
    gcc.segment2 ---- Segment value
    ) Segment2_desc,
    gcc.segment3,
    gl_flexfields_pkg.get_description_sql (gcc.chart_of_accounts_id,--- chart of account id
    3,          ----- Position of segment
    gcc.segment3 ---- Segment value
    ) Segment3_desc,
    gcc.segment4,
    gl_flexfields_pkg.get_description_sql (gcc.chart_of_accounts_id,--- chart of account id
    4,          ----- Position of segment
    gcc.segment4 ---- Segment value
    ) Segment4_desc,
    gcc.segment5,
    gl_flexfields_pkg.get_description_sql (gcc.chart_of_accounts_id,--- chart of account id
    5,          ----- Position of segment
    gcc.segment5 ---- Segment value
    ) Segment5_desc,
    gcc.segment6,
    gl_flexfields_pkg.get_description_sql (gcc.chart_of_accounts_id,--- chart of account id
    6,          ----- Position of segment
    gcc.segment6 ---- Segment value
    ) Segment6_desc,
    gcc.segment7,
    gl_flexfields_pkg.get_description_sql (gcc.chart_of_accounts_id,--- chart of account id
    7,          ----- Position of segment
    gcc.segment7 ---- Segment value
    ) Segment7_desc,
    gcc.segment8,
    gl_flexfields_pkg.get_description_sql (gcc.chart_of_accounts_id,--- chart of account id
    8,          ----- Position of segment
    gcc.segment8 ---- Segment value
    ) Segment8_desc
  FROM
    gl_code_combinations gcc;

Query to check concurrent program attached to responsibility and User

SELECT fu.USER_NAME,
  frt.responsibility_name,
  frg.request_group_name,
  frgu.request_unit_type,
  DECODE (frgu.request_unit_type,
  'A' ,'Application',
  'P' ,'Program',
  'S' ,'Request Set',
  'Unknown')Request_type,
  fcpt.user_concurrent_program_name
FROM apps.fnd_Responsibility fr,
  apps.fnd_responsibility_tl frt,
  apps.fnd_request_groups frg,
  apps.fnd_request_group_units frgu,
  apps.fnd_concurrent_programs_tl fcpt,
  apps.fnd_user_resp_groups_direct furg,
  apps.fnd_user fu
WHERE frt.responsibility_id= fr.responsibility_id
AND frg.request_group_id= fr.request_group_id
AND furg.RESPONSIBILITY_ID= fr.RESPONSIBILITY_ID
AND furg.USER_ID    = fu.USER_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 = NVL(:p_conc_prog_name,fcpt.user_concurrent_program_name)
AND frt.responsibility_name = NVL(:p_resp_name,frt.responsibility_name)
AND fu.USER_NAME=nvl(:p_user_name , fu.USER_NAME)
ORDER BY 1,2,3;

Query for Oracle Period status for AP and AR applications

query to check Period status for AP and AR application

SELECT GLS.NAME LEDGER_NAME,
  FA.APPLICATION_SHORT_NAME,
  GPS.closing_status,
  DECODE(GPS.closing_status, 'C', 'Closed', 
         'O', 'Open', 
         'F', 'Future', 
         'W', 'Closed Pending', 
         'N', 'Never Opened', 
         'P', 'Permanently Closed') "PeriodStatus" ,
  GPS.*
FROM GL.GL_PERIOD_STATUSES GPS,
  GL.GL_LEDGERS GLS,
  APPLSYS.FND_APPLICATION FA
WHERE PERIOD_NAME     ='SEP-FY-17'
AND GPS.APPLICATION_ID IN (222,200)
AND FA.APPLICATION_ID =GPS.APPLICATION_ID
AND GLS.LEDGER_ID     =GPS.SET_OF_BOOKS_ID
ORDER BY 1;