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

Query to get failed records in Invoices Interface in Oracle APPS R12

SELECT aif.invoice_id

              ,ailf.invoice_line_id

  ,aif.invoice_num

  ,air.reject_lookup_code

  ,aif.invoice_amount

  ,ailf.amount line_amount

  ,ailf.line_number

  ,aif.source

  ,ailf.accounting_date

  ,aif.gl_date

  ,aif.invoice_date

  ,aif.creation_date

  ,(SELECT vendor_name

                  FROM ap_suppliers

                 WHERE vendor_id = aif.vendor_id) vendor_name

  ,(SELECT end_date_active

                  FROM ap_suppliers

                 WHERE vendor_id = aif.vendor_id) vendor_end_date_active

  ,(SELECT last_update_date

                  FROM ap_suppliers

                 WHERE vendor_id = aif.vendor_id) vendor_last_update_date

  ,(SELECT segment1

                  FROM po_headers_all

                 WHERE po_header_id = ailf.po_header_id) po_number

  ,(SELECT name

                  FROM hr_operating_units

                 WHERE organization_id = aif.org_id) operating_unit

          FROM ap_invoices_interface aif

      ,ap_interface_rejections air

  ,ap_invoice_lines_interface ailf

         WHERE aif.invoice_id = ailf.invoice_id

           AND ailf.invoice_line_id = air.parent_id

           -- AND    aif.group_id        =v_group_id

           AND air.parent_table = 'AP_INVOICE_LINES_INTERFACE'

   --AND aif.source = 'ERS'

           AND aif.status = 'REJECTED'

        UNION

        SELECT aif.invoice_id

      ,ailf.invoice_line_id

  ,aif.invoice_num

  ,air.reject_lookup_code

  ,aif.invoice_amount

  ,ailf.amount line_amount

  ,ailf.line_number

  ,aif.source

  ,ailf.accounting_date

  ,aif.gl_date

  ,aif.invoice_date

  ,aif.creation_date

  ,(SELECT vendor_name

                  FROM ap_suppliers

                 WHERE vendor_id = aif.vendor_id) vendor_name

  ,(SELECT end_date_active

                  FROM ap_suppliers

                 WHERE vendor_id = aif.vendor_id) vendor_end_date_active

  ,(SELECT last_update_date

                  FROM ap_suppliers

                 WHERE vendor_id = aif.vendor_id) vendor_last_update_date

  ,(SELECT segment1

                  FROM po_headers_all

                 WHERE po_header_id = ailf.po_header_id) po_number

  ,(SELECT name

                  FROM hr_operating_units

                 WHERE organization_id = aif.org_id) operating_unit

          FROM ap_invoices_interface aif

      ,ap_interface_rejections air

  ,ap_invoice_lines_interface ailf

         WHERE aif.invoice_id = ailf.invoice_id

           AND aif.invoice_id = air.parent_id

           -- AND    aif.group_id        =v_group_id

           AND air.parent_table = 'AP_INVOICES_INTERFACE'

           --AND aif.source = 'ERS'

   AND aif.status = 'REJECTED'

Query to get GRN Details in Oracle APPS R12

SELECT    

       DISTINCT rsl.to_subinventory

           ,rts.invoice_status_code

   ,phs.org_id

   ,phs.segment1 po_number

   ,rts.transaction_date

           FROM po_vendor_sites_all pvss

               ,po_vendor_sites_all pvss2

               ,po_vendors pvds

               ,po_headers_all phs

               ,po_lines_all pls

               ,po_line_locations_all plls

               ,rcv_shipment_headers rsh

               ,rcv_shipment_lines rsl

               ,inl_ship_lines_all isl

               ,rcv_transactions rts

               ,mtl_system_items_b msi

          WHERE rts.shipment_header_id = rsh.shipment_header_id

            AND rts.po_header_id = phs.po_header_id

            AND rts.po_line_location_id = plls.line_location_id

            AND rts.po_line_id = pls.po_line_id

            AND rts.shipment_header_id = rsl.shipment_header_id

            AND rts.shipment_line_id = rsl.shipment_line_id

            AND rts.lcm_shipment_line_id = isl.ship_line_id(+)

            AND rts.lcm_adjustment_num = isl.adjustment_num(+)

            AND phs.vendor_id = pvds.vendor_id

            AND phs.vendor_site_id = pvss.vendor_site_id

            AND rsh.receipt_source_code = 'VENDOR'

            AND rts.source_document_code = 'PO' 

            AND phs.segment1 = '1234567890'           --- Purchase Order Number/LPO Number

            --AND NVL (rts.invoice_status_code, 'NA') IN ('PENDING', 'REJECTED')

            AND (   (rts.transaction_type = 'RECEIVE')

                 OR (    'N' = 'Y'

                     AND rts.transaction_type = 'CORRECT'

                     AND rts.timecard_id IS NOT NULL

                     AND EXISTS (

                            SELECT 1

                              FROM apps.rcv_transactions prt

                             WHERE prt.transaction_type = 'RECEIVE'

                               AND prt.source_document_code = 'PO'

                               AND prt.transaction_id =

                                                     rts.parent_transaction_id

                               AND prt.invoice_status_code = 'INVOICED'

                               AND prt.last_update_date <= rts.creation_date)

                    )

                )

            AND pvss.pay_on_code IN ('RECEIPT', 'RECEIPT_AND_USE')

            AND phs.pay_on_code IN ('RECEIPT', 'RECEIPT_AND_USE')

            AND NVL (plls.consigned_flag, 'N') <> 'Y'

            AND pvss2.vendor_site_id = NVL (pvss.default_pay_site_id, pvss.vendor_site_id)

            AND NVL (rsh.asn_type, ' ') <> 'ASBN'

            AND rts.po_release_id IS NULL

            AND rsl.item_id = msi.inventory_item_id

            AND rts.organization_id = msi.organization_id

            AND rts.transaction_date >= '01-JAN-2024'

Query to get POR is Enabled at Supplier Site and PO Level in Oracle APPS R12

SELECT DISTINCT apss.pay_on_code por_supp_site

               ,pha.pay_on_code por_po

           FROM ap_suppliers aps

       ,ap_supplier_sites_all apss

   ,po_headers_all pha

          WHERE aps.vendor_id = apss.vendor_id

            AND pha.vendor_id = apss.vendor_id

            AND pha.vendor_site_id = apss.vendor_site_id

            AND pha.segment1 = '1234567890'  --- Purchase Order Number/LPO Number

Query to get Inventory Item details in R12

SELECT MTL_SYSTEM_ITEMS_B.LAST_UPDATE_DATE

      ,MTL_SYSTEM_ITEMS_B.LAST_UPDATED_BY

  ,MTL_SYSTEM_ITEMS_B.CREATION_DATE

  ,MTL_SYSTEM_ITEMS_B.CREATED_BY

  ,MTL_SYSTEM_ITEMS_B.DESCRIPTION

  ,MTL_SYSTEM_ITEMS_B.SEGMENT1

  ,MTL_SYSTEM_ITEMS_B.SHELF_LIFE_CODE

  ,MTL_SYSTEM_ITEMS_B.UNIT_WEIGHT

  ,MTL_SYSTEM_ITEMS_B.WEIGHT_UOM_CODE

  ,MTL_SYSTEM_ITEMS_B.VOLUME_UOM_CODE

  ,MTL_SYSTEM_ITEMS_B.UNIT_VOLUME

  ,MTL_SYSTEM_ITEMS_B.PRIMARY_UOM_CODE

  ,MTL_SYSTEM_ITEMS_B.INVENTORY_ITEM_ID

  ,MTL_SYSTEM_ITEMS_B.ITEM_TYPE

  ,MTL_PARAMETERS.MASTER_ORGANIZATION_ID

  ,MTL_SYSTEM_ITEMS_B.HAZARD_CLASS_ID

  ,MTL_SYSTEM_ITEMS_B.SERIAL_NUMBER_CONTROL_CODE

  ,MTL_SYSTEM_ITEMS_B.PLANNING_MAKE_BUY_CODE

  ,MTL_SYSTEM_ITEMS_B.BASE_ITEM_ID

  ,MTL_SYSTEM_ITEMS_B.START_DATE_ACTIVE

  ,MTL_SYSTEM_ITEMS_B.RETURNABLE_FLAG

  ,MTL_SYSTEM_ITEMS_B.PURCHASING_ENABLED_FLAG

  ,MTL_SYSTEM_ITEMS_B.SERVICEABLE_PRODUCT_FLAG

  ,MTL_SYSTEM_ITEMS_B.END_DATE_ACTIVE

  ,MTL_SYSTEM_ITEMS_B.FIXED_ORDER_QUANTITY

  ,MTL_SYSTEM_ITEMS_B.ORDER_COST

  ,MTL_SYSTEM_ITEMS_B.INVENTORY_ITEM_FLAG

  ,MTL_SYSTEM_ITEMS_B.CUSTOMER_ORDER_ENABLED_FLAG 

  FROM MTL_SYSTEM_ITEMS_B

      ,MTL_PARAMETERS 

 WHERE MTL_SYSTEM_ITEMS_B.ORGANIZATION_ID = MTL_PARAMETERS.ORGANIZATION_ID 

   AND MTL_PARAMETERS.MASTER_ORGANIZATION_ID = MTL_PARAMETERS.ORGANIZATION_ID                             

Query to get GL Journal Details in R12

SELECT JEL.JE_HEADER_ID

      ,JEL.JE_LINE_NUM

  ,JEL.LAST_UPDATE_DATE

      ,JEL.LAST_UPDATED_BY

  ,JEL.LEDGER_ID

  ,JEL.CODE_COMBINATION_ID

      ,JEL.PERIOD_NAME

  ,JEL.STATUS

  ,JEL.CREATION_DATE

  ,JEL.CREATED_BY

  ,JEL.ENTERED_DR

  ,JEL.ENTERED_CR

  ,JEL.ACCOUNTED_DR

  ,JEL.ACCOUNTED_CR

  ,JEL.REFERENCE_1

  ,JEL.REFERENCE_2

  ,JEL.REFERENCE_3

  ,JEL.REFERENCE_4

  ,JEL.REFERENCE_5

  ,JEL.REFERENCE_6

  ,JEL.REFERENCE_7

  ,JEL.REFERENCE_8

  ,JEL.REFERENCE_9

  ,JEL.REFERENCE_10

  ,JEL.GL_SL_LINK_ID

  ,JEH.JE_CATEGORY

  ,JEH.JE_SOURCE

  ,JEH.NAME

  ,JEH.CURRENCY_CODE

  ,JEH.POSTED_DATE

  ,JEB.NAME

  ,PRDS.START_DATE

  ,PRDS.END_DATE

  ,GL.LEDGER_CATEGORY_CODE

  ,PRDS.ADJUSTMENT_PERIOD_FLAG 

  FROM GL_JE_LINES JEL

      ,GL_JE_HEADERS JEH

      ,GL_JE_BATCHES JEB

      ,GL_PERIOD_STATUSES PRDS

  ,GL_LEDGERS GL 

 WHERE JEL.JE_HEADER_ID = JEH.JE_HEADER_ID 

   AND JEH.ACTUAL_FLAG = 'A' 

   AND JEH.JE_BATCH_ID = JEB.JE_BATCH_ID (+) 

   AND JEL.PERIOD_NAME = PRDS.PERIOD_NAME 

   AND JEL.LEDGER_ID = PRDS.SET_OF_BOOKS_ID 

   AND JEL.LEDGER_ID = GL.LEDGER_ID 

   AND PRDS.APPLICATION_ID = 101 

   AND JEH.CURRENCY_CODE <> 'STAT' 

   AND JEB.STATUS = 'P' 

Query to get Sales Invoice(Receivables) Details in R12

SELECT RA_CUST_TRX_TYPES_ALL.TYPE,

       RA_CUSTOMER_TRX_LINES_ALL.CUSTOMER_TRX_LINE_ID,

       RA_CUSTOMER_TRX_LINES_ALL.LAST_UPDATE_DATE,

       RA_CUSTOMER_TRX_LINES_ALL.LAST_UPDATED_BY,

       RA_CUSTOMER_TRX_LINES_ALL.CREATION_DATE,

       RA_CUSTOMER_TRX_LINES_ALL.CREATED_BY,

       RA_CUSTOMER_TRX_LINES_ALL.CUSTOMER_TRX_ID,

       RA_CUSTOMER_TRX_LINES_ALL.LINE_NUMBER,

       RA_CUSTOMER_TRX_LINES_ALL.SET_OF_BOOKS_ID,

       RA_CUSTOMER_TRX_LINES_ALL.INVENTORY_ITEM_ID,

       RA_CUSTOMER_TRX_LINES_ALL.QUANTITY_INVOICED,

       RA_CUSTOMER_TRX_LINES_ALL.QUANTITY_CREDITED,

       RA_CUSTOMER_TRX_LINES_ALL.UNIT_STANDARD_PRICE,

       RA_CUSTOMER_TRX_LINES_ALL.UNIT_SELLING_PRICE,

       RA_CUSTOMER_TRX_LINES_ALL.SALES_ORDER,

       RA_CUSTOMER_TRX_LINES_ALL.SALES_ORDER_LINE,

       RA_CUSTOMER_TRX_LINES_ALL.LINE_TYPE,

       RA_CUSTOMER_TRX_LINES_ALL.EXTENDED_AMOUNT,

       RA_CUSTOMER_TRX_LINES_ALL.TAX_RATE,

       RA_CUSTOMER_TRX_LINES_ALL.UOM_CODE,

       RA_CUSTOMER_TRX_LINES_ALL.ORG_ID,

       RA_CUSTOMER_TRX_LINES_ALL.INTERFACE_LINE_CONTEXT,

       RA_CUSTOMER_TRX_LINES_ALL.INTERFACE_LINE_ATTRIBUTE6,

       RA_CUSTOMER_TRX_LINES_ALL.INTERFACE_LINE_ATTRIBUTE7,

       RA_CUSTOMER_TRX_LINES_ALL.LINK_TO_CUST_TRX_LINE_ID,

       RA_CUSTOMER_TRX_LINES_ALL.WAREHOUSE_ID,

       RA_CUSTOMER_TRX_ALL.CUSTOMER_TRX_ID,

       RA_CUSTOMER_TRX_ALL.TRX_NUMBER,

       RA_CUSTOMER_TRX_ALL.CUST_TRX_TYPE_ID,

       RA_CUSTOMER_TRX_ALL.TRX_DATE,

       RA_CUSTOMER_TRX_ALL.SOLD_TO_CUSTOMER_ID,

       OE_ORDER_HEADERS_ALL.SOLD_TO_CONTACT_ID,

       RA_CUSTOMER_TRX_ALL.SOLD_TO_SITE_USE_ID,

       RA_CUSTOMER_TRX_ALL.BILL_TO_CUSTOMER_ID,

       RA_CUSTOMER_TRX_ALL.BILL_TO_SITE_USE_ID,

       RA_CUSTOMER_TRX_ALL.SHIP_TO_CUSTOMER_ID,

       RA_CUSTOMER_TRX_ALL.SHIP_TO_SITE_USE_ID,

       RA_CUSTOMER_TRX_ALL.TERM_ID,

       RA_CUSTOMER_TRX_ALL.TERM_DUE_DATE,

       RA_CUSTOMER_TRX_ALL.PRIMARY_SALESREP_ID,

       RA_CUSTOMER_TRX_ALL.PRINTING_ORIGINAL_DATE,

       RA_CUSTOMER_TRX_ALL.PURCHASE_ORDER,

       RA_CUSTOMER_TRX_ALL.EXCHANGE_DATE,

       RA_CUSTOMER_TRX_ALL.EXCHANGE_RATE,

       RA_CUSTOMER_TRX_ALL.TERRITORY_ID,

       RA_CUSTOMER_TRX_ALL.INVOICE_CURRENCY_CODE,

       RA_CUSTOMER_TRX_ALL.COMPLETE_FLAG,

       RA_CUSTOMER_TRX_ALL.PAYING_SITE_USE_ID,

       RA_CUSTOMER_TRX_LINES_ALL1.CUSTOMER_TRX_LINE_ID,

       RA_CUSTOMER_TRX_LINES_ALL1.INVENTORY_ITEM_ID,

       RA_CUSTOMER_TRX_LINES_ALL1.SALES_ORDER,

       RA_CUSTOMER_TRX_LINES_ALL1.SALES_ORDER_LINE,

       RA_CUSTOMER_TRX_LINES_ALL1.INTERFACE_LINE_CONTEXT,

       RA_CUSTOMER_TRX_LINES_ALL1.INTERFACE_LINE_ATTRIBUTE6,

       RA_CUSTOMER_TRX_LINES_ALL1.INTERFACE_LINE_ATTRIBUTE7,

       RA_CUSTOMER_TRX_LINES_ALL1.WAREHOUSE_ID,

       RA_CUSTOMER_TRX_ALL.STATUS_TRX,

       RA_CUSTOMER_TRX_ALL.EXCHANGE_RATE_TYPE,

       RA_CUSTOMER_TRX_ALL.LAST_UPDATE_DATE,

       RA_CUST_TRX_TYPES_ALL.LAST_UPDATE_DATE,

       OE_ORDER_HEADERS_ALL.LAST_UPDATE_DATE,

       OE_ORDER_HEADERS_ALL.SALES_CHANNEL_CODE,

       OE_ORDER_HEADERS_ALL.ORDER_TYPE_ID,

       OE_ORDER_HEADERS_ALL.PAYMENT_TYPE_CODE,

       OE_ORDER_HEADERS_ALL.FREIGHT_TERMS_CODE,

       OE_ORDER_LINES_ALL.LINE_CATEGORY_CODE,

       OE_ORDER_LINES_ALL.ITEM_TYPE_CODE,

       OE_ORDER_LINES_ALL.SHIPMENT_NUMBER,

       OE_ORDER_LINES_ALL.SHIP_FROM_ORG_ID,

       OE_ORDER_LINES_ALL.PROJECT_ID,

       OE_ORDER_LINES_ALL.TASK_ID,

       OE_ORDER_LINES_ALL.OPTION_NUMBER,

       OE_ORDER_LINES_ALL.COMPONENT_NUMBER,

       OE_ORDER_LINES_ALL.SOURCE_TYPE_CODE,

       OE_ORDER_HEADERS_ALL.ORDER_SOURCE_ID,

       RA_CUSTOMER_TRX_ALL.LEGAL_ENTITY_ID,

       RA_CUSTOMER_TRX_LINES_ALL.INTERFACE_LINE_ATTRIBUTE11

  FROM RA_CUSTOMER_TRX_LINES_ALL  RA_CUSTOMER_TRX_LINES_ALL1,

       RA_CUSTOMER_TRX_LINES_ALL,

       RA_CUSTOMER_TRX_ALL,

       RA_CUST_TRX_TYPES_ALL,

       OE_ORDER_HEADERS_ALL,

       OE_ORDER_LINES_ALL

 WHERE     RA_CUSTOMER_TRX_ALL.CUSTOMER_TRX_ID =

           RA_CUSTOMER_TRX_LINES_ALL.CUSTOMER_TRX_ID

       AND RA_CUSTOMER_TRX_ALL.COMPLETE_FLAG = 'Y'

       AND RA_CUSTOMER_TRX_ALL.CUST_TRX_TYPE_ID =

           RA_CUST_TRX_TYPES_ALL.CUST_TRX_TYPE_ID

       AND RA_CUSTOMER_TRX_ALL.ORG_ID = RA_CUST_TRX_TYPES_ALL.ORG_ID

       AND RA_CUSTOMER_TRX_LINES_ALL.LINK_TO_CUST_TRX_LINE_ID =

           RA_CUSTOMER_TRX_LINES_ALL1.CUSTOMER_TRX_LINE_ID(+)

       AND RA_CUSTOMER_TRX_LINES_ALL.INTERFACE_LINE_CONTEXT IN

               ('ORDER ENTRY', 'INTERCOMPANY')

       AND TO_NUMBER (RA_CUSTOMER_TRX_LINES_ALL.INTERFACE_LINE_ATTRIBUTE6) =

           OE_ORDER_LINES_ALL.LINE_ID(+)

       AND OE_ORDER_LINES_ALL.HEADER_ID = OE_ORDER_HEADERS_ALL.HEADER_ID(+)

       AND RA_CUSTOMER_TRX_ALL.LAST_UPDATE_DATE >=

           TO_DATE (:P_LAST_EXTRACT_DATE, 'MM/D D/YYYY HH24:MI:SS')

Query to get AP Invoice details in R12

SELECT AP_INVOICE_DISTRIBUTIONS_ALL.DISTRIBUTION_LINE_NUMBER

      ,AP_INVOICE_DISTRIBUTIONS_ALL.LAST_UPDATED_BY

      ,AP_INVOICE_DISTRIBUTIONS_ALL.LAST_UPDATE_DATE

  ,AP_INVOICE_DISTRIBUTIONS_ALL.DIST_CODE_COMBINATION_ID

  ,AP_INVOICE_DISTRIBUTIONS_ALL.CREATED_BY

  ,AP_INVOICE_DISTRIBUTIONS_ALL.CREATION_DATE

  ,AP_INVOICES_ALL.INVOICE_ID

  ,AP_INVOICES_ALL.INVOICE_NUM

  ,AP_INVOICES_ALL.INVOICE_CURRENCY_CODE

  ,AP_INVOICES_ALL.VENDOR_ID

  ,AP_INVOICES_ALL.VENDOR_SITE_ID

  ,AP_INVOICES_ALL.INVOICE_DATE

  ,AP_INVOICE_DISTRIBUTIONS_ALL.LINE_TYPE_LOOKUP_CODE

  ,AP_INVOICES_ALL.INVOICE_RECEIVED_DATE

  ,AP_INVOICES_ALL.CREATION_DATE

  ,AP_INVOICE_DISTRIBUTIONS_ALL.ORG_ID

  ,PO_HEADERS_ALL.SEGMENT1

  ,PO_LINES_ALL.LINE_NUM

  ,PO_HEADERS_ALL.CREATION_DATE

  ,AP_INVOICES_ALL.LAST_UPDATE_DATE

  ,PO_HEADERS_ALL.LAST_UPDATE_DATE

  ,PO_LINES_ALL.LAST_UPDATE_DATE

  ,PO_LINES_ALL.ITEM_DESCRIPTION

  ,AP_INVOICE_DISTRIBUTIONS_ALL.DESCRIPTION

  ,AP_INVOICES_ALL.INVOICE_TYPE_LOOKUP_CODE

  ,AP_INVOICES_ALL.WFAPPROVAL_STATUS

  ,AP_INVOICES_ALL.PAYMENT_STATUS_FLAG

  ,AP_INVOICES_ALL.CANCELLED_DATE

  ,AP_INVOICE_DISTRIBUTIONS_ALL.ACCOUNTING_DATE

  ,PO_DISTRIBUTIONS_ALL.PO_DISTRIBUTION_ID

  ,AP_INVOICE_DISTRIBUTIONS_ALL.RCV_TRANSACTION_ID

  ,AP_INVOICE_DISTRIBUTIONS_ALL.MATCH_STATUS_FLAG

  ,AP_INVOICE_DISTRIBUTIONS_ALL.INVOICE_DISTRIBUTION_ID

  ,AP_INVOICES_ALL.TERMS_ID

  ,AP_INVOICE_DISTRIBUTIONS_ALL.EXCHANGE_DATE

  ,AP_INVOICES_ALL.EXCHANGE_DATE

  ,AP_INVOICE_DISTRIBUTIONS_ALL.EXCHANGE_RATE_TYPE

  ,AP_INVOICES_ALL.EXCHANGE_RATE_TYPE

  ,AP_INVOICE_DISTRIBUTIONS_ALL.EXCHANGE_RATE

  ,AP_INVOICES_ALL.EXCHANGE_RATE

  ,AP_INVOICE_DISTRIBUTIONS_ALL.ACCTS_PAY_CODE_COMBINATION_ID

  ,AP_INVOICES_ALL.ACCTS_PAY_CODE_COMBINATION_ID

  ,AP_INVOICE_DISTRIBUTIONS_ALL.SET_OF_BOOKS_ID

  ,AP_INVOICES_ALL.SET_OF_BOOKS_ID

  ,AP_INVOICE_DISTRIBUTIONS_ALL.QUANTITY_INVOICED

  ,AP_INVOICE_DISTRIBUTIONS_ALL.AMOUNT

  ,AP_INVOICE_DISTRIBUTIONS_ALL.BASE_AMOUNT

  ,CASE WHEN AP_INVOICE_LINES_ALL.UNIT_MEAS_LOOKUP_CODE IS NULL 

        THEN PO_LINES_ALL.UNIT_MEAS_LOOKUP_CODE 

ELSE AP_INVOICE_LINES_ALL.UNIT_MEAS_LOOKUP_CODE 

   END

  ,AP_INVOICE_DISTRIBUTIONS_ALL.INVOICE_PRICE_VARIANCE

  ,AP_INVOICE_DISTRIBUTIONS_ALL.EXCHANGE_RATE_VARIANCE

  ,PO_VENDORS.RECEIPT_REQUIRED_FLAG

  ,PO_VENDORS.INSPECTION_REQUIRED_FLAG

  ,PO_VENDOR_SITES_ALL.PURCHASING_SITE_FLAG

  ,PO_VENDORS.LAST_UPDATE_DATE

  ,AP_INVOICES_ALL.SOURCE

  ,AP_INVOICES_ALL.PROJECT_ID

  ,AP_INVOICES_ALL.TASK_ID

  ,AP_INVOICES_ALL.EXPENDITURE_TYPE

  ,AP_INVOICES_ALL.EXPENDITURE_ORGANIZATION_ID

  ,AP_INVOICES_ALL.PAYMENT_CURRENCY_CODE

  ,AP_INVOICES_ALL.PAYMENT_CROSS_RATE

  ,AP_INVOICES_ALL.REQUESTER_ID

  ,AP_INVOICE_LINES_ALL.LINE_NUMBER

  ,AP_INVOICE_LINES_ALL.LINE_TYPE_LOOKUP_CODE

  ,AP_INVOICE_LINES_ALL.REQUESTER_ID

  ,AP_INVOICE_LINES_ALL.DESCRIPTION

  ,AP_INVOICES_ALL.LEGAL_ENTITY_ID

  FROM AP_INVOICE_DISTRIBUTIONS_ALL

      ,AP_INVOICE_LINES_ALL

  ,AP_INVOICES_ALL

  ,PO_LINES_ALL

  ,PO_HEADERS_ALL

  ,PO_VENDORS

  ,PO_VENDOR_SITES_ALL

  ,PO_DISTRIBUTIONS_ALL

  ,(SELECT AP_INVOICES_ALL.INVOICE_ID 

      FROM AP_INVOICES_ALL 

WHERE AP_INVOICES_ALL.LAST_UPDATE_DATE > TO_DATE(:P_LAST_EXTRACT_DATE,'MM/DD/Y YYY HH24:MI:SS') 

UNION 

SELECT AP_INVOICE_LINES_ALL.INVOICE_ID 

  FROM AP_INVOICE_LINES_ALL 

WHERE AP_INVOICE_LINES_ALL.LAST_UPDATE_DATE > TO_DATE(:P_LAST_EXTRACT_DATE,'MM /DD/YYYY HH24:MI:SS') 

UNION 

SELECT DISTINCT AP_HOLDS_ALL.INVOICE_ID 

  FROM AP_HOLDS_ALL 

WHERE AP_HOLDS_ALL.RELEASE_LOOKUP_CODE IS NULL 

   AND AP_HOLDS_ALL.LAST_UPDATE_DATE > TO_DATE(:P_LAST_EXTRACT_DATE,'MM/DD/YYYY HH24:MI:SS') 

UNION 

SELECT DISTINCT AP_INVOICE_DISTRIBUTIONS_ALL.INVOICE_ID 

  FROM PO_DISTRIBUTIONS_ALL, AP_INVOICE_DISTRIBUTIONS_ALL 

WHERE AP_INVOICE_DISTRIBUTIONS_ALL.PO_DISTRIBUTION_ID = PO_DISTRIBUTIONS_ALL.PO_DISTRIBUTION_ID 

   AND PO_DISTRIBUTIONS_ALL.LAST_UPDATE_DATE > TO_DATE (:P_LAST_EXTRACT_DATE,'MM/D D/YYYY HH24:MI:SS') 

UNION 

SELECT DISTINCT AP_INVOICE_DISTRIBUTIONS_ALL.INVOICE_ID 

  FROM PO_DISTRIBUTIONS_ALL

      ,AP_INVOICE_DISTRIBUTIONS_ALL

  ,PO_HEADERS_ALL 

WHERE AP_INVOICE_DISTRIBUTIONS_ALL.PO_DISTRIBUTION_ID = PO_DISTRIBUTIONS_ALL.PO_DISTRIBUTION_ID 

   AND PO_DISTRIBUTIONS_ALL.PO_HEADER_ID = PO_HEADERS_ALL.PO_HEADER_ID

UNION 

    SELECT DISTINCT AP_INVOICE_DISTRIBUTIONS_ALL.INVOICE_ID 

  FROM PO_DISTRIBUTIONS_ALL, AP_INVOICE_DISTRIBUTIONS_ALL, PO_LINES_ALL 

WHERE AP_INVOICE_DISTRIBUTIONS_ALL.PO_DISTRIBUTION_ID = PO_DISTRIBUTIONS_ALL.PO_DISTRIBUTION_ID 

   AND PO_DISTRIBUTIONS_ALL.PO_LINE_ID = PO_LINES_ALL.PO_LINE_ID 

   AND PO_LINES_ALL.LAST_UPDATE_DATE > TO_DATE (:P_LAST_EXTRACT_DATE,'MM/DD/YYYY H H24:MI:SS') 

   ) TEMP 

 WHERE AP_INVOICE_DISTRIBUTIONS_ALL.INVOICE_ID = AP_INVOICES_ALL.INVOICE_ID 

   AND AP_INVOICES_ALL.VENDOR_ID = PO_VENDORS.VENDOR_ID(+) 

   AND AP_INVOICES_ALL.VENDOR_SITE_ID = PO_VENDOR_SITES_ALL.VENDOR_SITE_ID(+) 

   AND AP_INVOICE_DISTRIBUTIONS_ALL.PO_DISTRIBUTION_ID = PO_DISTRIBUTIONS_ALL.PO_DISTRIBUTION_ID(+) 

   AND PO_DISTRIBUTIONS_ALL.PO_HEADER_ID = PO_HEADERS_ALL.PO_HEADER_ID(+) 

   AND PO_DISTRIBUTIONS_ALL.PO_HEADER_ID = PO_LINES_ALL.PO_HEADER_ID(+) 

   AND PO_DISTRIBUTIONS_ALL.PO_LINE_ID = PO_LINES_ALL.PO_LINE_ID(+) 

   AND AP_INVOICE_DISTRIBUTIONS_ALL.INVOICE_ID = TEMP.INVOICE_ID 

   AND AP_INVOICE_DISTRIBUTIONS_ALL.INVOICE_ID = AP_INVOICE_LINES_ALL.INVOICE_ID 

   AND AP_INVOICE_DISTRIBUTIONS_ALL.INVOICE_LINE_NUMBER = AP_INVOICE_LINES_ALL.LINE_NUMBER 

Query to get Oracle Workflow attribute values

SELECT name attribute_name

      ,NVL(text_value, NVL(TO_CHAR(number_value), TO_CHAR(date_value))) VALUE

  FROM wf_item_attribute_values

 WHERE item_type = 'XXITEMTP'

   AND item_key = 'XXITEMTP001'

Query to get Errored Activities in Oracle Workflow

SELECT wav.display_name            activity

      ,wias.activity_result_code activity_result

  ,wias.error_name            error_name

  ,wias.error_message         error_message

  ,wias.error_stack           error_stack

   FROM wf_item_activity_statuses wias

       ,wf_process_activities      wpa

   ,wf_activities_vl           wav

   ,wf_activities_vl           wav1

   ,wf_items wi

  WHERE wias.item_type = wi.item_type

    AND wias.item_key = wi.item_key

    AND wias.activity_status = 'ERROR'

    AND wias.process_activity = wpa.instance_id

    AND wpa.activity_name = wav.name

    AND wpa.activity_item_type = wav.item_type

    AND wpa.process_name = wav1.name

    AND wpa.process_item_type = wav1.item_type

    AND wpa.process_version = wav1.version

    AND wi.parent_item_type = 'XXITEMTP'

    AND wi.parent_item_key = 'XXITEMTP001'

    AND wi.begin_date >= wav.begin_date

    AND wi.begin_date < NVL(wav.end_date, wi.begin_date + 1)

  ORDER BY wias.execution_time

Query to get Oracle Workflow Deferred Activities

SELECT COUNT(1)

      ,wias.item_type

  FROM apps.wf_items wi

      ,apps.wf_item_activity_statuses wias

  ,apps.wf_process_activities wpa

 WHERE wi.item_type = wias.item_type

   AND wi.item_key = wias.item_key

   AND wi.end_date IS NULL

   AND wias.end_date IS NULL

   AND wias.activity_status = 'DEFERRED'

   --AND wias.item_type = 'REQAPPRV'

   AND wias.item_type = wi.item_type

   AND wpa.instance_id(+) = wias.process_activity

GROUP BY wias.item_type

Query to check the Workflow Agent Listeners and their Statuses

SELECT fsc.component_name

      ,dq.owner

      ,dq.queue_table

      ,fsc.correlation_id

  FROM applsys.fnd_svc_components fsc

      ,applsys.wf_agents          wa

  ,dba_queues dq

 WHERE fsc.inbound_agent_name || fsc.outbound_agent_name = wa.name

   AND dq.owner || '.' || dq.name = wa.queue_name

   AND fsc.component_type LIKE 'WF_%AGENT%'

Query to get List of all Errored Oracle Workflow Activities for a given Item Type/Item Key

SELECT wac.display_name          activity

      ,wias.activity_result_code activity_result

  ,wias.error_name           error_name

  ,wias.error_message        error_message

  ,wias.error_stack          error_stack

  FROM wf_item_activity_statuses wias

      ,wf_process_activities wpa

      ,wf_activities_vl wac

      ,wf_activities_vl wap

      ,wf_items wi

 WHERE wias.item_type = 'XXITEMTP'

   AND wias.item_key = 'XXITEMTP001'

   AND wias.activity_status = 'ERROR'

   AND wias.process_activity = wpa.instance_id

   AND wpa.activity_name = wac.name

   AND wpa.activity_item_type = wac.item_type

   AND wpa.process_name = wap.name

   AND wpa.process_item_type = wap.item_type

   AND wpa.process_version = wap.version

   AND wi.item_type = 'XXITEMTP'

   AND wi.item_key = wias.item_key

   AND wi.begin_date >= wac.begin_date

   AND wi.begin_date < nvl(wac.end_date, wi.begin_date + 1)

 ORDER BY wias.execution_time

SQL Query to get Activity Statuses for all Workflow Activities of a given Item Type and Item Key in Oracle APPS

SELECT *

  FROM(

SELECT execution_time

      ,TO_CHAR(wias.begin_date, 'DD-MON-RR HH24:MI:SS') begin_date

  ,wap.display_name || '/' || wac.display_name activity

  ,wias.activity_status status

  ,wias.activity_result_code activity_result

  ,wias.assigned_user ass_user

  FROM wf_item_activity_statuses wias

      ,wf_process_activities wpa

      ,wf_activities_vl wac

      ,wf_activities_vl wap

      ,wf_items         wi

 WHERE wias.item_type = 'XXITEMTP'

   AND wias.item_key = 'XXITEMTP001'

   AND wias.process_activity = wpa.instance_id

   AND wpa.activity_name = wac.name

   AND wpa.activity_item_type = wac.item_type

   AND wpa.process_name = wap.name

   AND wpa.process_item_type = wap.item_type

   AND wpa.process_version = wap.version

   AND wi.item_type = 'XXITEMTP'

   AND wi.item_key = wias.item_key

   AND wi.begin_date >= wac.begin_date

   AND wi.begin_date < nvl(wac.end_date, wi.begin_date + 1)

UNION ALL

SELECT execution_time

      ,TO_CHAR(wias.begin_date, 'DD-MON-RR HH24:MI:SS') begin_date

  ,wap.display_name || '/' || wac.display_name activity

  ,wias.activity_status status

  ,wias.activity_result_code activity_result

  ,wias.assigned_user ass_user

  FROM wf_item_activity_statuses_h wias

      ,wf_process_activities wpa

      ,wf_activities_vl wac

      ,wf_activities_vl wap

      ,wf_items wi

  WHERE wias.item_type = 'XXITEMTP'

    AND wias.item_key = 'XXITEMTP001'

    AND wias.process_activity = wpa.instance_id

    AND wpa.activity_name = wac.name

    AND wpa.activity_item_type = wac.item_type

    AND wpa.process_name = wap.name

    AND wpa.process_item_type = wap.item_type

    AND wpa.process_version = wap.version

    AND wi.item_type = 'XXITEMTP'

    AND wi.item_key = wias.item_key

    AND wi.begin_date >= wac.begin_date

    AND wi.begin_date < nvl(wac.end_date, wi.begin_date + 1)

)

  ORDER BY begin_date

          ,execution_time

Query to get Notifications sent by a given Oracle Workflow

SELECT wn.notification_id nid

      ,wn.context

      ,wn.group_id

      ,wn.status

      ,wn.mail_status

      ,wn.message_type

      ,wn.message_name

      ,wn.access_key

      ,wn.priority

      ,wn.begin_date

      ,wn.end_date

      ,wn.due_date

      ,wn.callback

      ,wn.recipient_role

      ,wn.responder

      ,wn.original_recipient

      ,wn.from_user

      ,wn.to_user

      ,wn.subject

  FROM wf_notifications wn

      ,wf_item_activity_statuses wias

 WHERE wn.group_id = wias.notification_id

   AND wias.item_type = 'XXITEMTP'

   AND wias.item_key = 'XXITEMTP001'

Query to Select all Oracle Workflow Items for a given Item Type

SELECT item_type

      ,item_key

  ,TO_CHAR(begin_date, 'DD-MON-RR HH24:MI:SS') begin_date

  ,TO_CHAR(end_date, 'DD-MON-RR HH24:MI:SS') end_date

  ,root_activity activity

  FROM apps.wf_items

 WHERE item_type = 'XXITEMTP'

   AND end_date IS NULL

 ORDER BY to_date(begin_date, 'DD-MON-YYYY hh24:mi:ss')

 ASC

How to add LOV to the Manual Adjustment reasons in Comp off Plan Type ?(Doc ID 2483891.1)

 Use ORA_ANC_COMP_ADJRSN from Manage Common Lookup 

Query to get Employee Absence details in Oracle APPS

SELECT /*+ rule*/

       paaf.person_id

  ,paaf.assignment_id

  ,flv.meaning absence_category

  ,c.name absence_type

  ,flv1.meaning absence_reason

  ,abs.date_notification

  ,abs.date_projected_start

  ,abs.date_projected_end

  ,abs.date_start

  ,abs.date_end

  ,abs.absence_days

  ,abs.attribute1

  ,abs.attribute2

  ,abs.attribute3

  ,abs.attribute4

  ,abs.attribute5

  ,abs.attribute6

  ,abs.attribute7

  ,abs.attribute8

  ,abs.attribute9

  ,abs.attribute10

  ,abs.attribute11

  ,abs.attribute12

  ,abs.attribute13

  ,abs.attribute14

  ,abs.attribute15

  ,abs.attribute16

  ,abs.attribute17

  ,abs.attribute18

  ,abs.attribute19

  ,abs.attribute20

  ,b.admission_code

  ,b.admission_date

  ,b.amendment_date

      ,b.amendment_reason

  ,b.concatenated_segments

  ,b.contact_grade

      ,b.contact_type

  ,b.discharge_date

  ,b.disease_name

      ,b.hospital_name

  ,b.leave_amended

  ,b.leave_salary_paid

      ,b.physician_approved_accident

  ,b.physician_name

      ,b.resumption_date

  ,b.context

  FROM per_absence_attendances abs

      ,per_absence_attendance_types c

      ,per_abs_attendance_reasons d

      ,per_absence_attendances_dfv b

      ,fnd_lookup_values flv1

      ,fnd_lookup_values flv

      ,per_all_assignments_f paaf          

 WHERE abs.person_id = paaf.person_id

   AND abs.ROWID = b.row_id

   AND abs.absence_attendance_type_id = c.absence_attendance_type_id(+)

   AND abs.abs_attendance_reason_id = d.abs_attendance_reason_id(+)

   AND d.name = flv1.lookup_code(+)

   AND flv1.lookup_type(+) = 'ABSENCE_REASON'

   AND flv.lookup_code(+) = c.absence_category

   AND flv.lookup_type(+) = 'ABSENCE_CATEGORY'

ORDER BY abs.person_id

        ,absence_type

,date_start 

DESC