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';