Basic SQLs – EBS DB Custom Object Backup

 

Business Need:

Backup DB object source codes (Tables, Functions, Packages, Triggers, etc.) from a development instance to local machine. Can be used before cloning to avoid loss of development work.

Note: This will back up only database objects. Any Unix (or other OS) files are not backed up by this, including:

• Ø Java Packages, sources
• Ø Shell Scripts
• Ø JSP Files, other middle tier files

They have to be backed up manually.

 

Tables:

All custom tables

 

SQLs:

Get Cloned date of current development instance:

 

-- get cloned date
SELECT resetlogs_time FROM v$database;
 
06/26/2016 9:44:39 PM

 

Get a list of all custom objects, created or changed after cloning date (substitute date from above SQL). Replace custom schema names:

-- all recent custom objects - change date
 
select *
from dba_objects
where LAST_DDL_TIME > TO_DATE('06/26/2016 9:44:39 PM', 'MM/DD/YYYY HH:MI:SS AM')
-- created > TO_DATE('06/26/2016 9:44:39 PM', 'MM/DD/YYYY HH:MI:SS AM')
and ((object_name like 'O%') OR (object_name like 'XXAAA%') OR (object_name like 'XXBBB%') OR (object_name like 'XXCCC%'))
and owner in ('APPS', 'XXAAA', 'XXBBB', 'XXCCC')
order by owner, object_type, object_name;
 
 
-- get source codes of all APPS-Owned objects
-- to remove Line Feed, replace \r\n"\r\n with "\r\n
select src.owner, src.name, src.type, src.line, src.text
from dba_source src
where owner in ('APPS', 'XXAAA', 'XXBBB', 'XXCCC')
and ((src.name like 'OXX%') OR (src.name like 'UHXX%') OR (src.name like 'KESX%') OR (src.name like 'SSXX%'))
and exists (select *
    from dba_objects obj
    where LAST_DDL_TIME > TO_DATE('7/29/2020 5:54:47 PM', 'MM/DD/YYYY HH:MI:SS AM')
    and ((object_name like 'O%') OR (object_name like 'XXAAA%') OR (object_name like 'XXCCC%') OR (object_name like 'XXBBB%'))
    and owner in ('APPS', 'XXAAA', 'XXBBB', 'XXCCC')
    and src.name = obj.object_name
    and src.owner = obj.owner)
order by src.owner, src.type, src.name, src.line asc;
 
 

 

Keywords:

Oracle EBS, R12, R12.2.8, Oracle Applications, Query, SQL, SQLPLUS

Oracle Internet Expenses (iExpenses): Tables and SQLs

 Introduction:

Some example SQLs (queries) related to EBS Module iExpenses (OIE – Oracle Internet Expenses. This is a part of AP (Account Payables) functionality within the Oracle Applications.

Notes: 

1. I am using EBS 12.2.3 version. But these SQLs will work with any EBS versions (At least from R12)

2. Oracle iExpenses can be used from an IOS or Android APP (Oracle Fusion Expenses) provided by Oracle. The data collected from the APP is also stored within the same tables.

 

SQLs:

Major Tables:

AP_EXPENSE_REPORT_HEADERS_ALL - iExpense header
AP_EXPENSE_REPORT_LINES_ALL - iExpense lines
 

Useful Base Query:

-- base query - major expense header and line items
-- reference_1 -  EBS_MOBILE is from APP (Oracle Fusion Expenses)
SELECT EXH.REPORT_HEADER_ID, EXH.CREATION_DATE, EXH.TOTAL HEADER_TOTAL, EXH.REFERENCE_1, EXH.SOURCE, EXH.EXPENSE_STATUS_CODE,
    EXL.ITEM_DESCRIPTION, EXL.AMOUNT AS LINE_AMOUNT, EXL.ATTRIBUTE_CATEGORY, EXL.ORG_ID, EXL.LOCATION --,  exh.*,  exl.*
FROM AP_EXPENSE_REPORT_HEADERS_ALL EXH, AP_EXPENSE_REPORT_LINES_ALL EXL
WHERE EXH.CREATION_DATE > SYSDATE-3.1
AND  EXH.REFERENCE_1 = 'EBS_MOBILE'
AND EXH.REPORT_HEADER_ID = EXL.REPORT_HEADER_ID;
 
 

 

Major Transaction Tables:

 

AP_EXPENSE_REPORT_HEADERS_ALL Expense report header information
AP_EXPENSE_REPORT_LINES_ALL   Expense report lines information
AP_EXP_REPORT_DISTS_ALL Expense report distribution information. It contains the accounts against each expense report line.
AP_CREDIT_CARD_TRXNS_ALL      Table to store the corporate credit card transactions that are sent by the banks. These lines are saved as expense lines when the user creates the expense lines for credit cards
AP_NOTES    Table to store the comments entered by approvers and auditors
 

 

iExpense Setup Tables:

AP_EXPENSE_REPORTS_ALL  This table contains the header level information about the expense templates
AP_EXPENSE_REPORT_PARAMS_ALL  This table contains the detail level information about the expense templates
AP_POL_CAT_OPTIONS_ALL  Table to store the policy options
AP_POL_CONTEXT    Table to store the policy context
 

 

iExpense Data tables  

AP_POL_LOCATIONS_TL     Table to store the locations for which policies have been defined.
AP_POL_VIOLATIONS_ALL   Table to store the lines for which the defined policies have been violated
AP_POL_ITEMIZATIONS    
AP_POL_SCHEDULE_PERIODS
AP_POL_EXRATE_OPTIONS_ALL     Table to store the exchange rate tolerance
AP_POL_HEADERS    Table to store all the policy headers
AP_POL_LINES      Table to store all the policy details
AP_CARDS_ALL      Table to store the corporate credit card details for the employees
AP_EXPENSE_REPORTS_ALL  Table to store the expense report templates that can be used by employees from different operating units
AP_WEB_DISC_HEADERS_GT 
AP_POL_SCHEDULE_OPTIONS Table to store the basis of the policy created, E.g. location, currency, etc.
AP_EXPENSE_REPORT_PARAMS_ALL  Table to store the expense template detailed information
 
 

iExpense Audit tables  

AP_AUD_AUDITORS   Table to store auditor id and security_profile_id
AP_AUD_AUDIT_REASONS    Table containing the expense report header id and audit reason id and code
AP_AUD_AUTO_AUDITS      Table to store the employees who are auditors. This table is updated through the seeded package,AP_WEB_AUDIT_PROCESS.add_to_audit_list
AP_AUD_QUEUES    
AP_AUD_RULE_ASSIGNMENTS_ALL   Table containing audit rule assignments
AP_AUD_RULE_SETS  Table containing audit rules
 

 

Keywords:

AP_EXPENSE_REPORT_HEADERS_ALL, AP_EXPENSE_REPORT_LINES_ALL, iExpenses, OIE, Oracle Internet Expenses, EBS, R12, Query, SQL, SQLPLUS