Oracle Applications hand book : June 2015

1. These events are introduced for ASL integration with ERES. The ER documentation might have more info.

oracle.apps.po.asl.create -> PO ERES ASL Creation
oracle.apps.po.asl.update ->PO ERES ASL Update
oracle.apps.po.event.create_asl->Create New Approved Supplier List Entry
oracle.apps.po.event.update_asl ->Update Existing Approved Supplier List Entry

2. These events are launched during the XML Communication in PO Approval wf,after the PO is approved.

oracle.apps.po.event.setxml -> Set up PO via XML
oracle.apps.po.event.xmlpo -> Send PO via XML
oracle.apps.po.event.xmlpogen -> XML PO document is generated
oracle.apps.po.event.xmlposent -> PO Sent via XML

3. These events are newly introduced for Change Requests and signature flow (11.5.9 - 11.5.10.CU2)

oracle.apps.po.buyersignature -> Buyer Signature Event
oracle.apps.po.suppliersignature -> Supplier Signature Event
oracle.apps.po.event.requester_change -> Requester change Request pending
oracle.apps.po.event.suppchnresp -> Response to Supplier Change Request Event
oracle.apps.po.event.supplier_change -> Supplier Change, and PO is Responded

4. OTM Event - These event is launched for OTM integration flow, to send the PO details to OTM.

oracle.apps.po.event.document_action_event -> Document Action Event

5. Receiving Events

oracle.apps.po.group.receive -> PO ERES Receiving Group Event
oracle.apps.po.line.receive -> PO ERES Receiving Line Event
oracle.apps.po.rcv.deliver -> PO ERES Receiving Delivery
oracle.apps.po.rcv.inspect -> PO ERES Receiving Inspection
oracle.apps.po.rcv.rcvtxn -> Receive Transaction
oracle.apps.po.rcv.transfer -> PO ERES Receiving Transfer

REGEXP_REPLACE function replaces string with regular expression matching supports. The simplest format for this function is:

REGEXP_REPLACE (source_string, pattern_to_find, pattern_to_replace_by)

The general format for the REGEXP_REPLACE function with all the options is

REGEXP_REPLACE (source_string, 
                pattern_to_find, 
               [pattern_to_replace_by, 
                position, 
                occurrence,
                match_parameter])


source_string:the string you want to search for
pattern-to-find:the pattern used to search.
pattern_to_replace_by: pattern used to do the matching
position:indicates where to start.
occurrence:indicates which occurrence of the pattern-to-find in the source-string you want to search for. For example, which occurrence of "si" do you want to extract from the source string "Mississippi".
match-parameter:for further customizing.

"i" in match-parameter can be used for caseinsensitive matching
"c" in match-parameter can be used for casesensitive matching
"n" in match-parameter allows the period to match the new line character
"m" in match-parameter allows for more than one line in source-string





Metacharacters Meaning

\ Specify the escape sequence
\d Digit character
\D Non-digit character
\w Word character
\W Non-word character
\s Whitespace character
\S Non-whitespace character
\A Matches only at the beginning of a string or before a newline character at the end of a string
\Z Matches only at the end of a string
^ Matches the position at the start of the string.
$ Matches the position at the end of the string.
* Matches the preceding character zero or more times.
+ Matches the preceding character one or more times.
? Matches the preceding character zero or one time.
*? Matches the preceding pattern element 0 or more times
+? Matches the preceding pattern element 1 or more times
?? Matches the preceding pattern element 0 or 1 time
{n} Matches a character exactly n times, where n is an integer.
{n,} Matches the preceding pattern element at least n times
{n,m} Matches a character at least n times and at most m times, where n and m are both integers.
. Matches any single character except null.
(pattern) A subexpression that matches the specified pattern.
x|y Matches x or y, where x and y are one or more characters. war|peace matches war or peace.
[abc] Matches any of the enclosed characters.
[a-z] Matches any character in the specified range.
[:alphanum:] matches alphanumeric characters 0-9, A-Z, and a-z.
[:alpha:] matches alphabetic characters A-Z and a-z.
[:blank:] matches space or tab.
[:digit:] matches digits 0-9.
[:graph:] matches non-blank characters.
[:lower:] matches lowercase alphabetic characters a-z.
[:print:] is similar to [:graph:] except [:print:] includes the space character.
[:punct:] matches punctuation characters .,"`, and so on.
[:space:] matches all whitespace characters.
[:upper:] matches all uppercase alphabetic characters A-Z.
[:xdigit:] matches characters permissible in a hexadecimal number 0-9, A-F, and a-f.
[..] Matches one collation element, like a multicharacter element.
[==] Specifies equivalence classes.
\n A backreference to an earlier capture, where n is a positive integer.



Example:




Example Description

\n matches the newline character
\\ matches \
\( matches (
^A matches if A is the first character in the string.
$B matches if B is the last character in the string.
f*d matches flood, food, and so on.
fo+d matches fod, food, and so on.
fo?d matches fd and fod only.
fo{2}d matches food.
fo{2,3}d matches food and foood only.
[ab]bc matches abc and bbc.
[a-c]bc matches abc, bbc, and cbc.




SQL> SELECT REGEXP_REPLACE('Mississippi', 'si', 'SI', 1, 0, 'i') FROM dual;

REGEXP_REPL
-----------
MisSIsSIppi

SQL> SELECT REGEXP_REPLACE('lord, llll','l[[:alpha:]]{2}', 'ssss') AS result FROM dual;

RESULT
------------
ssssd, ssssl








SQL Query to remove Non-Numeric characters from a String




Using TRANSLATE and REPLACE Function

We can use this method when we are completely aware of what all non-numeric characters that would be present in the input value. Here in this example i am trying to remove non-numeric characters from phone number field


SELECT TRANSLATE(REPLACE(LOWER('Ph +91 984-809-8540'),'(0) -',' '),'abcdefghijklmnopqrstuvwxyz()- +/,.#',' ') OUT_PUT FROM dual;



rs from phone number field


SELECT TRANSLATE(REPLACE(LOWER('Ph +91 984-809-8540'),'(0) -',' '),'abcdefghijklmnopqrstuvwxyz()- +/,.#',' ') OUT_PUT FROM dual;

Metacharacters	Meaning
\	Specify the escape sequence
\d	Digit character
\D	Non-digit character
\w	Word character
\W	Non-word character
\s	Whitespace character
\S	Non-whitespace character
\A	Matches only at the beginning of a string or before a newline character at the end of a string
\Z	Matches only at the end of a string
^	Matches the position at the start of the string.
$	Matches the position at the end of the string.
*	Matches the preceding character zero or more times.
+	Matches the preceding character one or more times.
?	Matches the preceding character zero or one time.
*?	Matches the preceding pattern element 0 or more times
+?	Matches the preceding pattern element 1 or more times
??	Matches the preceding pattern element 0 or 1 time
{n}	Matches a character exactly n times, where n is an integer.
{n,}	Matches the preceding pattern element at least n times
{n,m}	Matches a character at least n times and at most m times, where n and m are both integers.
.	Matches any single character except null.
(pattern)	A subexpression that matches the specified pattern.
x\|y	Matches x or y, where x and y are one or more characters. war\|peace matches war or peace.
[abc]	Matches any of the enclosed characters.
[a-z]	Matches any character in the specified range.
[:alphanum:]	matches alphanumeric characters 0-9, A-Z, and a-z.
[:alpha:]	matches alphabetic characters A-Z and a-z.
[:blank:]	matches space or tab.
[:digit:]	matches digits 0-9.
[:graph:]	matches non-blank characters.
[:lower:]	matches lowercase alphabetic characters a-z.
[:print:]	is similar to [:graph:] except [:print:] includes the space character.
[:punct:]	matches punctuation characters .,"`, and so on.
[:space:]	matches all whitespace characters.
[:upper:]	matches all uppercase alphabetic characters A-Z.
[:xdigit:]	matches characters permissible in a hexadecimal number 0-9, A-F, and a-f.
[..]	Matches one collation element, like a multicharacter element.
[==]	Specifies equivalence classes.
\n	A backreference to an earlier capture, where n is a positive integer.

Example	Description
\n	matches the newline character
\\	matches \
\(	matches (
^A	matches if A is the first character in the string.
$B	matches if B is the last character in the string.
f*d	matches flood, food, and so on.
fo+d	matches fod, food, and so on.
fo?d	matches fd and fod only.
fo{2}d	matches food.
fo{2,3}d	matches food and foood only.
[ab]bc	matches abc and bbc.
[a-c]bc	matches abc, bbc, and cbc.


The above query would return a output as “919848098540” but if the input is something like ‘Ph: +91 984-809-8540‘ then this would return an output ‘:919848098540‘ since we are not handling the character ‘:‘. So we can’t go for this method if we are not sure of the all possible non-numeric characters that would come in as input.

Using Regular Expression:

regexp_replace function replaces string with regular expression matching supports. The simplest format for this function is:


REGEXP_REPLACE (source_string, pattern_to_find, pattern_to_replace_by)


For more information about regexp_replace please read this article from oracle.com


SELECT to_number(regexp_replace('Ph: +91 984-809-8540', '[^0-9]+', '')) OUT_PUT FROM dual;





The above statement would replace all the characters except the digits 0-9 with null.


SELECT to_number(regexp_replace('Ph: +91 984-809-8540', '\D', '')) OUT_PUT FROM dual;




In this statement ‘\D’ would find all Non-digit characters and the will be replaced by null.



Though the above two statements works well, there is a scenario where these two statements fail to work, let me tell you with an example


SELECT to_number(regexp_replace('0*0-7-', '[^0-9]+', '')) OUT_PUT FROM dual;
SELECT to_number(regexp_replace('0*0-7-', '\D', '')) OUT_PUT FROM dual;




we expect the above statement to return ‘007’ instead they would return ‘7’. this method omitts if we have digit ‘0’ as prefix.


The best method I found is to use regexp Metacharacter ‘:digit:‘ which matches digits 0-9


SELECT REGEXP_REPLACE( '0*0-7-', '[^[:digit:]]', NULL ) OUT_PUT FROM DUAL;





SELECT regexp_replace( 'Ph: +91 984-809-8540', '[^[:digit:]]', NULL ) FROM dual;