Skip to main content

Hello.

We made a custom report which uses the UNIFORM_PAYMENT_ADVICE_REP table to make the report, and we have a report rule to send the report to suppliers, but sometimes, the report is well generated, sometimes, the report is empty.

On the report rule screen, we defined the following condition :

select NVL(sum(payment_amount), 0)

from UNIFORM_PAYMENT_ADVICE_REP

where result_key==#ResultKey]  <> 0

 

To generate the report, we have this main sql query :

SELECT

RESULT_KEY,

    SUPPLIER_INFO_API.Get_Name(LEDGER_TRANSACTION_SU_QRY.SUPPLIER_ID) AS SUPPLIER_NAME,

    SUPPLIER_INFO_ADDRESS_API.Get_Address(LEDGER_TRANSACTION_SU_QRY.SUPPLIER_ID, 'ADD_DEL_FR_0001') AS SUPPLIER_ADDRESS,

    COMM_METHOD_API.Get_Value('Supplier', LEDGER_TRANSACTION_SU_QRY.SUPPLIER_ID, 2) AS SUPPLIER_EMAIL,

    UNIFORM_PAYMENT_ADVICE_REP.Payment_Address AS SUPPLIER_IBAN,

    LEDGER_TRANSACTION_SU_QRY.COMPANY,

    LEDGER_TRANSACTION_SU_QRY.PAYMENT_ID,

    LEDGER_TRANSACTION_SU_QRY.LEDGER_ITEM_ID,

    LEDGER_TRANSACTION_SU_QRY.PAID_AMOUNT,

    LEDGER_TRANSACTION_SU_QRY.INVOICE_CURRENCY,

    CASE WHEN LEDGER_TRANSACTION_SU_QRY.USED_DISCOUNT IS NULL THEN 0 ELSE LEDGER_TRANSACTION_SU_QRY.USED_DISCOUNT END AS USED_DISCOUNT,

    MAN_SUPP_INVOICE.DUE_DATE,

    CASE WHEN MAN_SUPP_INVOICE.INV_GROSS_CURR_AMT IS NULL THEN 1 ELSE MAN_SUPP_INVOICE.INV_GROSS_CURR_AMT END AS INV_GROSS_CURR_AMT,

    PAYMENT_PER_CURRENCY_SU_QRY.CURR_AMOUNT,

    PAYMENT_PER_CURRENCY_SU_QRY.CURRENCY

from UNIFORM_PAYMENT_ADVICE_REP

         left join SUPPLIER_INFO_GENERAL On UNIFORM_PAYMENT_ADVICE_REP.PAYEE_IDENTITY  = SUPPLIER_INFO_GENERAL.SUPPLIER_ID  

         left join LEDGER_TRANSACTION_SU_QRY On UNIFORM_PAYMENT_ADVICE_REP.INVOICE_NO = LEDGER_ITEM_SERIES_ID || LEDGER_TRANSACTION_SU_QRY.LEDGER_ITEM_ID AND UNIFORM_PAYMENT_ADVICE_REP.PAYEE_IDENTITY = LEDGER_TRANSACTION_SU_QRY.IDENTITY

         left join MAN_SUPP_INVOICE On LEDGER_ITEM_ID = MAN_SUPP_INVOICE.INVOICE_NO

        left join PAYMENT_PER_CURRENCY_SU_QRY On PAYMENT_PER_CURRENCY_SU_QRY.PAYMENT_ID = LEDGER_TRANSACTION_SU_QRY.PAYMENT_ID AND PAYMENT_PER_CURRENCY_SU_QRY.COMPANY = LEDGER_TRANSACTION_SU_QRY.COMPANY

where RESULT_KEY = the result key value

When the empty report is sent to the supplier, each time I run the sql query with its result key value, the report is complete...

Can you tell me what’s wrong with the report / report rule?

Thanks

IFS APP 10; UPD 5

Hi @romsar ,

Did you check whether there are any errors in the “Report Rule Log” window?

Note: You need to set the "Report Rule Advance Logging" System Parameter to "ON' to enable Report Rule Logging.

Thanks!


hello @Arosha Mudalige 

Unfortunately, the log is empty. Can you tell me how to find this parameter?


Hi @romsar ,

You need to go to the “System Parameters” window and set the "Report Rule Advance Logging" parameter to "ON”. Then recreate the issue and see whether you get any information in the logs

Thanks!


Thanks @Arosha Mudalige  I could enable the parameter.

But I’m wondering if the issue is actually on the oracle side, on some reports, the _REP tables are empty by using the result key generated on IFS but the issue occurs randomly.


@romsar  Further, you can also check in the “Report Archive” window, to check whether the report is generated. In this window, you can right click on the relevant record to get the XML data or the pdf report to see whether those are generated properly at a time of the issue.


@Arosha Mudalige I have different results in between the report archive and the application message screens.

Archice report :

RESULT KEY Printed ? Exists in application message?
297552 no no
297554 no no
297556 no no
297558 yes yes
297559 no no
297561 yes yes
297562 yes yes
297563 no no
297565 no no
297567 yes yes

 

Application message :

RESULT KEY Document complete ?
297553 yes
297555 no, total amount = 0
297557 yes
297558 yes
297560 no, total amount = 0
297561 yes
297562 yes
297564 yes
297566 yes
297567 yes

 

About the XML files, IFS doesn’t let me open the XML on the printed archive document. By opening the 6 xml files, they are not empty.


Hello.

On the report itself, I added a pause to make “sure” that the report wouldn’t be sent “too quickly” but that didn’t fix the generation of empty documents, the pause is for 300 seconds (so 5 minutes).

Any idea? I’m now out of idea of what else I might do


Strange behaviour : in the application message screen, I can find the result_key value 302800


BUT this result_key value does not exist :
 

How c​​​​​​​an that be possible?


In the report rule screen, I added the condition highlighted in green. Is that the way to make sure that the report would be correctly generated?

 


I made a discovery, the UNIFORM_PAYMENT_ADVICE_REP is not correctly filled, there are some suppliers which don’t appear in this view 

 


Reply