link between fnd_user and per_all_people_f

 SELECT
    fur.user_id,
    fur.user_name,
    papf.employee_number,
    papf.full_name
FROM
    per_all_people_f  papf,
    fnd_user          fur
WHERE
        1 = 1
    AND papf.person_id = fur.employee_id
    AND trunc(sysdate) BETWEEN trunc(papf.effective_start_date) AND trunc(papf.effective_end_date)
    AND papf.employee_number = 704391
ORDER BY
    fur.user_id;

Query to find list of responsibilities assign to user

SELECT

    usr.user_id,

    usr.user_name,

    papf.employee_number,

    papf.full_name,

    resp.responsibility_name,

    resp.responsibility_id,

    resp.application_id,

    furg.security_group_id,

    furg.start_date,

    furg.end_date

FROM

    per_all_people_f          papf,

    fnd_responsibility_tl     resp,

    fnd_user                  usr,

    fnd_user_resp_groups_all  furg

WHERE

        1 = 1

    AND trunc(sysdate) BETWEEN trunc(furg.start_date) AND trunc(nvl(furg.end_date, sysdate))

    AND furg.responsibility_id = resp.responsibility_id

    AND usr.user_id = furg.user_id

    AND papf.person_id = usr.employee_id

    AND trunc(sysdate) BETWEEN trunc(papf.effective_start_date) AND trunc(papf.effective_end_date)

    AND papf.employee_number = 1111111

ORDER BY

    usr.user_id;

API to get order line number, line amount and order amount

---------------------------------------------------------------------------------

oe_order_misc_pub.get_concat_line_number(oola.line_id) 

oe_totals_grp.get_order_total(ooha.header_id, oola.line_id, 'ALL')

oe_totals_grp.get_order_total(ooha.header_id, NULL, 'ALL')

---------------------------------------------------------------------------------

SELECT
    ooha.org_id,
    ooha.header_id,
    ooha.order_number,
    oola.line_id,
    oe_order_misc_pub.get_concat_line_number(oola.line_id) line_number,
    nvl(oe_totals_grp.get_order_total(ooha.header_id, oola.line_id, 'ALL'), 0)                 line_amount,
    nvl(oe_totals_grp.get_order_total(ooha.header_id, NULL, 'ALL'), 0)                         order_amount  
FROM
    oe_order_headers_all  ooha,
    oe_order_lines_all    oola
WHERE
        ooha.header_id = oola.header_id
    AND ooha.org_id = oola.org_id
    AND ooha.order_number = '71000004'
    order by oola.line_id;

query to find supplier address and email id in oracle apps

 SELECT DISTINCT
    aps.vendor_name,
    aps.segment1    supplier_number,
    assa.org_id,
    assa.vendor_site_code,
    (
        SELECT
            hcp.email_address
        FROM
            hz_party_sites     hps,
            hz_contact_points  hcp
        WHERE
                1 = 1
            AND hcp.owner_table_name = 'HZ_PARTY_SITES'
            AND hps.party_id = aps.party_id
            AND hcp.owner_table_id = hps.party_site_id
            AND hcp.contact_point_type = 'EMAIL'
            AND ROWNUM <= 1
    )               email_id,
    assa.address_line1,
    assa.address_line2,
    assa.address_line3,
    assa.city,
    assa.state,
    assa.zip,
    assa.country
FROM
    apps.ap_suppliers             aps,
    apps.ap_supplier_sites_all    assa
WHERE
        1 = 1
    AND aps.vendor_id = assa.vendor_id
    AND aps.segment1 = '28015';

Query to find value set name from concurrent program

  SELECT
    fcpl.user_concurrent_program_name,
    fcp.concurrent_program_name,
    fdfcuv.column_seq_num,
    fdfcuv.end_user_column_name,
    fdfcuv.form_left_prompt,
    fdfcuv.enabled_flag,
    fdfcuv.required_flag,
    fdfcuv.display_flag,
    fdfcuv.flex_value_set_id,
    ffvs.flex_value_set_name,
    flv.meaning,
    fdfcuv.default_value
FROM
    fnd_concurrent_programs      fcp,
    fnd_concurrent_programs_tl   fcpl,
    fnd_descr_flex_col_usage_vl  fdfcuv,
    fnd_flex_value_sets          ffvs,
    fnd_lookup_values            flv
WHERE
        fcp.concurrent_program_id = fcpl.concurrent_program_id
    AND fcpl.user_concurrent_program_name = 'concurrent_program_name'

    AND fdfcuv.descriptive_flexfield_name = '$SRS$.' || fcp.concurrent_program_name
    AND ffvs.flex_value_set_id = fdfcuv.flex_value_set_id
    AND flv.lookup_type (+) = 'FLEX_DEFAULT_TYPE'
    AND flv.lookup_code (+) = fdfcuv.default_type
    AND fcpl.language = userenv('LANG')
    AND flv.language (+) = userenv('LANG')
ORDER BY
    fdfcuv.column_seq_num;

Query to find concurrent program name from value set

 SELECT
    fdfcuv.flex_value_set_id,
    ffvs.flex_value_set_name,
    flv.meaning,
    fdfcuv.default_value,
    fcpl.user_concurrent_program_name,
    fcp.concurrent_program_name,
    fdfcuv.column_seq_num,
    fdfcuv.end_user_column_name,
    fdfcuv.form_left_prompt,
    fdfcuv.enabled_flag,
    fdfcuv.required_flag,
    fdfcuv.display_flag
FROM
    fnd_concurrent_programs      fcp,
    fnd_concurrent_programs_tl   fcpl,
    fnd_descr_flex_col_usage_vl  fdfcuv,
    fnd_flex_value_sets          ffvs,
    fnd_lookup_values            flv
WHERE
        fcp.concurrent_program_id = fcpl.concurrent_program_id
    AND fdfcuv.descriptive_flexfield_name = '$SRS$.' || fcp.concurrent_program_name
    AND ffvs.flex_value_set_id = fdfcuv.flex_value_set_id
    AND flv.lookup_type (+) = 'FLEX_DEFAULT_TYPE'
    AND flv.lookup_code (+) = fdfcuv.default_type
    AND fcpl.language = userenv('LANG')
    AND flv.language (+) = userenv('LANG')
    AND fdfcuv.enabled_flag = 'Y'
    AND ffvs.flex_value_set_name LIKE '%VALUE_SET_NAME%'
ORDER BY
    fcpl.user_concurrent_program_name;

Query to remove junk characters

select REGEXP_REPLACE (' SSS TEST ãallç', '[^ -~]', '') from dual;