"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

Get Print Count in IFS

To get the print count for each report in IFS.

IFS Tips & Tricks

Rumesh Aponso (RMAX)

Sep 41 min read

Connection between Print Job and Archive in IFS

IFS Tips & Tricks

Rumesh Aponso (RMAX)

Sep 41 min read

BLOB to CLOB in PLSQL

How to get file content in a BLOB file into a CLOB in PL/SQL. The below function can be used if the BLOB file contains text format data...

PL/SQL Solutions

Rumesh Aponso (RMAX)

Feb 252 min read

LISTAGG in PLSQL

Example 1: SELECT DISTINCT LISTAGG(t.commission_receiver, ';') WITHIN GROUP (ORDER BY t.commission_receiver) FROM...

PL/SQL Solutions

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

IFS Tips & Tricks

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

PL/SQL Solutions

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',...

PL/SQL Solutions

Rumesh Aponso (RMAX)

Dec 10, 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'; ...

PL/SQL Solutions

Rumesh Aponso (RMAX)

Nov 29, 20241 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...

PL/SQL Solutions

Rumesh Aponso (RMAX)

Nov 28, 20242 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...

PL/SQL Solutions

Rumesh Aponso (RMAX)

Nov 28, 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

IFS Tips & Tricks

Rumesh Aponso (RMAX)

Nov 27, 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)...

PL/SQL Solutions

Rumesh Aponso (RMAX)

Nov 26, 20241 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;...

PL/SQL Solutions

Rumesh Aponso (RMAX)

Nov 25, 20242 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...

PL/SQL Solutions

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 =...

Oracle Insights

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'); ...

IFS Tips & Tricks

Rumesh Aponso (RMAX)

Nov 25, 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...

PL/SQL Solutions

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

PL/SQL Solutions

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_...

PL/SQL Solutions

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

IFS Tips & Tricks

Rumesh Aponso (RMAX)

Nov 22, 20241 min read