Solved

Create records (custom LU) from XML File Reader

  • 17 May 2021
  • 5 replies
  • 78 views

Userlevel 1
Badge +5

I am stumped.

 

Can someone help or point me where to figure out how I can drop a file into the file reader, read it in, and create a new record in a custom logic unit?

I know I could do this with existing LUs using BizAPI but the LU I’m using is custom and has no BizAPI for it.

icon

Best answer by NSRJMITCHELL 18 May 2021, 16:50

I ended up creating a custom PL/SQL package function that accepts a clob and extracts the values using a cursor. I then iterate over the cursor and insert the values into the custom table. I call this custom function from a Routing Rule and Routing Address when the file is read in.

View original

5 replies

Userlevel 1
Badge +5

I ended up creating a custom PL/SQL package function that accepts a clob and extracts the values using a cursor. I then iterate over the cursor and insert the values into the custom table. I call this custom function from a Routing Rule and Routing Address when the file is read in.

Userlevel 5
Badge +9

@NSRJMITCHELL are you able to share screen images of your routing rule and routing address? i am interested in this side of IFS but have no experience, anything like this i find helpful and interesting.

Userlevel 1
Badge +5

Sure! 

 

This is the function I wrote in PL/SQL and the Routing Rule, Routing Address, and Presentation Objects are all attached.

 

Hopefully that helps!

FUNCTION Receive_EDI_Acknowledgements(doc_ IN CLOB) RETURN CLOB IS

PROCEDURE Core(doc_ IN CLOB) IS
CURSOR ACKS IS
SELECT EXTRACTVALUE(COLUMN_VALUE, '/DocumentAck/AckStatusNotes') as NOTES,
EXTRACTVALUE(COLUMN_VALUE, '/DocumentAck/AckStatus') as STATUS,
TO_DATE(EXTRACTVALUE(COLUMN_VALUE, '/DocumentAck/Date'),
'YYMMDDHH24:MI:SS') as REC_DATE,
EXTRACTVALUE(COLUMN_VALUE, '/DocumentAck/DocumentNumber') as DOCUMENT_NUMBER,
EXTRACTVALUE(COLUMN_VALUE, '/DocumentAck/DocumentType') as DOCUMENT_TYPE
FROM TABLE(XMLSEQUENCE(XMLTYPE(doc_).EXTRACT('/OUTPUT/*'))) t;
BEGIN
FOR ack_row IN ACKS LOOP
INSERT INTO EDI_ACKNOWLEDGEMENT_CLT
(CF$_ACK_NOTES,
CF$_ACK_STATUS,
CF$_DATE,
CF$_DOCUMENT_NUMBER,
CF$_DOCUMENT_TYPE,
ROWVERSION)
VALUES
(ack_row.NOTES,
ack_row.STATUS,
ack_row.REC_DATE,
ack_row.DOCUMENT_NUMBER,
ack_row.DOCUMENT_TYPE,
SYSDATE);
END LOOP;
COMMIT;
END Core;
BEGIN
Core(doc_);

RETURN NULL;
END Receive_EDI_Acknowledgements;

 

Userlevel 5
Badge +9

@NSRJMITCHELL thank you so much for sharing! love the Black IFS… how’d you do that?

Userlevel 1
Badge +5

@NSRJMITCHELL thank you so much for sharing! love the Black IFS… how’d you do that?

Using the “Graphite” theme that comes with IFS

Reply