top of page
"All truths are easy to understand once they are discovered; the point is to discover them."
Galileo Galilei
RMAXOneNote covers a wide range of programming languages and technologies (Oracle PL/SQL, C#, Java, etc.), code examples, and solutions related to IFS ERP. The purpose of this blog is to share knowledge among developers about the problems they encounter daily.
PL/SQL Solutions
Rumesh Aponso (RMAX)
Dec 10, 20241 min read
LISTAGG in PLSQL
Example 1: SELECT DISTINCT LISTAGG(t.commission_receiver, ';') WITHIN GROUP (ORDER BY t.commission_receiver) FROM...
2 views0 comments
Rumesh Aponso (RMAX)
Dec 10, 20241 min read
Get Foundation Error from ORA Error
FUNCTION Strip_Ora_Error ( sqlerrm_Â Â Â Â Â Â Â IN VARCHAR2, Â Â sqlcode_Â Â Â Â Â Â Â IN NUMBER DEFAULT NULL, Â Â strip_ora_only_ IN BOOLEAN DEFAULT...
1 view0 comments
Rumesh Aponso (RMAX)
Dec 10, 20241 min read
CONNECT BY PRIOR / Hierarchical Queries / Query Tree Structure
Example 1: SELECTÂ handling_unit_id, t.shipment_id FROMÂ Â Â handling_unit_tab t STARTÂ Â WITH handling_unit_id = '4558' CONNECT BY PRIOR...
3 views0 comments
Rumesh Aponso (RMAX)
Dec 10, 20241 min read
Find Database Objects in Oracle
SELECT DISTINCT object_type FROM all_objects; SELECT t.* FROM all_objects t WHERE t.object_type IN ('VIEW', 'TABLE', 'INDEX',...
2 views0 comments
Rumesh Aponso (RMAX)
Nov 29, 20241 min read
Encode Decode base64 in PLSQL
Decode base64 in PLSQL: DECLARE CURSOR get_base64_clob IS SELECT neutral_file FROM c_smart_document_tab WHERE sequence_no = '42'; ...
32 views0 comments
Rumesh Aponso (RMAX)
Nov 28, 20242 min read
Ways to save CLOB to a File in PLSQL
These PL/SQL code examples show how to save a CLOB to a File. Example 1: DECLARE clob_ CLOB := 'A Character Large OBject (or...
5 views0 comments
Rumesh Aponso (RMAX)
Nov 28, 20241 min read
Convert CLOB to BLOB in PLSQL
The PL/SQL code below shows how to convert a CLOB to a BLOB and save it in a database directory. DECLARE clob_ CLOB := 'A...
12 views0 comments
Rumesh Aponso (RMAX)
Nov 27, 20241 min read
Object Property in IFS
BEGIN Â Â Object_Property_API.Add_Value('LU_NAME', 'OBJECT_KEY', 'PROPERTY_NAME', 'VALUE1;VALUE2;'); END; / IFS APP10 IFS APP7.5
3 views0 comments
Rumesh Aponso (RMAX)
Nov 26, 20241 min read
How to Read BLOB in PLSQL
Below code examples shows how to read a file in Oracle Directory into a BLOB in PLSQL. Example 1: DECLARE dir_ VARCHAR2(100)...
11 views0 comments
Rumesh Aponso (RMAX)
Nov 25, 20242 min read
Array in PLSQL
Example 1: VARRAY TYPE 1 DECLARE TYPE namesarray IS VARRAY(5) OF VARCHAR2(10); names_ namesarray; TYPE grades IS VARRAY(5) OF INTEGER;...
5 views0 comments
Rumesh Aponso (RMAX)
Nov 25, 20241 min read
Sort Numbers in PLSQL
Issue: Solution: Sort Numbers Only ORDER BY REGEXP_REPLACE( <COLUMN_NAME> , '[[:digit:]]', '~') ASC ; Sort Numbers and Text ORDERÂ BY...
1 view0 comments
Rumesh Aponso (RMAX)
Nov 25, 20241 min read
Create Database Link in Oracle
CREATE DATABASE LINK <DB_LINK_NAME> CONNECT TO <SCHEMA> IDENTIFIED BY <PASSWORD> USING '(DESCRIPTION = (ADDRESS_LIST = (ADDRESS =...
2 views0 comments
Rumesh Aponso (RMAX)
Nov 25, 20241 min read
Trigger System Event in IFS
IF (Event_SYS.Event_Enabled('ProdStructure', 'PROD_STRUCTURE_COPIED')) THEN c_msg_ := Message_SYS.Construct('PROD_STRUCTURE_COPIED'); ...
3 views0 comments
Rumesh Aponso (RMAX)
Nov 22, 20241 min read
How to convert Numbers to Words in PLSQL
--julian date must be between 1 and 5373484 SELECT 12358, (TO_CHAR(TO_DATE(12358, 'j'), 'jsp')) FROM DUAL Output: Related Links: Spell...
8 views0 comments
Rumesh Aponso (RMAX)
Nov 22, 20241 min read
Get Only Numbers from a String in PLSQL
Example 1: SELECT REGEXP_REPLACE(TRIM(REGEXP_SUBSTR('print document 2000, document 1000', '[^,]+', 1, LEVEL)), '[^[:digit:]]', '') str...
10 views0 comments
Rumesh Aponso (RMAX)
Nov 22, 20241 min read
Temporary Table in PLSQL
DECLARE --table row type TYPE customer_order_tab_type IS TABLE OF customer_order_tab%ROWTYPE INDEX BY BINARY_INTEGER; customer_order_tab_...
7 views0 comments
Rumesh Aponso (RMAX)
Nov 22, 20241 min read
Send Email via Event in IFS
IF (Event_SYS.Event_Enabled(lu_name_, 'PART_FULFILL'))THEN msg_ := Message_Sys.Construct('PART_FULFILL'); Message_Sys.Add_Attribute(m...
5 views0 comments
Rumesh Aponso (RMAX)
Nov 22, 20241 min read
Send Email via Server Side in IFS
Command_SYS.Mail(Fnd_Session_API.Get_Fnd_User(), email_id_, message_body_, subject_ => subject_, attach_ => attachment_);
3 views0 comments
Rumesh Aponso (RMAX)
Nov 22, 20241 min read
Timestamp to Date in PLSQL
d_start_time_ := TO_DATE(TO_CHAR(start_time_, 'YYYY-MM-DD-HH24.MI.SS', 'NLS_CALENDAR=GREGORIAN'), 'YYYY-MM-DD-HH24.MI.SS',...
0 views0 comments
Rumesh Aponso (RMAX)
Nov 22, 20241 min read
TO_DATE in PLSQL
Example 1: TO_DATE('2020-10-29-11.40.15', 'YYYY-MM-DD-HH24.MI.SS', 'NLS_CALENDAR=GREGORIAN'); Example 2: SELECT...
6 views0 comments
bottom of page