Oracle EBS: "You cannot reverse frozen subledger journal sources" error

問題: 在GL要作reverse時, 出現"You cannot reverse frozen subledger journal sources"訊息

原因: The journal source for the subledger journal is frozen.

解法: To reverse the journal you need to unfreeze the subledger journal source:
1. Navigate to: Setup>Journal>Sources
2. Query for the source of your journal
3. Select the pretended option from the 'Freeze journals' list of values:

• Yes: Journals from this source cannot be changed in the Enter Journals form nor the Journal Correction form. In addition, you cannot reverse journals with Subledger Accounting journal sources that are frozen.   

• No: Journals from this source can be changed in both the Enter Journals form and the Journal Correction form. In addition, you can reverse journals with unfrozen Subledger Accounting journal sources.  

• Journal Entry Page Only: Journals from this source cannot be changed in the Enter Journals form but can be changed in the Journal Correction form. In addition, you cannot reverse journals with Subledger Accounting journal sources that have this option.

NOTE: It is strongly advised that you freeze the subledger source back after you reverse the journal; unfreezing subledger journal sources will allow users to reverse subledger sourced journals at will, causing reconciliation problems between General Ledger and subledgers.


Ref:
1.GLXJEENT: Journal Reversal Throws Error: You Cannot Reverse Frozen Subledger Journal Sources (Doc ID 454472.1)

Python: pywin32 安裝與使用教學，Python 的 Windows 擴充功能套件

pywin32 安裝與使用教學，Python 的 Windows 擴充功能套件

https://officeguide.cc/python-windows-extensions-pywin32-installation/

Python: 以openpyxl寫入大量資料至xlsx, 出現MemoryError

問題: 以openpyxl寫入大量資料至xlsx, 出現MemoryError, 但若筆數少就正常

原因: 原先只指定 wb=workbook() , 找到資料是要加註 write_only=True , 改為

  wb-workbook(write_only=True)

但仍有問題, 出現:

  I/O operation on closed file error

解法: 要再安裝lxml

Here again, the regular openpyxl.worksheet.worksheet.Worksheet has been replaced by a faster alternative, the openpyxl.writer.write_only.WriteOnlyWorksheet. When you want to dump large amounts of data make sure you have lxml installed.

果然, 裝好就能正常運作了.


Ref:
1.Write-only mode
https://openpyxl.readthedocs.io/en/default/optimized.html#write-only-mode

2.openpyxl - A Python library to read/write Excel 2010 xlsx/xlsm files
https://openpyxl.readthedocs.io/en/default/index.html


Error log:
Traceback (most recent call last):
  File "C:\Users\UT05\AppData\Local\Programs\Python\Python38-32\lib\xml\etree\ElementTree.py", line 832, in _get_writer
    yield file.write
  File "C:\Users\UT05\AppData\Local\Programs\Python\Python38-32\lib\xml\etree\ElementTree.py", line 772, in write
    serialize(write, self._root, qnames, namespaces,
  File "C:\Users\UT05\AppData\Local\Programs\Python\Python38-32\lib\xml\etree\ElementTree.py", line 937, in _serialize_xml
    _serialize_xml(write, e, qnames, None,
  File "C:\Users\UT05\AppData\Local\Programs\Python\Python38-32\lib\xml\etree\ElementTree.py", line 937, in _serialize_xml
    _serialize_xml(write, e, qnames, None,
  File "C:\Users\UT05\AppData\Local\Programs\Python\Python38-32\lib\xml\etree\ElementTree.py", line 937, in _serialize_xml
    _serialize_xml(write, e, qnames, None,
  File "C:\Users\UT05\AppData\Local\Programs\Python\Python38-32\lib\xml\etree\ElementTree.py", line 939, in _serialize_xml
    write("</" + tag + ">")
MemoryError
During handling of the above exception, another exception occurred:
Traceback (most recent call last):
  File "C:\temp\dl_rawdata_ebs.py", line 81, in <module>
    wb.save('c:\\shortage\\All.xlsx')
  File "C:\Users\UT05\AppData\Local\Programs\Python\Python38-32\lib\site-packages\openpyxl\workbook\workbook.py", line 392, in save
    save_workbook(self, filename)
  File "C:\Users\UT05\AppData\Local\Programs\Python\Python38-32\lib\site-packages\openpyxl\writer\excel.py", line 293, in save_workbook
    writer.save()
  File "C:\Users\UT05\AppData\Local\Programs\Python\Python38-32\lib\site-packages\openpyxl\writer\excel.py", line 275, in save
    self.write_data()
  File "C:\Users\UT05\AppData\Local\Programs\Python\Python38-32\lib\site-packages\openpyxl\writer\excel.py", line 75, in write_data
    self._write_worksheets()
  File "C:\Users\UT05\AppData\Local\Programs\Python\Python38-32\lib\site-packages\openpyxl\writer\excel.py", line 215, in _write_worksheets
    self.write_worksheet(ws)
  File "C:\Users\UT05\AppData\Local\Programs\Python\Python38-32\lib\site-packages\openpyxl\writer\excel.py", line 200, in write_worksheet
    writer.write()
  File "C:\Users\UT05\AppData\Local\Programs\Python\Python38-32\lib\site-packages\openpyxl\worksheet\_writer.py", line 360, in write
    self.close()
  File "C:\Users\UT05\AppData\Local\Programs\Python\Python38-32\lib\site-packages\openpyxl\worksheet\_writer.py", line 368, in close
    self.xf.close()
  File "C:\Users\UT05\AppData\Local\Programs\Python\Python38-32\lib\site-packages\openpyxl\worksheet\_writer.py", line 299, in get_stream
    pass
  File "C:\Users\UT05\AppData\Local\Programs\Python\Python38-32\lib\contextlib.py", line 120, in __exit__
    next(self.gen)
  File "C:\Users\UT05\AppData\Local\Programs\Python\Python38-32\lib\site-packages\et_xmlfile\xmlfile.py", line 50, in element
    self._write_element(el)
  File "C:\Users\UT05\AppData\Local\Programs\Python\Python38-32\lib\site-packages\et_xmlfile\xmlfile.py", line 77, in _write_element
    xml = tostring(element)
  File "C:\Users\UT05\AppData\Local\Programs\Python\Python38-32\lib\xml\etree\ElementTree.py", line 1133, in tostring
    ElementTree(element).write(stream, encoding,
  File "C:\Users\UT05\AppData\Local\Programs\Python\Python38-32\lib\xml\etree\ElementTree.py", line 772, in write
    serialize(write, self._root, qnames, namespaces,
  File "C:\Users\UT05\AppData\Local\Programs\Python\Python38-32\lib\contextlib.py", line 131, in __exit__
    self.gen.throw(type, value, traceback)
  File "C:\Users\UT05\AppData\Local\Programs\Python\Python38-32\lib\xml\etree\ElementTree.py", line 832, in _get_writer
    yield file.write
  File "C:\Users\UT05\AppData\Local\Programs\Python\Python38-32\lib\contextlib.py", line 525, in __exit__
    raise exc_details[1]
  File "C:\Users\UT05\AppData\Local\Programs\Python\Python38-32\lib\contextlib.py", line 510, in __exit__
    if cb(*exc_details):
  File "C:\Users\UT05\AppData\Local\Programs\Python\Python38-32\lib\contextlib.py", line 382, in _exit_wrapper
    callback(*args, **kwds)
ValueError: I/O operation on closed file.

Python: 以cx_Oracle取Oracle DB資料時, 出現illegal multibyte sequence error

問題: Python以cx_Oracle取Oracle DB資料時, 出現illegal multibyte sequence error

原因: 預設使用Big5, 但DB中有簡體字, 無法轉換

解法: 加註encoding = 'UTF-8'


conn = cx_Oracle.connect(user='u',password ='pw', dsn=dsn_tns, encoding = 'UTF-8')


Ref:
1.How to handle unicode data in cx_Oracle and python 2.7?
https://stackoverflow.com/questions/49174710/how-to-handle-unicode-data-in-cx-oracle-and-python-2-7

2.Random character is displayed when unicode data is fetched
https://github.com/oracle/python-cx_Oracle/issues/157

Python: 把CSV轉為XLSB檔

要先安裝pywin32, 實測也可以把xlsx轉為xlsb.

 

sample code: 

import win32com.client

excel = win32com.client.Dispatch("Excel.Application")

doc = excel.Workbooks.Open('D:\\input.csv')

doc.SaveAs( 'D:\\output_bin.xlsb', 50 )

 

Ref:

1.How do I convert a csv file to xlsb using Python?
https://stackoverflow.com/questions/24159542/how-do-i-convert-a-csv-file-to-xlsb-using-python

 

2.pywin32 安裝與使用教學，Python 的 Windows 擴充功能套件
https://officeguide.cc/python-windows-extensions-pywin32-installation/