Query to get the Concurrent Program along with Value Set details

SELECT fcpl1.user_concurrent_program_name

      ,fdfcuv.end_user_column_name

      ,ffvs.flex_value_set_name value_set_name

      ,ffvt.application_table_name

  ,ffvt.value_column_name

      ,ffvt.additional_where_clause

  FROM apps.fnd_concurrent_programs fcp1

      ,apps.fnd_descr_flex_col_usage_vl fdfcuv

      ,apps.fnd_flex_value_sets ffvs

      ,apps.fnd_flex_validation_tables ffvt

      ,apps.fnd_concurrent_programs_tl fcpl1

 WHERE 1 = 1     

   AND fcp1.concurrent_program_id IN (SELECT DISTINCT fcp.concurrent_program_id

                                        FROM apps.fnd_concurrent_programs fcp

                                            ,apps.fnd_concurrent_programs_tl fcpl

                                            ,apps.fnd_concurrent_requests fcr

                                       WHERE 1 = 1

                                         AND fcp.concurrent_program_id = fcpl.concurrent_program_id

                                         AND fcpl.language = 'US'

                                         AND fcr.concurrent_program_id = fcpl.concurrent_program_id

                                         AND trunc(fcr.request_date) BETWEEN :p_from_date AND :p_to_date

                                         AND fcp.concurrent_program_name LIKE '%XXCPNAME%'

)

   AND fdfcuv.descriptive_flexfield_name = '$SRS$.' ||fcp1.concurrent_program_name

   AND ffvs.flex_value_set_id = fdfcuv.flex_value_set_id

   AND ffvt.flex_value_set_id = ffvs.flex_value_set_id

   AND fcp1.concurrent_program_id = fcpl1.concurrent_program_id

ORDER BY fcpl1.user_concurrent_program_name 

Query to get Unposted Journals in Oracle APPS R12

SELECT gjh.accrual_rev_flag accrual_rev_flag

      ,gjh.accrual_rev_period_name accrual_rev_period_name

  ,gjh.creation_date journal_creation_date

  ,gjh.currency_code currency_code

  ,gjh.doc_sequence_value doc_sequence_value

  ,fu.email_address email_address

  ,gjh.je_category je_category

  ,gjh.last_update_date last_update_date

  ,gjh.name name journal_header_name

  ,gsobv.name ledger_name

  ,gjh.period_name period_name

  ,gjh.posted_date posted_date

  ,gjh.reversed_je_header_id reversed_je_header_id

  ,gjh.running_total_accounted_dr running_total_accounted_dr

  ,gjh.status status

  ,fu.user_name user_name

  ,gjsv.user_je_source_name user_je_source_name

  ,fu1.user_name user_name

  ,gjbv.name journal_batch_name

  ,gjbv.posted_by_name posted_by_name

  ,ppx.email_address email_address

  ,period.period_set

  ,CASE WHEN ( ( MIN(fad.attached_document_id) ) IS NOT NULL OR ( MIN(fad1.attached_document_id) ) IS NOT NULL ) 

        THEN 'Y' 

ELSE 'N' 

END document_exists

  FROM applsys.fnd_user fu

      ,gl.gl_je_headers gjh

  ,apps.gl_sets_of_books_v gsobv

  ,applsys.fnd_user fu1

  ,apps.gl_je_sources_v gjsv

  ,apps.gl_je_batches_v gjbv

  ,apps.per_people_x ppx

  ,applsys.fnd_attached_documents fad

  ,applsys.fnd_attached_documents fad1

  ,(SELECT gpv.period_name AS period_name

          ,MAX((gpv.period_set_name||gpv.period_year||LPAD(gpv.period_num,2,0))) AS period_set 

  FROM apps.gl_periods_v gpv 

WHERE (gpv.period_year >= 2024) 

GROUP BY gpv.period_name

) period

 WHERE ( (gsobv.set_of_books_id = gjh.ledger_id)

   AND (fu1.employee_id = ppx.person_id(+))

   AND (gjh.last_updated_by = fu.user_id(+))

   AND (gjh.created_by = fu1.user_id(+))

   AND (gjh.je_batch_id = gjbv.je_batch_id(+))

   AND ((TO_CHAR(gjbv.je_batch_id)) = fad.pk1_value(+))

   AND ((TO_CHAR(gjh.je_header_id)) = fad1.pk2_value(+))

   AND (gjh.je_source = gjsv.je_source_name)

   AND (gjh.period_name = period.period_name(+)))

   AND (fad.entity_name(+) = 'GL_JE_BATCHES')

   AND (fad1.entity_name(+) = 'GL_JE_HEADERS')

   AND (gjh.posted_date >= :p_posted_date)

   AND (gjsv.user_je_source_name IN (:p_source))

   AND (gjh.status IN ('U'))

   AND (gjh.period_name = :p_period_name)

GROUP BY period.period_set

        ,gjh.accrual_rev_flag

,gjh.accrual_rev_period_name

,gjh.creation_date

,gjh.currency_code

,gjh.doc_sequence_value

,fu.email_address,gjh.je_category

,gjh.last_update_date

,gjh.name

,gsobv.name

,gjh.period_name

,gjh.posted_date

,gjh.reversed_je_header_id

,gjh.running_total_accounted_dr

,gjh.running_total_dr

,gjh.status

,fu.user_name

,gjsv.user_je_source_name

,fu1.user_name

,gjbv.name

,gjbv.posted_by_name

,ppx.email_address

ORDER BY gsobv.name ASC 

        ,period.period_set ASC 

,gjsv.user_je_source_name ASC 

,gjh.doc_sequence_value ASC 

SQL Query To Get Invoice Validation And Accounting Status For The Given Check Number (Doc ID 2975480.1)

SELECT aca.check_number

      ,aia.invoice_id

  ,aia.invoice_num

  ,aia.invoice_amount

  ,distribution_line_number

  ,aida.accrual_posted_flag

  ,decode(aida.match_status_flag,

              'A', 'Validated',

              'N', 'Never Validated',

              'T', 'Needs Revalidation',

              'S', 'Stopped') "Invoice Validation Status"

  ,decode(ap_invoices_pkg.get_posting_status(aia.invoice_id),

              'S', 'Selected for Accounting',

              'P', 'Partial',

              'N', 'Unaccounted',

              'Y', 'Accounted',

              'Unaccounted') "Invoice Accounting Status",

      aida.invoice_distribution_id,

      aida.accounting_event_id,

      apps.ap_invoices_pkg.get_approval_status(aia.invoice_id,

                                               aia.invoice_amount,

                                               aia.payment_status_flag,

                                               aia.invoice_type_lookup_code)"Invoice Approval Status",

      decode(aida.posted_flag,

              'Y', 'Posted',

              'N', 'Not Posted',

              'Not Posted') "Invoice Posting Status"   

FROM ap_checks_all aca

    ,ap_invoice_payments_all aipa

,ap_invoices_all aia

,ap_suppliers asa

,ap_invoice_distributions_all aida

WHERE aca.check_id = aipa.check_id

AND aipa.invoice_id = aia.invoice_id

AND aia.vendor_id = asa.vendor_id

AND aida.invoice_id = aia.invoice_id

AND aca.check_number = <pass check number>

How To Populate the Phone Number Field in Oracle Loans? (Doc ID 2990762.1)

SELECT party.party_name Borrower_Name

      ,org.name operating_unit

  ,loan.loan_number

  ,contact_person.party_name contact_person_name

  ,con_phone.raw_phone_number contact_phone_number

  FROM hz_parties party

      ,lns_loan_headers_all loan

  ,hr_all_organization_units_tl org

  ,hz_parties contact_person

  ,hz_contact_points con_phone

 WHERE party.party_id = loan.primary_borrower_id

   AND org.organization_id = loan.org_id

   AND loan.contact_pers_party_id = contact_person.party_id(+)

   AND con_phone.owner_table_name(+) = 'HZ_PARTIES'

   AND con_phone.owner_table_id(+) = loan.contact_rel_party_id

   AND con_phone.primary_flag(+) = 'Y'

   AND con_phone.status(+) = 'A'

   AND con_phone.contact_point_type(+) = 'PHONE'

   AND loan.loan_id = :p_loan_id

Query to get Parent and Child Accounts in Oracle APPS R12

SELECT ffv1.flex_value parent_account

      ,ffvt1.description parent_account_desc

  ,ffv2.flex_value child_account

  ,ffvt2.description child_account_desc

  FROM fnd_flex_values_tl ffvt2

      ,fnd_flex_values ffv2

      ,fnd_flex_value_hierarchies ffvh

      ,fnd_flex_values_tl ffvt1

      ,fnd_flex_values ffv1

      ,fnd_id_flex_segments fifs

 WHERE fifs.application_id = 101

   AND fifs.id_flex_code = 'GL#'

   AND fifs.id_flex_num = 101

   AND fifs.application_column_name = 'SEGMENT4'

   AND fifs.enabled_flag = 'Y'

   AND ffv1.enabled_flag = 'Y'

   AND ffv1.summary_flag = 'Y'

   AND ffv1.flex_value != 'T'

   AND ffv1.flex_value_set_id = fifs.flex_value_set_id + 0

   AND NVL (TO_CHAR (ffv1.start_date_active, 'YYYY/MM/DD'), '0000/00/00') <= TO_CHAR (SYSDATE, 'YYYY/MM/DD')

   AND NVL (TO_CHAR (ffv1.end_date_active, 'YYYY/MM/DD'), '9999/99/99') >= TO_CHAR (SYSDATE, 'YYYY/MM/DD')

   AND ffvt1.flex_value_id = ffv1.flex_value_id + 0

   AND ffvt1.language = USERENV ('LANG')

   AND ffvh.flex_value_set_id = fifs.flex_value_set_id + 0

   AND ffvh.parent_flex_value = ffv1.flex_value

   AND ffv2.summary_flag = 'N'

   AND NVL (TO_CHAR (ffv2.start_date_active, 'YYYY/MM/DD'), '0000/00/00') <= TO_CHAR (SYSDATE, 'YYYY/MM/DD')

   AND NVL (TO_CHAR (ffv2.end_date_active, 'YYYY/MM/DD'), '9999/99/99') >= TO_CHAR (SYSDATE, 'YYYY/MM/DD')

   AND ffv2.flex_value_set_id + 0 = fifs.flex_value_set_id + 0

   AND ffv2.flex_value BETWEEN ffvh.child_flex_value_low AND ffvh.child_flex_value_high

   AND ffv2.enabled_flag = 'Y'

   AND ffvt2.flex_value_id = ffv2.flex_value_id + 0

   AND ffvt2.language = USERENV ('LANG')

ORDER BY ffv1.flex_value, ffv2.flex_value

Query to get Employee Assignment Status in Oracle Fusion

SELECT papf.person_number

      ,pastt.user_status assignment_status

  FROM per_all_people_f papf

      ,per_all_assignments_m paam

  ,per_assignment_status_types past

  ,per_assignment_status_types_tl pastt

 WHERE papf.person_id = paam.person_id

   AND paam.assignment_status_type_id = past.assignment_status_type_id

   AND past.assignment_status_type_id = pastt.assignment_status_type_id

   AND pastt.source_lang = USERENV('LANG')

   AND TRUNC(SYSDATE) BETWEEN papf.effective_start_date AND papf.effective_end_date

   AND paam.primary_assignment_flag = 'Y'

   AND paam.assignment_type = 'E'

   and paam.effective_latest_change = 'Y'

   AND TRUNC(SYSDATE) BETWEEN paam.effective_start_date AND paam.effective_end_date

   AND TRUNC(SYSDATE) BETWEEN past.start_date AND NVL(past.end_date,SYSDATE)

   AND papf.person_number = nvl(:p_person_number,papf.person_number)

ORDER BY papf.person_number asc

        ,pastt.user_status 

Query to get Element Entry details in Oracle Fusion HCM

SELECT peef.* 

  FROM per_all_people_f papf

      ,pay_element_entries_f peef

  ,pay_element_types_f pet

 WHERE peef.element_type_id = pet.element_type_id

   AND papf.person_id = peef.person_id

   AND papf.person_number = 'E-12345'

   AND peef.creator_type = 'BEN'

   AND pet.base_element_name= 'XX_ELEMENT_NAME'  

Mandatory Input value for HCM Elements in Oracle Fusion

SELECT petf.element_type_id

      ,pettl.element_name

      ,petf.processing_type Recurring_NonRecurring

      ,petf.effective_start_date

      ,petf.effective_end_date

      ,petf.multiple_entries_allowed_flag

      ,pivf.base_name

      ,pivf.mandatory_flag

  FROM pay_element_types_f petf

      ,pay_element_types_tl pettl

      ,pay_input_values_f pivf

 WHERE petf.element_type_id = pettl.element_type_id

   AND pivf.element_type_id=petf.element_type_id

   AND pettl.element_name = 'XX_ELEMENT_NAME'

   AND pettl.language=USERENV('LANG')

   AND pivf.user_enterable_flag='Y'

Query to get Grade Rate details in Oracle ERP

SELECT prft.name "ratename"

      ,pg.name "gradename"

      ,prvf.minimum

      ,prvf.maximum

      ,prvf.mid_value

      ,prft.effective_start_date "ab"

      ,prvf.effective_start_date "cd"

      ,pg.effective_start_date "ef"

  FROM per_rates_f_tl    prft

      ,per_rate_values_f prvf

      ,per_grades        pg

 WHERE prft.rate_id        = prvf.rate_id

   AND prvf.rate_object_id = pg.grade_id

   AND prvf.rate_object_type = 'GRADE'

   AND prft.name IN ('XX Rate Name')

   AND PRFT.LANGUAGE = SYS_CONTEXT('USERENV', 'LANG')

   AND TRUNC(SYSDATE) BETWEEN prft.effective_start_date AND prft.effective_end_date

   AND TRUNC(SYSDATE) BETWEEN prvf.effective_start_date AND prvf.effective_end_date

   AND TRUNC(SYSDATE) BETWEEN pg.effective_start_date AND pg.effective_end_date

ORDER BY prft.name

       , pg.name

Exclude Freight from Discount(Supplier) in Oracle APPS R12

SELECT aps.vendor_name

      ,aps.exclude_freight_from_discount vend_excd

  ,apss.vendor_site_code

  ,apss.exclude_freight_from_discount site_excd

  FROM ap_suppliers aps

      ,ap_supplier_sites_all apss

 WHERE aps.vendor_id = apss.vendor_id

   AND apss.exclude_freight_from_discount IS NULL

   AND aps.exclude_freight_from_discount IS NULL

   --AND aps.vendor_id NOT IN (1001)

   

SQL query to get list of Jurisdictions for which Tax Rates has been defined in Oracle APPS R12

SELECT zj.tax

      ,zj.tax_jurisdiction_code

  ,hzg.geography_element2_code state_code

  ,hzg.geography_element3_code county_code

  ,hzg.geography_element4_code city_code

  FROM zx_jurisdictions_b zj

      ,hz_geographies hzg

 WHERE zj.tax_regime_codE='XX_SALE_TAX'

   AND SYSDATE BETWEEN zj.effective_from AND NVL(zj.effective_to,'31-DEC-4999')

   AND SYSDATE BETWEEN hzg.start_date AND hzg.end_date

   AND zj.zone_geography_id=hzg.geography_id

   AND zj.tax=hzg.geography_type

   AND NOT EXISTS (SELECT 1 

                     FROM zx_rates_b zr

                    WHERE zr.tax_regime_code='XX_SALE_TAX'

                      AND zr.tax_jurisdiction_code=zj.tax_jurisdiction_code

   )

ORDER BY zj.tax

        ,zj.tax_jurisdiction_code

    ,hzg.geography_element2_code

,hzg.geography_element3_code

,hzg.geography_element4_code

Query to get Customer Address that doesn't have Geography Reference in Oracle APPS R12

SELECT hca.account_number

      ,hca.account_name

      ,hcs_ship.site_use_code

      ,hl_ship.address1

      ,hl_ship.state

      ,hl_ship.county

      ,hl_ship.city

      ,hl_ship.postal_code

  FROM hz_cust_site_uses_all hcs_ship

      ,hz_cust_acct_sites_all hca_ship

      ,hz_cust_accounts hca

      ,hz_party_sites hps_ship

      ,hz_locations hl_ship

 WHERE hca.cust_account_id=hca_ship.cust_account_id(+)

   AND hcs_ship.cust_acct_site_id(+) = hca_ship.cust_acct_site_id

   AND hca_ship.party_site_id = hps_ship.party_site_id

   AND hps_ship.location_id = hl_ship.location_id

   AND hca.status='A'

   AND hcs_ship.status='A'

   AND hca_ship.status='A'

   AND hl_ship.country='US'

   --AND hca.account_number='1234567890'

   AND NOT EXISTS (SELECT 1 

                     FROM hz_geographies hg

                    WHERE hg.geography_element2_code=hl_ship.state

                      AND UPPER(hl_ship.county)=UPPER(hg.geography_element3_code)

                      AND UPPER(hl_ship.city)=UPPER(hg.geography_element4_code)

                      AND SYSDATE BETWEEN hg.start_date AND hg.end_date

  )

Query to get Errored or Warning Concurrent Program Request details in R12

SELECT fu.user_name

      ,fcpt.user_concurrent_program_name

  ,fcpt.description

  ,fcp.concurrent_program_name

  ,fcr.request_id

  ,((fcr.actual_completion_date

         -DECODE (TRUNC(fcr.request_date)

         ,fcr.requested_start_date

,fcr.request_date

,fcr.requested_start_date

))*60*24) wait_time_min

      ,DECODE(fcr.phase_code,'C','Completed'

                        ,'R','Running'

,fcr.phase_code

) phase_code

  ,DECODE(fcr.status_code,'C','Completed'

                         ,'R','Running'

,'W','Paused'

,'E','Error'

,'G','Warning'

,fcr.status_code

) status_code

  ,TO_CHAR(fcr.request_date,'DD/MM/YYYY HH24:MI:SS') request_date

  ,TO_CHAR(fcr.requested_start_date,'DD/MM/YYYY HH24:MI:SS') start_time

  ,TO_CHAR(fcr.actual_completion_date,'DD/MM/YYYY HH24:MI:SS') complete_time

  FROM fnd_concurrent_requests fcr

      ,fnd_concurrent_programs fcp

  ,fnd_concurrent_programs_tl fcpt

  ,fnd_user fu

 WHERE 1=1

   AND fcp.concurrent_program_id=fcr.concurrent_program_id

   AND fcpt.concurrent_program_id=fcp.concurrent_program_id

   AND fcr.requested_by = fu.user_id

   AND TRUNC(fcr.request_date) BETWEEN SYSDATE - 5 AND SYSDATE

   AND fcr.status_code IN ('E','G')

ORDER BY fcr.status_code

        ,fcp.concurrent_program_name

,fcr.request_id

fnd_concurrent_requests.status_code in Oracle APPS R12(CP_STATUS_CODE)

SELECT lookup_code

      ,meaning 

  FROM fnd_lookups 

 WHERE lookup_type = 'CP_STATUS_CODE'

ORDER BY lookup_code

SQL Query to find Request Set details in R12

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 frt.language = USERENV('LANG')

   AND fcpt.language = USERENV('LANG')

   AND fcpt.user_request_set_name = :p_request_set_name

order by frt.responsibility_name

      ,frg.request_group_name

  ,frgu.request_unit_type

SQL Query to check Concurrent Program details in R12

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 frt.language = USERENV('LANG')

   AND fcpt.language = USERENV('LANG')

   AND fcpt.user_concurrent_program_name = :p_conc_program_name

ORDER BY frt.responsibility_name

      ,frg.request_group_name

  ,frgu.request_unit_type

SQL Query to check Concurrent Requests SID & OS Process Details for given Request ID in R12

SELECT fcr.request_id

      ,fcr.argument_text

      ,fcr.phase_code

  ,fcr.status_code

  ,fcr.oracle_process_id

  ,fcr.os_process_id

  ,f.user_name

  FROM fnd_concurrent_requests fcr

      ,fnd_user f 

WHERE fcr.request_id = :p_request_id

  AND fcr.requested_by=f.user_id

Query to get the Running History of Concurrent Programs in Oracle APPS R12

SELECT fcr.oracle_session_id o_sid

      ,ROUND (( ( NVL (fcr.actual_completion_date, SYSDATE)- fcr.actual_start_date)* 60* 24),2) runtime_min

      ,fcr.request_id request_id

      ,fcr.requested_by requested_by

      ,fu.user_name user_name

      ,fr.responsibility_name

      ,fcr.concurrent_program_id concurrent_program_id

      ,fcp.user_concurrent_program_name concurrent_program_name

      ,TO_CHAR (fcr.actual_start_date, 'DD-MON-YYYY HH24:MI:SS') start_time

      ,DECODE (fcr.status_code ,'C', 'C:Completed','G', 'G:Warning','E', 'E:Error','Q','Q:Queued',fcr.status_code) status

      ,TO_CHAR (fcr.actual_completion_date, 'DD-MON-YYYY HH24:MI:SS') end_time

      ,ROUND (( ( NVL (fcr.actual_completion_date, SYSDATE)- fcr.actual_start_date)* 60* 60* 24),2) run_time_in_sec

      ,fcr.oracle_process_id "oracle_pid/SPID"

      ,fcr.os_process_id os_pid

      ,fcr.argument_text

      ,fcr.outfile_name

      ,fcr.logfile_name

      ,fcr.enable_trace

  FROM fnd_concurrent_requests fcr

      ,fnd_user fu

      ,fnd_responsibility_tl fr

      ,fnd_concurrent_programs_tl fcp

 WHERE fcp.user_concurrent_program_name LIKE 'XX Concurrent Program Name%'

    AND fr.responsibility_id = fcr.responsibility_id

   AND fcr.concurrent_program_id = fcp.concurrent_program_id

   AND fcr.program_application_id = fcp.application_id

   AND fu.user_id = fcr.requested_by

   --AND fcr.request_id = 1234567890

ORDER BY fcr.concurrent_program_id

        ,request_id DESC

Query to get Trace file Including SID in Oracle APPS R12

SELECT 'Request id: '||request_id 

      ,'Trace id: '||oracle_Process_id

  ,'Trace Flag: '||req.enable_trace

  ,'Trace Name: '||dest.value||'/'||LOWER(dbnm.value)

                 ||'_ora_'||oracle_process_id||'.trc', 'Prog. Name: '

                     ||prog.user_concurrent_program_name

  ,'File Name: '||execname.execution_file_name|| execname.subroutine_name 

  ,'Status : '||DECODE(phase_code,'R','Running') ||'-'|| DECODE(status_code,'R','Normal')

  ,'SID Serial: '||ses.sid||','|| ses.serial#, 'Module : '||ses.module 

  FROM fnd_concurrent_requests req

      ,v$session ses

  ,v$process proc

  ,v$parameter dest

  ,v$parameter dbnm

  ,fnd_concurrent_programs_vl prog

  ,fnd_executables execname 

 WHERE req.request_id = :p_request_id

   AND req.oracle_process_id=proc.spid(+)

   AND proc.addr = ses.paddr(+) 

   AND dest.name='user_dump_dest' 

   AND dbnm.name='db_name'

   AND req.concurrent_program_id = prog.concurrent_program_id 

   AND req.program_application_id = prog.application_id

   AND prog.application_id = execname.application_id 

   AND prog.executable_id=execname.executable_id

SQL to find out the Raw Trace Name and Location for the Concurrent Program

SELECT req.request_id

      ,req.logfile_node_name node

      ,req.oracle_Process_id

      ,req.enable_trace

      ,dest.VALUE||'/'||LOWER(dbnm.VALUE)||'_ora_'||oracle_process_id||'.trc' trace_filename

      ,prog.user_concurrent_program_name

      ,execname.execution_file_name

      ,execname.subroutine_name

      ,phase_code

      ,status_code

      ,ses.SID

      ,ses.serial#

      ,ses.module

      ,ses.machine

  FROM fnd_concurrent_requests req

      ,v$session ses

      ,v$process proc

      ,v$parameter dest

      ,v$parameter dbnm

      ,fnd_concurrent_programs_vl prog

      ,fnd_executables execname

 WHERE 1=1

   AND req.request_id = :p_request_id

   AND req.oracle_process_id = proc.spid(+)

   AND proc.addr = ses.paddr(+)

   AND dest.name='user_dump_dest'

   AND dbnm.name='db_name'

   AND req.concurrent_program_id = prog.concurrent_program_id

   AND req.program_application_id = prog.application_id

   AND prog.application_id = execname.application_id

   AND prog.executable_id=execname.executable_id