Skip to main content

Hello All,

I have a requirement to create a CSV file with the data from a quickreport(select) in IFS9 and write it to a folder everyday. I created a database task and was unable to create the process that creates the CSV file.

Regards,

Henrique

Hi Henrique,

below code snippet will help you to create the csv from quick report SQL. You can save the created csv to a database directory using oracle UTL_FILE package or create an application message and route to a file destination using IFS Connect.

 DECLARE

  csv_header_ VARCHAR2(32000);
  csv_line_ VARCHAR2(32000);
  csv_file_ CLOB;


  -- Your quick report SQL
  CURSOR get_po_lines IS
  select * from PURCHASE_ORDER_LINE_TAB
  where order_no = '123';

BEGIN

  — Create CSV header columns
  csv_header_ := 'ORDER_NO;LINE_NO;RELEASE_NO;DESCRIPTION;BUY_QTY_DUE;BUY_UNIT_PRICE;STATE' ||chr(13)||chr(10);
  csv_file_ := csv_header_;
  — Create the CSV
  FOR rec_ IN get_po_lines LOOP
    csv_line_ := NULL;
    csv_line_ := rec_.ORDER_NO || ';' || rec_.LINE_NO || ';' || rec_.RELEASE_NO || ';' || rec_.DESCRIPTION || ';' || rec_.BUY_QTY_DUE || ';' || rec_.BUY_UNIT_PRICE || ';' || rec_.ROWSTATE|| ';' ||chr(13)||chr(10);
    csv_file_ := csv_file_ || csv_line_;
  END LOOP;

-- write the file to database directory using UTL_FILE 
-- or create an application message and route to a file folder using IFS Connect

END;

 

Hope it helps!

Damith

Reply


Terms & ConditionsCookie settings