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 – 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