Outbound Integration - CSV file creation

Thank you for Sharing Heinz .. I will give it a try .

Hi ​@Thejaswini 

Did you generate the csv file with this option?
I used the same mechanism in APPS10, but it is getting below error

Error while preparing input data. 
Caused by: ifs.fnd.base.SystemException: Invalid xml format

 

I tried with below code.

 PLSQLAP_Server_API.Post_Outbound_BizAPI(
      bizapi_name_  => null,
      xml_   => csv_clob, 
      sender_ => 'IFS',
      message_type_ => 'MALJLK');

Did I miss anything??

Hello Malan,
Yes, I was able to generate CSV and your code looks good.

How did you structure your Clop variable?

Regards

Thej

Hi ​@Thejaswini 

I used the attached sample for testing.
It has the clob variable structure.

I have attached the Sample code I have used.

Hope this helps.

Hi ​@Thejaswini 

Thank you for providing the sample file. I have identified the root cause of the issue.

Initially, I implemented the solution in the APPS10 UPD0 version, where the CSV file format was not supported. I then reimplemented the same solution in the APPS10 UPD21 environment, and the CSV file was successfully transferred without any issues.

It is possible that the CSV file format was not supported in earlier UPD versions.

/Malan Jayanka

And how to do the same thing in Cloud?

Because Plsqlap_Server_API.Post_Outbound_BizAPI does not work there any more:

PROCEDURE Post_Outbound_Bizapi (
   bizapi_name_ VARCHAR2, message_body_ IN OUT NOCOPY Plsqlap_Document_API.Document, sender_ VARCHAR2 DEFAULT NULL, receiver_ VARCHAR2 DEFAULT NULL, message_type_ VARCHAR2 DEFAULT NULL, message_function_ VARCHAR2 DEFAULT NULL, subject_ VARCHAR2 DEFAULT NULL, in_order_ BOOLEAN DEFAULT FALSE) 
IS
BEGIN
   IF (Log_SYS.extended_trace_enabled_) THEN
      Log_SYS.Put_Method_Trace('Plsqlap_Server_API.Post_Outbound_Bizapi (Foundation1)', 'bizapi_name_', bizapi_name_, 'message_body_', '(Plsqlap_Document_API.Document)', 'sender_', sender_, 'receiver_', receiver_, 'message_type_', message_type_, 'message_function_', message_function_, 'subject_', subject_, 'in_order_', Log_SYS.Boolean_To_Varchar2(in_order_));
   END IF;
   Error_SYS.Appl_General(lu_name_, 'Procedure Post_Outbound_Bizapi is not supported');
END Post_Outbound_Bizapi;

Answering my own question:

Transformer: CDATA_FROM_XML does the job.

The message can be created like this, where ‘msg_’ was previously put together as the full contents of the CSV file:

      msg_ := '<ShopOrdDataToMES><![CDATA['|| msg_ || ']]></ShopOrdDataToMES>';

      Plsqlap_Server_API.Post_Outbound_Message(
         msg_,
         sender_           => REPLACE(so_.contract||'_'||so_keys_||'_'||TO_CHAR(SYSTIMESTAMP,'YYYYMMDD_HH24MISS_FF3'), '*', '#'),
         receiver_         => so_.contract,
         message_function_ => 'SENDING_CSV_TO_MES');

What was the specific reason to use the Transformer? input file always get empty for me. I didnt use a transformer. was that the why you build the msg_ like this and use the transformer. so msg_ is initially the csv structured clob and you re assigning message in this way to transform it to csv?. The mentioned transformers usage is like below
 

can you please share the way you created the csv

And how to do the same thing in Cloud?

 

Because Plsqlap_Server_API.Post_Outbound_BizAPI does not work there any more:

 

PROCEDURE Post_Outbound_Bizapi (
   bizapi_name_ VARCHAR2, message_body_ IN OUT NOCOPY Plsqlap_Document_API.Document, sender_ VARCHAR2 DEFAULT NULL, receiver_ VARCHAR2 DEFAULT NULL, message_type_ VARCHAR2 DEFAULT NULL, message_function_ VARCHAR2 DEFAULT NULL, subject_ VARCHAR2 DEFAULT NULL, in_order_ BOOLEAN DEFAULT FALSE) 
IS
BEGIN
   IF (Log_SYS.extended_trace_enabled_) THEN
      Log_SYS.Put_Method_Trace('Plsqlap_Server_API.Post_Outbound_Bizapi (Foundation1)', 'bizapi_name_', bizapi_name_, 'message_body_', '(Plsqlap_Document_API.Document)', 'sender_', sender_, 'receiver_', receiver_, 'message_type_', message_type_, 'message_function_', message_function_, 'subject_', subject_, 'in_order_', Log_SYS.Boolean_To_Varchar2(in_order_));
   END IF;
   Error_SYS.Appl_General(lu_name_, 'Procedure Post_Outbound_Bizapi is not supported');
END Post_Outbound_Bizapi;

Answering my own question:

Transformer: CDATA_FROM_XML does the job.

The message can be created like this, where ‘msg_’ was previously put together as the full contents of the CSV file:

      msg_ := '<ShopOrdDataToMES><![CDATA['|| msg_ || ']]></ShopOrdDataToMES>';

      Plsqlap_Server_API.Post_Outbound_Message(
         msg_,
         sender_           => REPLACE(so_.contract||'_'||so_keys_||'_'||TO_CHAR(SYSTIMESTAMP,'YYYYMMDD_HH24MISS_FF3'), '*', '#'),
         receiver_         => so_.contract,
         message_function_ => 'SENDING_CSV_TO_MES');

What was the specific reason to use the Transformer? input file always get empty for me. I didnt use a transformer. was that the why you build the msg_ like this and use the transformer. so msg_ is initially the csv structured clob and you re assigning message in this way to transform it to csv?. The mentioned transformers usage is like below
 

can you please share the way you created the csv

‘msg_’ in the example above contains the CSV.

You use whatever method in PL/SQL you want, for instance plain string concatenation,  to put together a string (msg_) and then create a ‘dummy’ xml structure around it (see above, with ‘ShopOrdDataToMES’, containing a CDATA element to be able pass it through the mechanisms of file sending in IFS. It is quite irrelevant what way you do the string construction of CSV,  that is just a string, usually with new line characters in it if you want a good looking CSV.

The transformer has a key role here, because it extract the contents of CDATA and output that as the new content that will become a file.

And how to do the same thing in Cloud?

 

Because Plsqlap_Server_API.Post_Outbound_BizAPI does not work there any more:

 

PROCEDURE Post_Outbound_Bizapi (
   bizapi_name_ VARCHAR2, message_body_ IN OUT NOCOPY Plsqlap_Document_API.Document, sender_ VARCHAR2 DEFAULT NULL, receiver_ VARCHAR2 DEFAULT NULL, message_type_ VARCHAR2 DEFAULT NULL, message_function_ VARCHAR2 DEFAULT NULL, subject_ VARCHAR2 DEFAULT NULL, in_order_ BOOLEAN DEFAULT FALSE) 
IS
BEGIN
   IF (Log_SYS.extended_trace_enabled_) THEN
      Log_SYS.Put_Method_Trace('Plsqlap_Server_API.Post_Outbound_Bizapi (Foundation1)', 'bizapi_name_', bizapi_name_, 'message_body_', '(Plsqlap_Document_API.Document)', 'sender_', sender_, 'receiver_', receiver_, 'message_type_', message_type_, 'message_function_', message_function_, 'subject_', subject_, 'in_order_', Log_SYS.Boolean_To_Varchar2(in_order_));
   END IF;
   Error_SYS.Appl_General(lu_name_, 'Procedure Post_Outbound_Bizapi is not supported');
END Post_Outbound_Bizapi;

Answering my own question:

Transformer: CDATA_FROM_XML does the job.

The message can be created like this, where ‘msg_’ was previously put together as the full contents of the CSV file:

      msg_ := '<ShopOrdDataToMES><![CDATA['|| msg_ || ']]></ShopOrdDataToMES>';

      Plsqlap_Server_API.Post_Outbound_Message(
         msg_,
         sender_           => REPLACE(so_.contract||'_'||so_keys_||'_'||TO_CHAR(SYSTIMESTAMP,'YYYYMMDD_HH24MISS_FF3'), '*', '#'),
         receiver_         => so_.contract,
         message_function_ => 'SENDING_CSV_TO_MES');

What was the specific reason to use the Transformer? input file always get empty for me. I didnt use a transformer. was that the why you build the msg_ like this and use the transformer. so msg_ is initially the csv structured clob and you re assigning message in this way to transform it to csv?. The mentioned transformers usage is like below
 

can you please share the way you created the csv

‘msg_’ in the example above contains the CSV.

You use whatever method in PL/SQL you want, for instance plain string concatenation,  to put together a string (msg_) and then create a ‘dummy’ xml structure around it (see above, with ‘ShopOrdDataToMES’, containing a CDATA element to be able pass it through the mechanisms of file sending in IFS. It is quite irrelevant what way you do the string construction of CSV,  that is just a string, usually with new line characters in it if you want a good looking CSV.

The transformer has a key role here, because it extract the contents of CDATA and output that as the new content that will become a file.

I can’t see the reason for complicating a very straight forward and easy task with a CDATA field, and all this business with a transformer, in order to make it work in IFS Cloud.

The only change you need to do relative the original answer for Apps 10, is to use a different method when posting your CLOB variable, since the method Post_Outbound_BizAPI was obsoleted with the first IFS Cloud release.
 

Send out a text file, IFS Cloud version:

Create a clob as per usual, representing the content of the text-file you want, and then just call this method:

Plsqlap_Server_API.Post_Outbound_Message(
      xml_              => csv_clob_,
      sender_           => ‘IFS’,
      receiver_         => receiver_,
      message_function_ => 'MY_MESSAGE_FUNCTION');

Set up an outbound routing rule and connect an outbound address in IFS Connect.

Leave the Envelope empty and set the output file name according to your requirements. The content of the file is a plain text file.

In the Application Message page, the Input Message Data section, the name of the file created will be <MESSAGE_FUNCTION>.xml, but it will still be a plain text file.