Skip to main content
Solved

Is there a Way to Automate an Output Channel?

  • February 7, 2022
  • 5 replies
  • 550 views
Charith Epitawatta
Ultimate Hero (Employee)
Forum|alt.badge.img+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?

Best answer by ZTC ZTC JGOTA

@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

 

 

 


 

View original
Charith - https://charith.xyz
Did this topic help you find an answer to your question?

5 replies

Charith Epitawatta
Ultimate Hero (Employee)
Forum|alt.badge.img+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
Hero (Customer)
Forum|alt.badge.img+14
  • ZTC ZTC JGOTA
  • Hero (Customer)
  • 219 replies
  • February 7, 2022

@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
1 person likes this
  • Charith Epitawatta
    Charith Epitawatta
Charith Epitawatta
Ultimate Hero (Employee)
Forum|alt.badge.img+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
Hero (Customer)
Forum|alt.badge.img+14
  • ZTC ZTC JGOTA
  • Hero (Customer)
  • 219 replies
  • Answer
  • February 7, 2022

@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
1 person likes this
  • Charith Epitawatta
    Charith Epitawatta
Charith Epitawatta
Ultimate Hero (Employee)
Forum|alt.badge.img+31

Thanks for sharing the script @ZTC ZTC JGOTA!

Charith - https://charith.xyz
ZTC ZTC JGOTA
1 person likes this
  • ZTC ZTC JGOTA
    ZTC ZTC JGOTA

Reply


Terms & ConditionsCookie settings

Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings