Basic SQLs – RegEx Regular Expression – SQL Split String

 

Business Need:

Oracle Database SQL, SQL PLUS

Split a sting into multiple parts – name, EDI Inputs, Comma Separated Values, Character delimiters, etc.

 

Tables:

Any …

 

SQLs:

Split based on “_”:

SELECT TRIM(REGEXP_SUBSTR('4811_ONT_127044_5798526', '[^_]+', 1, 1)) FROM DUAL;        -- 4811             - Order Number / Receipt Number
SELECT TRIM(REGEXP_SUBSTR('4811_ONT_127044_5798526', '[^_]+', 1, 2)) FROM DUAL;        -- ONT / AR - application name
SELECT TRIM(REGEXP_SUBSTR('4811_ONT_127044_5798526', '[^_]+', 1, 3)) FROM DUAL;        -- 127044    - Payment Order Number
SELECT TRIM(REGEXP_SUBSTR('4811_ONT_127044_5798526', '[^_]+', 1, 4)) FROM DUAL;        -- 5798526   - Order Header ID
 

“_” can be replaced with COMMA, SPACE, HYPEN, TAB or any other character to split based on the given character.

  

Eg: Consider a name string with “<First> <Middle><Last>” format. Here is the query to get First, Middle and Last names:

select regexp_substr(name, '[^,]+', 1, 1) as lastname,
       regexp_substr(name, '[^ ,]+', 1, 2) as firstname,
       regexp_substr(name, '[^ ,]+', 1, 3) as middle 
 

 

Keywords:

Oracle EBS, R12, R12.2.8, AR, Accounts Receivables, Financials, Oracle Applications, Query, SQL, SQLPLUS, regexp_substr, RegEx

Basic SQLs – Currently Open EBS Forms

 Business Need:

Find who is logged into EBS Forms and have open (non-navigator) forms. Must be run and inform users before bouncing middle tier. Or else they will be kicked out with unsaved data lost.

 

Query:

SELECT USR.USER_NAME, S.SID, S.SERIAL# "SER#", L.PROCESS_SPID "OS PID", S.PROCESS,
   S.OSUSER, RSP.RESPONSIBILITY_NAME RESP_NAME, FRM.USER_FORM_NAME, TO_CHAR(NVL(F.START_TIME, NVL(R.START_TIME, L.START_TIME)), 'MonDD hh24:mi') AS START_TIME,
   VA.NAME "COMMAND", S.STATUS, W.EVENT "WAITING FOR", S.MACHINE
FROM APPS.FND_RESPONSIBILITY_VL RSP, APPS.FND_FORM_VL FRM, APPS.FND_USER USR, APPS.FND_LOGINS L,
   APPS.FND_LOGIN_RESPONSIBILITIES R, APPS.FND_LOGIN_RESP_FORMS F, SYS.V_$SESSION S , SYS.V_$SESSION_WAIT W, SYS.AUDIT_ACTIONS VA
WHERE R.LOGIN_ID = F.LOGIN_ID
AND R.LOGIN_RESP_ID = F.LOGIN_RESP_ID
AND L.LOGIN_ID = R.LOGIN_ID
-- AND L.END_TIME IS NULL
AND R.END_TIME IS NULL
AND F.END_TIME IS NULL
AND L.USER_ID = USR.USER_ID
AND R.RESPONSIBILITY_ID = RSP.RESPONSIBILITY_ID
AND R.RESP_APPL_ID = RSP.APPLICATION_ID
AND F.FORM_ID = FRM.FORM_ID
AND F.FORM_APPL_ID = FRM.APPLICATION_ID
AND F.AUDSID = S.AUDSID
AND S.SID = W.SID
AND VA.ACTION = S.COMMAND
--and S.SID = '1234'
ORDER BY USR.USER_NAME, START_TIME

 
 

Keywords:

Oracle EBS, R12, R12.2.8, Oracle Forms, Oracle Applications, Query, SQL, SQLPLUS, Logged Users

Basic SQLs – AR Invoice, Receipt, Receipt Application

 

Business Need:

Oracle EBS Financials

AR – Account Receivables

Analysis, Troubleshooting, Reporting, Bug Fixing, User Tracking , <you name it!>

 

Tables:

RA_CUSTOMER_TRX_ALL TRX – AR Transactions (=Customer Invoices)

AR_CASH_RECEIPTS_ALL RCT – AR Receipts

AR_RECEIVABLE_APPLICATIONS_ALL APP – AR Receipt Applications

 

Receipts have Many-to-Many relationship with Transactions. I.e. a single invoice can be applied with 10 receipts. Also a single receipt can be used against 10 invoices.

 

SQLs:

 
SELECT RCT.RECEIPT_NUMBER, TRX.TRX_NUMBER INVOICE_NUM, TRX.CT_REFERENCE SALES_ORDER, TRX.CUSTOMER_TRX_ID, RCT.CASH_RECEIPT_ID, APP.RECEIVABLE_APPLICATION_ID, APP.AMOUNT_APPLIED, TO_CHAR(TRX.CREATION_DATE,'MonDD hh24mi') TRX_CREATION_DATE, TO_CHAR(RCT.CREATION_DATE,'MonDD hh24mi') RCT_CREATION_DATE
    --,  TRX.*, RCT.*, APP.*
FROM AR_RECEIVABLE_APPLICATIONS_ALL APP, RA_CUSTOMER_TRX_ALL TRX, AR_CASH_RECEIPTS_ALL RCT
WHERE APP.APPLIED_CUSTOMER_TRX_ID = TRX.CUSTOMER_TRX_ID
AND TRX.CT_REFERENCE IN ( '4955')
--AND RCT.CREATION_DATE > SYSDATE-1.02
--AND RCT.RECEIPT_NUMBER = '1000026'
--AND TRX.TRX_NUMBER = '20000151'
--AND RCT.CASH_RECEIPT_ID = 1000128
AND APP.CASH_RECEIPT_ID = RCT.CASH_RECEIPT_ID;
 
 
SELECT RCT.RECEIPT_NUMBER, TRX.TRX_NUMBER INVOICE_NUM, TRX.CT_REFERENCE SALES_ORDER, TRX.CUSTOMER_TRX_ID, RCT.CASH_RECEIPT_ID, TO_CHAR(RCT.CREATION_DATE,'MonDD hh24mi') RCT_CREATION_DATE, TO_CHAR(TRX.CREATION_DATE,'MonDD hh24mi') TRX_CREATION_DATE
    -- ,  TRX.*, RCT.*
FROM AR_RECEIVABLE_APPLICATIONS_ALL APP, RA_CUSTOMER_TRX_ALL TRX, AR_CASH_RECEIPTS_ALL RCT
WHERE APP.APPLIED_CUSTOMER_TRX_ID = TRX.CUSTOMER_TRX_ID
AND RCT.CREATION_DATE > SYSDATE-1.02
--AND RCT.RECEIPT_NUMBER = '1000026'
--AND TRX.TRX_NUMBER = '20000151'
--AND TRX.CT_REFERENCE = '4958'
--AND RCT.CASH_RECEIPT_ID = 1000128
AND APP.CASH_RECEIPT_ID = RCT.CASH_RECEIPT_ID;
 
SELECT ARC.RECEIPT_NUMBER, TRX_NUMBER INVOICE_NUM, CT_REFERENCE SALES_ORDER, RAC.*, ARC.*
  FROM AR_RECEIVABLE_APPLICATIONS_ALL APP,       RA_CUSTOMER_TRX_ALL            RAC,        AR_CASH_RECEIPTS_ALL           ARC
 WHERE APP.APPLIED_CUSTOMER_TRX_ID = RAC.CUSTOMER_TRX_ID
   AND APP.CASH_RECEIPT_ID = ARC.CASH_RECEIPT_ID
   AND ARC.RECEIPT_NUMBER = '1000026';
 
 
-- get order number from TX127 ... Merchant Ref Number
 
SELECT NVL(MAX(RAC.CT_REFERENCE),0) SALES_ORDER
FROM AR_RECEIVABLE_APPLICATIONS_ALL APP, RA_CUSTOMER_TRX_ALL RAC, AR_CASH_RECEIPTS_ALL ARC, IBY_FNDCPT_TX_EXTENSIONS IFTE
WHERE APP.APPLIED_CUSTOMER_TRX_ID = RAC.CUSTOMER_TRX_ID
AND IFTE.ORDER_ID = ARC.RECEIPT_NUMBER
AND IFTE.ORIGIN_APPLICATION_ID = 222
AND IFTE.TRXN_EXTENSION_ID = REGEXP_REPLACE('AR127248', '[^0-9]+', '') 
AND APP.CASH_RECEIPT_ID = ARC.CASH_RECEIPT_ID;
 

Keywords:

Oracle EBS, R12, R12.2.8, AR, Accounts Receivables, Financials, Oracle Applications, Query, SQL

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

 

Oracle Application Framework (OAF): Random Scripts and SQLs

 

Introduction:

Some example SQLs (queries) to check and review Oracle Application Framework components within an Oracle database. These scripts/queries are useful to:

1. Ø Review custom and standard OA Objects
2. Ø Remove or disable personalizations
3. Ø Check for existing object for extension or modifications
4. Ø Compare current functionality of a UI with standard functionality

Note: Personalizations can be enabled/disabled at SITE, Responsibility and User levels within EBS.

SQLs:

All Personalizations: All OAF Personalizations within EBS – Both Custom and Standard personalizations are listed by this SQL:

-- all oaf object personalization - custom and standard
SELECT PATH.PATH_DOCID PERZ_DOC_ID,
jdr_mds_internal.getdocumentname(PATH.PATH_DOCID) PERZ_DOC_PATH
FROM JDR_PATHS PATH
WHERE PATH.PATH_DOCID IN
(SELECT DISTINCT COMP_DOCID FROM JDR_COMPONENTS
WHERE COMP_SEQ = 0 AND COMP_ELEMENT = 'customization'
AND COMP_ID IS NULL)
ORDER BY PERZ_DOC_PATH;
 

Personalizations within a Module: All personalizations within an EBS Module. In this example, OIE (Oracle Internet Expenses à iExpenses) is used. Substitute with any EBS Standard module codes to get its customizations.

 

SELECT PATH.PATH_DOCID PERZ_DOC_ID,
jdr_mds_internal.getdocumentname(PATH.PATH_DOCID) PERZ_DOC_PATH
FROM JDR_PATHS PATH
WHERE PATH.PATH_DOCID IN
(SELECT DISTINCT COMP_DOCID FROM JDR_COMPONENTS
WHERE COMP_SEQ = 0 AND COMP_ELEMENT = 'customization'
and jdr_mds_internal.getdocumentname(PATH.PATH_DOCID) like '%oie%'            -- all from OIE - iExpenses
AND COMP_ID IS NULL)
ORDER BY PERZ_DOC_PATH;
 

 

All Objects within a Module: List of all OAF objects within a module. The module AP (Account Payables) is used here. Change module code accordingly (Eg: OE for Order Entry, ONT for Order management, PO for Purchasing, etc)

-- list all page, regions, customizations, personalizations
set serveroutput on;
set linesize 300;
 
DECLARE
BEGIN
jdr_utils.listdocuments('/oracle/apps/ap/', TRUE);
END;
/
 

  

JDR_UTILS Functions: JDR_UTILS is a standard package that provide lot of utilities to manage OA Framework pages and files. Here are some major functions available for OAF Troubleshooting usage:

JDR_UTILS Functions:
listCustomizations
printDocument
deleteDocument
listDocuments

 

Example jdr_utils.listCustomizations:

 

jdr_utils.listCustomizations()
This procedure can be used to check whether any personalization exists for a particular page or  substitution exists for a particular EO/VO/AM.
 begin 
  jdr_utils.listCustomizations('/xxabc/oracle/apps/fnd/xxabc/webui/XxabcPG'); 
 end; 
 begin 
  jdr_utils.listCustomizations('/xxabc/oracle/apps/fnd/xxabc/server/XxabcVO'); 
 end; 
 

 

Example jdr_utils. printDocument:

 

jdr_utils.printDocument()
This procedure can be used to get the Page / Personalization / Substitution file. You can pass the output of the above procedure as a parameter to this procedure to get the details.
 begin 
  jdr_utils.printDocument('/xxabc/oracle/apps/fnd/xxabc/webui/XxabcPG'); 
 end; 
 begin 
  jdr_utils.printDocument('/xxabc/oracle/apps/fnd/xxabc/webui/customizations/site/0/XxabcPG'); 
 end; 
 begin 
  jdr_utils.printDocument('/xxabc/oracle/apps/fnd/xxabc/server/customizations/site/0/XxabcVO'); 
 end;
 

 

Example jdr_utils. deleteDocument:

 
 begin  
   jdr_utils.deleteDocument('/xxabc/oracle/apps/fnd/xxabc/webui/XxabcPG'); 
 end; 
 begin 
   jdr_utils.deleteDocument('/xxabc/oracle/apps/fnd/xxabc/webui/customizations/site/0/XxabcPG'); 
 end; 
 begin 
   jdr_utils.deleteDocument('/xxabc/oracle/apps/fnd/xxabc/server/customizations/site/0/XxabcVO'); 
 end; 
 

 

Example jdr_utils. listdocuments:

This procedure will print all the files under the specified path.
 begin 
   jdr_utils.listDocuments('/xxabc/oracle/apps/fnd/xxabc/webui'); 
 end; 
 
You can add an additional parameter to recursively print all the documents under the specified path
 begin 
   jdr_utils.listDocuments('/xxabc/oracle/apps/fnd/xxabc',true); 
 end; 
 
 

 

Keywords:

OAF, OA Framework, Oracle Application Framework, Oracle, UIX, Customization, Personalization, Application Module, PG.XML, Query, SQL, SQLPLUS