Solved

Is there a Way to Automate an Output Channel?

  • 7 February 2022
  • 5 replies
  • 428 views
Charith Epitawatta
Rank
Userlevel 7
Badge +31

Created on behalf of a customer.

I am looking to find out if there is a way to trigger the output to CSV for:

Application Base Setup > General Data > Coordinators

I can from the context menu, select Output > output to CSV, but I want to output it or “save as” to a location in an automated fashion.

  • can you advise if there is a way to do this?
  • Or is there a way I can trigger the SQL report that generates this? If so, can you provide the SQL code for this report?
  • Can I create a connection from PowerShell or other scripting language, so the output can be used in another process?
icon

Best answer by ZTC ZTC JGOTA 7 February 2022, 23:03

View original
Charith - https://charith.xyz

5 replies

Charith Epitawatta
Rank
Userlevel 7
Badge +31

To my knowledge, you cannot automate the output channel. You would have to do it manually. 

An easy way to automate this would be to create a scheduled quick report. 

You can create an SQL type quick report using following SQL:

select AUTHORIZE_CODE AS "Coordinator Id", IFSAPP.ORDER_COORDINATOR_API.Get_Name(AUTHORIZE_CODE) AS Name, IFSAPP.ORDER_COORDINATOR_API.Get_Phone(AUTHORIZE_CODE) AS Phone, STATE AS Validity, AUTHORIZE_GROUP AS "Coordinator Group" from IFSAPP.ORDER_COORDINATOR

Make sure to change the App Owner prefix in above query from IFSAPP to the correct App Owner, if your App Owner is not IFSAPP. 

You can find instructions on how to schedule the quick report here: 

https://docs.ifs.com/techdocs/foundation1/040_administration/255_br_and_a/005_adhoc_reporting/030_scheduled_quickreports/default.htm

 

Let’s see if other community members have come up with any other clever solutions to achieve this. 

Charith - https://charith.xyz
ZTC ZTC JGOTA
Rank
Userlevel 6
Badge +14

@Charith Epitawatta 

Thanks for sharing your idea.

I have done the same request from a python code that will connect to oracle and with a Window Task to run the bat file that will make to run the python code and send the email to anybody and send the file to any part in your network.

 

JL
Charith Epitawatta
Rank
Userlevel 7
Badge +31

Hi @ZTC ZTC JGOTA,

Thank you very much for sharing how you solved the problem. That sounds very interesting and seems better suited for this requirement. If you could give some tips about how you wrote the Python script, that would also be helpful for anyone reading the thread. :)  

Charith - https://charith.xyz
ZTC ZTC JGOTA
Rank
Userlevel 6
Badge +14

@Charith Epitawatta 

Sure more than happy to share with you and the community. @Charith Epitawatta  you have extremely helpful with my requests in the past. 

The is running from a server. You will need to have the Oracle home installed to be able to pull the data using the CX_ORACLE library. You will need to install the CX_ORACLE library and the Panda Library for be able to export the data in form of csv or excel.

1- Python code

import cx_Oracle
import datetime
import time
import shutil
import pandas as pd
import os
import io
from datetime import date
import win32com.client
time3=time.strftime("%Y-%m-%d")
connection = cx_Oracle.connect(user="XXXXXXXXXXXXXXXX", password="XXXXXXXXX",dsn="XXXXXXXXXXXX")

print("Database version:",connection.version)
cursor = connection.cursor()


csv_file = open("Inventory1.csv","w",encoding="utf8")

writer = csv.writer(csv_file,delimiter=',',lineterminator="\n", quoting=csv.QUOTE_NONNUMERIC)

dataHere=cursor.execute("""
select CONTRACT,PART_NO,LOCATION_NO,SERIAL_NO,ENG_CHG_LEVEL,ACTIVITY_SEQ,EXPIRATION_DATE,FREEZE_FLAG,LAST_ACTIVITY_DATE,LAST_COUNT_DATE,LOCATION_TYPE,
QTY_ONHAND,QTY_IN_TRANSIT,QTY_RESERVED,RECEIPT_DATE,WAREHOUSE,BAY_NO,ROW_NO,TIER_NO,BIN_NO,CREATE_DATE,PART_OWNERSHIP,UNIT_COST
FROM INVENTORY_PART_IN_STOCK_UIV
where
CONTRACT =: cust and QTY_ONHAND > 0
""", cust ='WWWWW')
writer.writerow(['CONTRACT','PART_NO','LOCATION_NO','SERIAL_NO','ENG_CHG_LEVEL','ACTIVITY_SEQ','EXPIRATION_DATE','FREEZE_FLAG','LAST_ACTIVITY_DATE','LAST_COUNT_DATE','LOCATION_TYPE','QTY_ONHAND','QTY_IN_TRANSIT','QTY_RESERVED','RECEIPT_DATE','WAREHOUSE','BAY_NO','ROW_NO','TIER_NO','BIN_NO','CREATE_DATE','PART_OWNERSHIP','UNIT_COST'])
for row in cursor:
    writer.writerow(row)
cursor.close()
connection.close()
csv_file.close()
source1 = 'C:\\Users\\admin-SSSSS\\Reporting\\Report1\\Inventory1.csv'
destination1= f"C:\\Users\\admin-SSSSS\\Reporting\\Report1\\Inventory.csv"
print('In the process to send the file to the z drive')
shutil.copy(source1,destination1)
source = f'C:\\Users\\admin-SSSSS\\Reporting\\Report1\\Inventory.csv'
destination = '\\\\SSSSS-SSSSS-fs5\\ChaiSSSSSonnement\\SSSSSs\\ArSSSSS\\ArSSSSSes\\'
shutil.move(source,destination)

 

The code above get call by an Window Task that will call a bat file to be able to run python. by the way I am using python 3.7

 

Here is the bat file to be called from the window task

2- Bat file

"C:\Users\admin-SSSSS\AppData\Local\Programs\Python\Python37\python.exe" "C:\Users\SSSSSS\Reporting\Report1\inventory.py"

 

Some of the libraries from the python code are not necessary. I was trying to do something else.

The most important are:

import cx_Oracle
import shutil
import pandas as pd
import os
import io
import win32com.client

Let me know if you have any other questions.

I will be more than happy to help :-)

 

Thanks,

JL

 

 

 


 

JL
Charith Epitawatta
Rank
Userlevel 7
Badge +31

Thanks for sharing the script @ZTC ZTC JGOTA!

Charith - https://charith.xyz

Reply


Terms & ConditionsCookie settings