Remove/delete hr_api_transaction table data and its related table data using api

DECLARE
  P_TRANSACTION_ID NUMBER;
  P_VALIDATE BOOLEAN;
BEGIN
  P_TRANSACTION_ID := 434115;
  P_VALIDATE := FALSE;
  APPS.HR_TRANSACTION_API.ROLLBACK_TRANSACTION ( P_TRANSACTION_ID, P_VALIDATE );
  COMMIT;
END;

Oracle Reports Builder RDF Date Format

While developing RDF report we need to provide the Date Format in Parameter. We will get valueset from FND_STANDARD_DATE. To refer the parameter across the RDF we can put Input Mask and keep the Parameter in Date format.

RRRR/MM/DD HH24:MI:SS

Oracle apps EBS AP Invoice Approval History Query

select * from AP_INV_APRVL_HIST_ALL where invoice_id = 559817;

Query to get All Custom Objects in Oracle Apps(E-business Suite)

-- Form, OAF Pages
select decode(fff.type,'FORM', ff.user_form_name, fff.function_name) name,
       'Application' objects, decode(fff.type,'JSP',  'OAF Page',initcap(fff.type)) type,  
       decode(fff.type,'FORM', ff.form_name||'.fmb',web_html_call) exename ,  
       nvl(fa.application_short_name,'Custom') module  
from   fnd_form_functions fff,fnd_form_vl ff,fnd_application fa  
where  type in ('JSP','FORM')  
and    (     upper(fff.function_name) like 'XX%'
          or upper(fff.function_name) like nvl2(:CustObj1,'%'||:CustObj1||'%',null)
          or upper(fff.function_name) like nvl2(:CustObj2,'%'||:CustObj2||'%',null)
          or upper(fff.function_name) like nvl2(:CustObj3,'%'||:CustObj3||'%',null))
and    ff.form_id(+)        = fff.form_id 
and    fa.application_id(+) = fff.application_id  
union 

-- Concurrent Programs
select fct.user_concurrent_program_name,
       'Concurrent Prog', flv.meaning,
       fe.execution_file_name,
       fa.application_short_name
from   fnd_executables fe,fnd_concurrent_programs fcp,fnd_application fa, 
       fnd_concurrent_programs_tl fct, fnd_lookup_values flv 
where  1 = 1 
and    fe.executable_id          = fcp.executable_id 
and    fa.application_id         = fe.application_id 
and    fcp.concurrent_program_id = fct.concurrent_program_id  
and    fa.application_id         = fct.application_id 
and    fe.execution_method_code  = flv.lookup_code 
and    upper(flv.lookup_type)           = upper('Cp_execution_method_code')
and    fct.language              = 'US' 
and    fcp.enabled_flag          = 'Y'
and    flv.enabled_flag          = 'Y'  
and    flv.language              = 'US'
and    (     upper(fe.execution_file_name) like 'XX%'
              or upper(fe.execution_file_name) like nvl2(:CustObj1,'%'||:CustObj1||'%',null)
              or upper(fe.execution_file_name) like nvl2(:CustObj2,'%'||:CustObj2||'%',null)
              or upper(fe.execution_file_name) like nvl2(:CustObj3,'%'||:CustObj3||'%',null)
              or upper(fct.user_concurrent_program_name) LIKE  'XX%'
              or upper(fct.user_concurrent_program_name) LIKE  nvl2 (:CustObj1, '%' || :CustObj1 || '%', NULL)
              or upper(fct.user_concurrent_program_name) LIKE  nvl2 (:CustObj2, '%' || :CustObj2 || '%', NULL)
              or upper(fct.user_concurrent_program_name) LIKE  nvl2 (:CustObj3, '%' || :CustObj3 || '%', NULL)
         )

union 
-- Personalizations - Form  
select ffv.user_form_name,
       'Application', 'Personalization - Form',
       ffv.form_name,
       fa.application_short_name
from   fnd_form_vl ffv, fnd_form_custom_rules ffcr, fnd_application fa
where  1 = 1  
and    ffv.form_name     = ffcr.form_name 
and    fa.application_id = ffv.application_id
and    ffv.CREATED_BY not in (1, 2)
union
-- Personalization - OAF
select jdr_mds_internal.getdocumentname(jp.path_docid),
       'Application', 'Personalization - OAF',
       '',
       decode( upper(substr(jdr_mds_internal.getdocumentname(jp.path_docid),2,2)),'XX', 
                    substr(jdr_mds_internal.getdocumentname(jp.path_docid), 
                           regexp_instr(jdr_mds_internal.getdocumentname(jp.path_docid),'/',1,4)+1,
                           regexp_instr(jdr_mds_internal.getdocumentname(jp.path_docid),'/',1,5)-
                           regexp_instr(jdr_mds_internal.getdocumentname(jp.path_docid),'/',1,4)-1),
                    substr(jdr_mds_internal.getdocumentname(jp.path_docid), 
                           regexp_instr(jdr_mds_internal.getdocumentname(jp.path_docid),'/',1,3)+1,
                           regexp_instr(jdr_mds_internal.getdocumentname(jp.path_docid),'/',1,4)- 
                           regexp_instr(jdr_mds_internal.getdocumentname(jp.path_docid),'/',1,3)-1) 
             ) module  
from   apps.jdr_paths jp 
where  jp.path_docid in (select distinct comp_docid 
                         from   jdr_components 
                         where  comp_seq     = 0 
                         and    comp_element = 'customization' 
                         and    comp_id      is null)
                         
union 
-- Value Sets 
select ffvs.flex_value_set_name,
       'Application', 'Value Set',
       '',
       fa.application_short_name 
from   fnd_flex_value_sets ffvs,fnd_flex_validation_tables ffvt,fnd_descr_flex_column_usages fdfc,
       fnd_application fa
where  1=1 
and    ffvs.flex_value_set_id = ffvt.flex_value_set_id 
and    ffvs.flex_value_set_id = fdfc.flex_value_set_id 
and    fdfc.application_id    = fa.application_id 
and    fdfc.enabled_flag      = 'Y'  
and    (     upper(ffvs.flex_value_set_name) like 'XX%'
          or upper(ffvs.flex_value_set_name) like nvl2(:CustObj1,'%'||:CustObj1||'%',null)
          or upper(ffvs.flex_value_set_name) like nvl2(:CustObj2,'%'||:CustObj2||'%',null)
          or upper(ffvs.flex_value_set_name) like nvl2(:CustObj3,'%'||:CustObj3||'%',null))

union  
-- alerts 
select alr.alert_name,
       'Application', 'Alert',
       '',
       fa.application_short_name
from   alr_alerts alr, fnd_application fa
where  (    upper (alert_name) like 'XX%'
        or  upper (alert_name) like nvl2(:CustObj1,'%'||:CustObj1||'%',null)
        or  upper (alert_name) like nvl2(:CustObj2,'%'||:CustObj2||'%',null)
        or  upper (alert_name) like nvl2(:CustObj3,'%'||:CustObj3||'%',null))
and    alr.enabled_flag = 'Y'
and    sysdate between alr.start_date_active and nvl (alr.end_date_active, sysdate)
and    fa.application_id = alr.application_id 

union
-- responsibilities 
select frt.responsibility_name,
       'Application', 'Responsibility',
       '',
       fa.application_short_name 
from   fnd_responsibility_tl frt,
       fnd_application fa
where  frt.application_id = fa.application_id
and    (     upper(frt.responsibility_name) like 'XX%'
          or upper(frt.responsibility_name) like nvl2(:CustObj1,'%'||:CustObj1||'%',null)
          or upper(frt.responsibility_name) like nvl2(:CustObj2,'%'||:CustObj2||'%',null)
          or upper(frt.responsibility_name) like nvl2(:CustObj3,'%'||:CustObj3||'%',null))
and    frt.language = 'US'

union
-- menus 
select menu_name, 
       'Application', 'Menu',
       user_menu_name,
       ''
from   fnd_menus fm, fnd_menus_tl fmt
where  fmt.menu_id = fm.menu_id
and    (     upper(fm.menu_name) like 'XX%'
          or upper(fm.menu_name) like nvl2(:CustObj1,'%'||:CustObj1||'%',null)
          or upper(fm.menu_name) like nvl2(:CustObj2,'%'||:CustObj2||'%',null)
          or upper(fm.menu_name) like nvl2(:CustObj3,'%'||:CustObj3||'%',null))
and    language = 'US'
union
-- request groups
select frg.request_group_name,
       'Application', 'Request Group',
       '',
       fa.application_short_name
from   fnd_request_groups frg, fnd_application fa
where  (     upper(frg.request_group_name) like 'XX%'
          or upper(frg.request_group_name) like nvl2(:CustObj1,'%'||:CustObj1||'%',null)
          or upper(frg.request_group_name) like nvl2(:CustObj2,'%'||:CustObj2||'%',null)
          or upper(frg.request_group_name) like nvl2(:CustObj3,'%'||:CustObj3||'%',null))
and    fa.application_id = frg.application_id
union
-- Profiles 
select pro1.user_profile_option_name,
       'Application', 'Profile',
       pro1.profile_option_name  || '  -  ' || decode (pov.level_id,
                                               10001, 'Site',
                                               10002, 'Application',
                                               10003, 'Resp',
                                               10004, 'User'),
       appl.application_short_name
from   fnd_profile_option_values pov,
       fnd_responsibility_tl resp,
       fnd_application appl,
       fnd_user u,
       fnd_profile_options pro,
       fnd_profile_options_tl pro1
where  pro.profile_option_name = pro1.profile_option_name
and    pro.profile_option_id = pov.profile_option_id
and    pov.level_value = resp.responsibility_id(+)
and    pov.level_value = appl.application_id(+)
and    pov.level_value = u.user_id(+)
and    (     upper(pro1.profile_option_name) like 'XX%'
          or upper(pro1.profile_option_name) like nvl2(:CustObj1,'%'||:CustObj1||'%',null)
          or upper(pro1.profile_option_name) like nvl2(:CustObj2,'%'||:CustObj2||'%',null)
          or upper(pro1.profile_option_name) like nvl2(:CustObj3,'%'||:CustObj3||'%',null))
and    sysdate between pro.start_date_active and nvl (pro.end_date_active, sysdate)
union
-- messages
select message_name, 
       'Application', 'Messages',
       '',
       application_short_name
from   fnd_new_messages fnm, fnd_application fa
where  (     upper(fnm.message_name) like 'XX%'
          or upper(fnm.message_name) like nvl2(:CustObj1,'%'||:CustObj1||'%',null)
          or upper(fnm.message_name) like nvl2(:CustObj2,'%'||:CustObj2||'%',null)
          or upper(fnm.message_name) like nvl2(:CustObj3,'%'||:CustObj3||'%',null))
and    fa.application_id = fnm.application_id
union
-- Lookups
select lookup_type,
       'Application', 'Lookup',
       '',
       application_short_name
from   fnd_lookup_types l,  fnd_application fa
where  fa.application_id = l.application_id
and    (     upper(lookup_type) like 'XX%'
          or upper(lookup_type) like nvl2(:CustObj1,'%'||:CustObj1||'%',null)
          or upper(lookup_type) like nvl2(:CustObj2,'%'||:CustObj2||'%',null)
          or upper(lookup_type) like nvl2(:CustObj3,'%'||:CustObj3||'%',null))
union
-- Custom Applications
select application_short_name,
       'Application', 'Custom Applications',
       basepath,
       application_short_name
from   fnd_application
where  (     upper(application_short_name) like 'XX%'
          or upper(application_short_name) like nvl2(:CustObj1,'%'||:CustObj1||'%',null)
          or upper(application_short_name) like nvl2(:CustObj2,'%'||:CustObj2||'%',null)
          or upper(application_short_name) like nvl2(:CustObj3,'%'||:CustObj3||'%',null))
union
-- xml report templates 
select xddb.data_source_code,
       'Application', 'XML Publisher Templates',
       '',
       xddb.application_short_name
from   xdo_ds_definitions_b xddb,xdo_templates_b xtb
where  1 = 1 
and    xddb.data_source_code = xtb.data_source_code
and    (     upper(xddb.data_source_code) like 'XX%'
          or upper(xddb.data_source_code) like nvl2(:CustObj1,'%'||:CustObj1||'%',null)
          or upper(xddb.data_source_code) like nvl2(:CustObj2,'%'||:CustObj2||'%',null)
          or upper(xddb.data_source_code) like nvl2(:CustObj3,'%'||:CustObj3||'%',null))
union
-- DB Objects
select object_name,
       'DB Objects', initcap(object_type),
       status,
       owner
from   dba_objects
where  1 = 1
-- and    status = 'VALID'
and    (     upper(object_name) like 'XX%'
          or upper(object_name) like nvl2(:CustObj1,'%'||:CustObj1||'%',null)
          or upper(object_name) like nvl2(:CustObj2,'%'||:CustObj2||'%',null)
          or upper(object_name) like nvl2(:CustObj3,'%'||:CustObj3||'%',null))

union  
-- workflows
select name,
       'Workflow', 'Workflow',
       '',
       'Custom'
from   wf_item_types_tl 
where  1 = 1 
and    language = 'US'
and    (     upper(name) like 'XX%'
          or upper(name) like nvl2(:CustObj1,'%'||:CustObj1||'%',null)
          or upper(name) like nvl2(:CustObj2,'%'||:CustObj2||'%',null)
          or upper(name) like nvl2(:CustObj3,'%'||:CustObj3||'%',null))

union 
-- Legal Entity
select distinct xep.name, 'Setup', 'Legal Entity', '' ,'GL'
from   xle_entity_profiles xep, org_organization_definitions ood
where  ood.legal_entity = xep.legal_entity_id

union
-- Business Group
select distinct name, 'Setup', 'Business Group', '', 'GL'
from   hr_all_organization_units_tl haoutbg ,org_organization_definitions ood
where  haoutbg.organization_id = ood.business_group_id
and    language = 'US'

union
-- Ledger
select distinct gl.name, 'Setup' , 'Ledger', '', 'GL'
from   gl_ledgers gl, org_organization_definitions ood
where  ood.set_of_books_id = gl.ledger_id

union
-- Operating Unit
select distinct hou.name, 'Setup', 'Operating Unit', '', 'INV'
from   hr_operating_units hou, org_organization_definitions ood
where  ood.operating_unit = hou.organization_id

union
-- Inventory Organization
select ORGANIZATION_NAME, 'Setup', 'Inventory Org', '', 'INV'
from  org_organization_definitions ood, mtl_parameters mp
where ood.organization_id=mp.organization_id
order by 2,3 ;

Reconciled Payments and Invoices

Below is the Query for Invoices and Payments

SELECT
    aia.invoice_num,
    aia.invoice_amount,
    aia.amount_paid total_amount_paid,
    aia.invoice_currency_code,
    asup.vendor_name supplier,
    apsa.vendor_site_code supplier_site,
    apsa.address_line1|| ' '|| apsa.address_line2|| ' '|| apsa.address_line3|| ' '|| apsa.city|| ' '|| apsa.state|| ' '|| apsa.zip supplier_address,
    ac.bank_account_num eaa_bank_account,
    iebav.bank_name,
    iebav.bank_branch_name,
    iebav.branch_number,
    iebav.bank_account_number,
    iebav.iban_number,
    ac.CHECK_NUMBER payment_num,
    ac.status_lookup_code payment_status,
    ac.amount payment_amount,
    ac.currency_code payment_currency,
    ac.check_date payment_date,
    ac.cleared_date payment_clearning_date,
    (SELECT DISTINCT ffvv.flex_value|| '-'|| ffvv.description
        FROM ap_invoice_distributions_all aida,
            gl_code_combinations_kfv gcc,
            fnd_flex_values_vl ffvv
        WHERE gcc.code_combination_id = aida.dist_code_combination_id AND   distribution_line_number = 1
            AND   aida.invoice_line_number = 1 AND   aia.invoice_id = aida.invoice_id AND   ffvv.flex_value = gcc.segment1
            AND   ffvv.flex_value_set_id = 1015406
    ) directorate,
    (
        SELECT DISTINCT gcc.concatenated_segments
        FROM
            ap_invoice_distributions_all aida,
            gl_code_combinations_kfv gcc
        WHERE gcc.code_combination_id = aida.dist_code_combination_id AND   distribution_line_number = 1
            AND   aida.invoice_line_number = 1 AND   aia.invoice_id = aida.invoice_id
    ) account
FROM
    apps.ap_invoices_all aia,
    apps.ap_suppliers asup,
    apps.ap_supplier_sites_all apsa,
    apps.ap_payment_schedules_all d,
    apps.ap_invoice_payments_all ap,
    ap_checks_all ac,
    iby_external_bank_accounts_v iebav
WHERE
    aia.vendor_id = asup.vendor_id
    AND   aia.vendor_site_id = apsa.vendor_site_id
    AND   asup.vendor_id = apsa.vendor_id
    AND   aia.invoice_id = d.invoice_id
    AND   ap.invoice_id = aia.invoice_id
    AND   ac.check_id = ap.check_id
    and aia.org_id = 101
    and aia.org_id = ap.org_id
    and aia.org_id = ac.org_id
        and aia.org_id = apsa.org_id
    AND   ac.external_bank_account_id = iebav.ext_bank_account_id
    AND   ac.check_date >= '01-JAN-2022'
    and  ac.status_lookup_code in ('RECONCILED', 'RECONCILED UNACCOUNTED')

Annual Leave or Other Leave Accrual Query in Oracle Apps

SELECT DISTINCT
    pf.employee_number,
    pf.full_name,
    pf.business_group_id,
    TO_CHAR(per_utility_functions.get_net_accrual(paf.assignment_id,paf.payroll_id,pf.business_group_id,NULL,TO_DATE('01-JAN-2022','DD-MON-YYYY'
),61, -- Accrual Plan ID
    NULL,NULL) ) annual_leave_plan,
    TO_CHAR(per_utility_functions.get_net_accrual(paf.assignment_id,paf.payroll_id,pf.business_group_id,NULL,TO_DATE('01-JAN-2022','DD-MON-YYYY'
),1061, -- Accrual Plan ID
    NULL,NULL) ) residual_leave_plan
FROM
    pay_view_accrual_plans_v pv,
    per_all_people_f pf,
    per_all_assignments_f paf
WHERE
    pv.person_id = pf.person_id
    AND   pf.person_id = paf.person_id
    AND   pv.accrual_plan_id IN (61,1061)
    AND   trunc(SYSDATE) BETWEEN pf.effective_start_date AND pf.effective_end_date
    AND   trunc(SYSDATE) BETWEEN paf.effective_start_date AND paf.effective_end_date

GL Dump, GL Query, GL Detail Query in Oracle apps

Query to get GL Dump or GL related detail information.

SELECT   glh.doc_sequence_value Header_voucher_number,
         gll.JE_LINE_NUM,
         glh.LAST_UPDATED_BY,
         glh.LAST_UPDATED_BY last_updated_by_user,
         gjb.name batch_name,
         glc.segment1|| '-'|| apps.gl_flexfields_pkg.get_description_sql (glc.chart_of_accounts_id,1,glc.segment1) Company_name,
         gl.NAME ledger_name,
         glh.created_by,
         glh.NAME journal_name,
         gll.description Journal_Header_Description,
         glh.je_source JE_Source,
         gljc.user_je_category_name JE_Category,
         glc.segment4 account_,
         glc.concatenated_segments gl_code,
         glh.currency_code,
         glh.CURRENCY_CONVERSION_TYPE conversion_type,
         glh.CURRENCY_CONVERSION_RATE conversion_rate,
         glh.creation_date,
         glh.PERIOD_NAME,
         TO_CHAR (glh.default_effective_date, 'DD-MON-YYYY') Accounting_Date,
         glh.POSTED_DATE,
         gll.DESCRIPTION line_DESCRIPTION,
         gll.entered_dr,
         gll.entered_cr,
         gll.ACCOUNTED_DR,
         gll.ACCOUNTED_CR
    FROM apps.gl_je_headers           glh,
         apps.gl_ledgers              gl,
         apps.gl_je_batches           gjb,
         apps.gl_je_lines             gll,
         apps.gl_je_categories_tl     gljc,
         apps.gl_code_combinations_kfv glc
   WHERE     glh.je_header_id = gll.je_header_id
         AND gll.code_combination_id = glc.code_combination_id
         AND glh.ledger_id = gl.ledger_id
         and glh.je_batch_id = gjb.JE_BATCH_ID
         AND glh.default_effective_date BETWEEN :lv_from_date AND :lv_to_date
         AND glh.je_category = gljc.je_category_name
ORDER BY glh.doc_sequence_value,
         glh.default_effective_date,
         gll.entered_dr,
         gll.entered_cr;

Query to find all responsibilities assigned to a user in Oracle apps

SELECT
    fu.user_name "User Name",
    frt.responsibility_name "Responsibility Name",
    furg.start_date "Start Date",
    furg.end_date "End Date",
    fr.responsibility_key "Responsibility Key",
    fa.application_short_name "Application Short Name"
FROM
    fnd_user_resp_groups_direct furg,
    applsys.fnd_user fu,
    applsys.fnd_responsibility_tl frt,
    applsys.fnd_responsibility fr,
    applsys.fnd_application_tl fat,
    applsys.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('SYSADMIN')  -- <change it>
   -- AND (furg.end_date IS NULL OR furg.end_date >= TRUNC(SYSDATE))
ORDER BY
    frt.responsibility_name;

Query to get Ex-Employees/Terminated/Deactivated Employees in Oracle Apps

SELECT DISTINCT
    papf.employee_number "Employee Number",
    papf.title "Title",
    papf.first_name "First Name",
    papf.last_name "Last Name",
--papf.national_identifier,
    papf.email_address "E-mail",
--TO_CHAR(papf.effective_start_date, 'DD-MON-RRRR') "Start Date",
--TO_CHAR(papf.effective_end_date, 'DD-MON-RRRR') "End Date",
    TO_CHAR(papf.original_date_of_hire,'DD-MON-RRRR') "Hire Date",
    TO_CHAR(actual_termination_date) "ACTUAL_TERMINATION_DATE",
    fu.user_name,
    fu.start_date,
    fu.end_date
FROM
    apps.per_all_people_f papf,
    apps.per_periods_of_service ppos,
    fnd_user fu
WHERE
    1 = 1
    AND   fu.employee_id = papf.person_id
    AND   papf.person_id = ppos.person_id
    AND   ppos.period_of_service_id = (
        SELECT
            MAX(period_of_service_id)
        FROM
            per_periods_of_service
        WHERE
            person_id = papf.person_id
    )
    AND   ppos.actual_termination_date < trunc(SYSDATE);
 --   AND   ppos.actual_termination_date > trunc(SYSDATE - 340);

Query to get HRMS Active Positions in Oracle Apps EBz.

select HR_GENERAL.DECODE_AVAILABILITY_STATUS(pap.AVAILABILITY_STATUS_ID) AVAILABILITY_STATUS_DESC , pap.* from hr_all_positions_f pap where HR_GENERAL.DECODE_AVAILABILITY_STATUS(pap.AVAILABILITY_STATUS_ID) = 'Active'