Query to find list of schedule concurrent program

 SELECT fcr.request_id ,
  fcpt.user_concurrent_program_name
  || NVL2(fcr.description, ' ('
  || fcr.description
  || ')', NULL) conc_prog ,
  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" ,
  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 ,
  DECODE(fcr.hold_flag, 'Y', 'Yes', 'N', 'No') on_hold ,
  CASE
    WHEN fcr.hold_flag = 'Y'
    THEN SUBSTR( fu.description , 0 , 40 )
  END last_update_by ,
  CASE
    WHEN fcr.hold_flag = 'Y'
    THEN fcr.last_update_date
  END last_update_date ,
  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 ,
  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 ,
  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
FROM apps.fnd_concurrent_requests fcr ,
  apps.fnd_user fu ,
  apps.fnd_concurrent_programs fcp ,
  apps.fnd_concurrent_programs_tl fcpt ,
  apps.fnd_printer_styles_tl fpst ,
  apps.fnd_conc_release_classes fcrc ,
  apps.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.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 frt.language               = 'US'
AND fpst.language              = 'US'
AND fcpt.language              = 'US'
and fcpt.user_concurrent_program_name ='Concurrent program name'
ORDER BY Fu.Description,
  Fcr.Requested_Start_Date ASC  
  ;

Query to find receipt number from invoice

 SELECT
    acr.receipt_number,
    acr.receipt_date,
    acr.amount,
    rct.customer_trx_id,
    rct.trx_number,
    rct.trx_date,
    acr.last_update_date,
    rct.last_update_date,
    acr.receipt_method_id
FROM
    ar_receivable_applications_all  ara,
    ar_cash_receipts_all            acr,
    ra_customer_trx_all             rct
WHERE
        1 = 1
    --    ara.status = 'APP'
    AND ara.cash_receipt_id = acr.cash_receipt_id
    AND ara.applied_customer_trx_id = rct.customer_trx_id
    AND rct.org_id = 1111
    AND rct.trx_number IN ( '111111' )
ORDER BY
    rct.last_update_date DESC;

Query to find list of all open invoice for the customer

 SELECT
    hp.party_id,
    hp.party_number,
    hp.party_name,
    hp.party_type,
    hca.account_number,
    hcp.send_statements,
    hcp.statement_cycle_id,
    hcp.dunning_letters,
    rcta.org_id,
    hou.name,
    rcta.trx_number                           invoice_number,
    ar.class,
    rcta.purchase_order                       purchase_order,
    rcta.trx_date                             transaction_date,
    to_char(ar.due_date, 'MM/DD/YYYY')        due_date,
    ar.amount_due_remaining                   amount_due_remaining,
    trunc(sysdate - ar.due_date)              day_late,
    hca.cust_account_id
FROM
    hz_parties                hp,
    hz_cust_accounts          hca,
    hz_customer_profiles      hcp,
    ra_customer_trx_all       rcta,
    hr_operating_units        hou,
    ar_payment_schedules_all  ar
WHERE
        1 = 1
    AND hp.party_id = hca.party_id
    AND hca.cust_account_id = hcp.cust_account_id
    AND hcp.site_use_id IS NULL
    AND rcta.org_id = hou.organization_id
    AND rcta.bill_to_customer_id = hcp.cust_account_id
    AND rcta.customer_trx_id = ar.customer_trx_id
    AND ar.class = 'INV'
    AND ar.status = 'OP'
    AND hp.status = 'A'
    AND hca.status = 'A'
    AND hcp.status = 'A'
    AND hp.party_type = 'ORGANIZATION'
    AND hca.account_number = '37382'
ORDER BY
    hp.party_name,
    hca.account_number;

Query to find customer Profile and Profile amount information

 SELECT DISTINCT
    hp.party_id,
    hp.party_number,
    hca.account_number,
    hp.party_name,
    hp.party_type,
    ac.name           collector_name,
    ac.description    collector_description,
    hcp.send_statements,
    hcp.statement_cycle_id,
    ascs.name         statement_cycle_name,
    hcp.dunning_letters,
    hcp.dunning_letter_set_id,
    adls.name         dunning_letter_set,
    hcpa.min_statement_amount,
    hcpa.min_dunning_amount,
    hcpa.currency_code,
    hca.cust_account_id
FROM
    hz_parties              hp,
    hz_cust_accounts        hca,
    hz_customer_profiles    hcp,
    hz_cust_profile_amts    hcpa,
    ar_statement_cycles     ascs,
    ar_dunning_letter_sets  adls,
    ar_collectors           ac
WHERE
        1 = 1
    AND hp.party_id = hca.party_id
    AND hca.cust_account_id = hcp.cust_account_id
    AND hcp.cust_account_profile_id = hcpa.cust_account_profile_id (+)
    AND hcp.collector_id = ac.collector_id (+)
    AND hcp.statement_cycle_id = ascs.statement_cycle_id (+)
    AND hcp.dunning_letter_set_id = adls.dunning_letter_set_id (+)
    AND hcp.site_use_id IS NULL
    AND hcpa.site_use_id IS NULL
    AND hp.status = 'A'
    AND hca.status = 'A'
    AND hcp.status = 'A'
    AND hp.party_type = 'ORGANIZATION'
    AND hca.account_number = '1111111';

Link between ar_collectors and per_all_people_f

  SELECT
    ac.name,
    ac.description,
    papf.full_name
FROM
    ar_collectors     ac,
    per_all_people_f  papf
WHERE
        1 = 1
    AND ac.employee_id = papf.person_id(+)
    AND ac.status = 'A'
    AND trunc(sysdate) BETWEEN trunc(papf.effective_start_date) AND trunc(papf.effective_end_date);

Query to find list of responsibilities for a user in oracle apps

 SELECT
    fu.user_name,
    frt.responsibility_name,
    furg.start_date,
    furg.end_date,
    fr.responsibility_key,
    fa.application_short_name "Application Short Name"
FROM
    apps.fnd_user_resp_groups_direct    furg,
    apps.fnd_user                       fu,
    apps.fnd_responsibility_tl          frt,
    apps.fnd_responsibility             fr,
    apps.fnd_application_tl             fat,
    apps.fnd_application                fa
WHERE
        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('USER_NAME')
    AND trunc(nvl(furg.end_date, sysdate)) >= trunc(sysdate)
ORDER BY
    furg.start_date;

Query to find Note Source

SELECT

    jnv.source_object_code,

    jnv.source_object_meaning,

    jov.from_table,

    COUNT(*) count

FROM

    jtf_notes_vl    jnv,

    jtf_objects_vl  jov

WHERE

    jnv.source_object_code = jov.object_code (+)

GROUP BY

    jnv.source_object_code,

    jnv.source_object_meaning,

    jov.from_table

ORDER BY

    COUNT(*) DESC;