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

SQL Query to Check Currently Running Concurrent Requests in Oracle APPS R12

SELECT fcr.request_id

      ,fcr.oracle_process_id

  ,fcr.os_process_id

  ,fcp.user_concurrent_program_name

  ,fu.user_name

  ,fcr.status_code

  ,fcr.phase_code

  ,vsp.username

  ,vsp.sid

  ,vsp.serial#

  ,vsp.program

  ,vsw.event

  ,TO_CHAR(fcr.actual_start_date,'MON-DD-HH-MI-SS') actual_start_date

  ,TO_CHAR(fcr.actual_completion_date,'MON-DD-HH-MI-SS') actual_completion_date

  FROM fnd_concurrent_requests fcr

      ,(SELECT c.username

          ,c.sid

  ,c.serial#

  ,c.program

  ,d.spid 

  FROM v$session c

      ,v$process d

         WHERE c.paddr=d.addr

) vsp

  ,fnd_concurrent_programs_tl fcp

  ,fnd_user fu

  ,v$session_wait vsw

WHERE fcr.oracle_process_id=vsp.spid

  AND fcr.concurrent_program_id=fcp.concurrent_program_id

  AND fcp.language=USERENV('LANG')

  AND fcr.requested_by=fu.user_id

  AND vsp.sid=vsw.sid

  AND fcr.status_code='R'

  AND fcr.phase_code='R'

Query to get History of Concurrent Requests which are errored out in Oracle APPS R12

SELECT a.request_id request_id

      ,a.phase_code

  ,a.status_code

      ,actual_start_date

      ,actual_completion_date

      ,c.concurrent_program_name || ': ' || ctl.user_concurrent_program_name concurrent_program_name

  FROM fnd_Concurrent_requests a

      ,fnd_concurrent_processes b

      ,fnd_concurrent_queues q

      ,fnd_concurrent_programs c

      ,fnd_concurrent_programs_tl ctl

 WHERE a.controlling_manager = b.concurrent_process_id

   AND a.concurrent_program_id = c.concurrent_program_id

   AND a.program_application_id = c.application_id

   AND a.status_code = 'E'

   AND a.phase_code = 'C'

   AND actual_start_date > SYSDATE - 10

   AND b.queue_application_id = q.application_id

   AND b.concurrent_queue_id = q.concurrent_queue_id

   AND ctl.concurrent_program_id = c.concurrent_program_id

   AND ctl.LANGUAGE = USERENV('LANG')

ORDER BY actual_completion_date

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 Long Running Concurrent Programs in R12

SELECT fcr.oracle_session_id

      ,fcr.request_id rqst_id

      ,fcr.requested_by rqst_by

      ,fu.user_name

      ,fr.responsibility_name

      ,fcr.concurrent_program_id cp_id

      ,fcp.user_concurrent_program_name cp_name

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

      ,DECODE (fcr.status_code, 'R', 'R:Running', fcr.status_code) status

      ,ROUND (((SYSDATE - fcr.actual_start_date) * 60 * 24), 2) runtime_in_min

      ,ROUND (((SYSDATE - fcr.actual_start_date) * 60 * 60 * 24), 2)runtime_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 fcr.status_code = 'R'

   AND fu.user_id = fcr.requested_by

   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 ROUND (((SYSDATE - fcr.actual_start_date) * 60 * 24), 2) > 60

   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

Query to get Concurrent Manager Status in R12

SELECT q.concurrent_queue_id

      ,q.concurrent_queue_name

  ,q.user_concurrent_queue_name

  ,q.target_node

  ,q.max_processes

  ,q.running_processes

  ,running.run running

  ,pending.pend

  ,DECODE(q.control_code, 'D', 'Deactivating'

, 'E', 'Deactivated'

, 'N', 'Node unavai'

, 'A', 'Activating'

, 'X', 'Terminated'

, 'T', 'Terminating'

, 'V', 'Verifying'

, 'O', 'Suspending'

, 'P', 'Suspended'

, 'Q', 'Resuming'

, 'R', 'Restarting') status

  FROM (SELECT concurrent_queue_name

              ,COUNT(phase_code) run

          FROM fnd_concurrent_worker_requests

         WHERE phase_code = 'R'

           AND hold_flag != 'Y'

           AND requested_start_date <= SYSDATE GROUP BY concurrent_queue_name

) running

   ,(SELECT concurrent_queue_name

           ,COUNT(phase_code) pend 

   FROM fnd_concurrent_worker_requests 

  WHERE phase_code = 'P' 

    AND hold_flag != 'Y' 

AND requested_start_date <= SYSDATE GROUP BY concurrent_queue_name

    ) pending

   ,apps.fnd_concurrent_queues_vl q

 WHERE q.concurrent_queue_name = running.concurrent_queue_name(+) 

   AND q.concurrent_queue_name = pending.concurrent_queue_name(+) 

   AND q.enabled_flag = 'Y' 

ORDER BY DECODE(q.application_id, 0, DECODE(q.concurrent_queue_id, 1, 1,4, 2))

     ,SIGN(q.max_processes) DESC

,q.concurrent_queue_name

,q.application_id

SQL Query to get the Responsibility/Request Group attached for a given Concurrent Program

SELECT DISTINCT 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 UPPER(fcpt.user_concurrent_program_name) = UPPER(:p_concurrent_program) ORDER BY frt.responsibility_name