Solved

External supplier invoices period end warning

  • 14 November 2023
  • 8 replies
  • 120 views
JJäthing
Rank
Userlevel 4
Badge +9

When closing an accounting period we receive the Validation Transactions warning that there are External Supplier Invoices waiting to be created. But after thorough investigations we have come to the conclusion that all external incoming invoice messages are in the status Created, and so are all invoices in the External Supplier Invoices window. There are no failed external files.

For the previous period there was no such warning message and if we close the following period there is no warning message either.

We can only draw the conclusion that the warning message is wrong. But what triggers it?

Any ideas?

We are in the process of upgrading to IFS APPS 10 UPD 17. The issue is in our database for verifying the upgrade.

icon

Best answer by ArcStephA 14 November 2023, 19:02

View original

8 replies

F
Rank
Userlevel 6
Badge +19

Hello @JJäthing 

The function controlling that checkbox is EXT_INC_INV_LOAD_INFO_API. According to sql code if the status of load files on External Supplier Invoice Load Information window Created (db status is 5) within period date range then the checkbox must be FALSE.

 

Can you verify these conditions using SQL developer or using SQL Query Tool on IFS Apps?

 

Hope this helps 

 

FUNCTION Check_Non_Created_Inv_Exist (
   company_           IN VARCHAR2,
   accounting_year_   IN NUMBER,
   accounting_period_ IN NUMBER,
   start_date_        IN DATE,
   end_date_          IN DATE ) RETURN VARCHAR2
IS

   FUNCTION Core (
      company_           IN VARCHAR2,
      accounting_year_   IN NUMBER,
      accounting_period_ IN NUMBER,
      start_date_        IN DATE,
      end_date_          IN DATE ) RETURN VARCHAR2
   IS
      CURSOR check_ext_inv IS
         SELECT 1 
         FROM   ext_inc_inv_load_info_tab el, ext_inc_inv_head_tab eh
         WHERE  el.company          = eh.company
         AND    el.load_id          = eh.load_id
         AND    el.company          = company_
         AND    el.ext_inc_inv_load_state != '5'  -- Created
         AND    eh.transaction_date BETWEEN start_date_ AND end_date_;
      dummy_                 NUMBER;
   BEGIN
      OPEN  check_ext_inv;
      FETCH check_ext_inv INTO dummy_;
      IF (check_ext_inv%FOUND) THEN
         CLOSE check_ext_inv;
         RETURN 'TRUE';
      END IF;
      CLOSE check_ext_inv;
      RETURN 'FALSE';
   END Core;

BEGIN
   RETURN Core(company_, accounting_year_, accounting_period_, start_date_, end_date_);
END Check_Non_Created_Inv_Exist;

 

https://superp.solutions/
JJäthing
Rank
Userlevel 4
Badge +9

Thanks Gumab,

 

I tried to enter the code you gave me but I got an error:
 

 

JJäthing
Rank
Userlevel 4
Badge +9

Server error messages:
3d68e2be-5349-415f-9c57-613c129152a7

Ifs.Fnd.FndSystemException: Unexpected error while calling server method AccessPlsql/Invoke ---> Ifs.Fnd.FndServerFaultException: ORA-06550: line 1, column 16:
PLS-00103: Encountered the symbol "CHECK_NON_CREATED_INV_EXIST" when expecting one of the following:

   := . ( @ % ;
The symbol ":=" was substituted for "CHECK_NON_CREATED_INV_EXIST" to continue.

Failed executing statement (ORA-06550: line 1, column 16:
PLS-00103: Encountered the symbol "CHECK_NON_CREATED_INV_EXIST" when expecting one of the following:

   := . ( @ % ;
The symbol ":=" was substituted for "CHECK_NON_CREATED_INV_EXIST" to continue.
ORA-06550: line 2, column 26:
PLS-00103: Encountered the symbol "VARCHAR2" when expecting one of the following:

   (
The symbol "(" was substituted for "VARCHAR2" to continue.
ORA-06550: line 3, column 26:
PLS-00103: Encountered the symbol "NUMBER" when expecting one of the following:

   (
The symbol "(" was substituted for "NUMBER" to continue.
ORA-06550: line 4, column 26:
PLS-00103: Encountered the symbol "NUMBER" when expecting one of the following:

   (
The symbol "(" was substituted for "NUMBER" to continue.
ORA-06550: line 5, column 26:
PLS-00103: Encountered the symbol "DATE" when expecting one of the following:

   (
The symbol "(" was substituted for "DATE" to continue.
ORA-06550: line 6, column 26:
PLS-00103: Encountered the symbol "DATE" when expecting one of the following:

   ()
   at Ifs.Fnd.AccessProvider.FndConnection.ParseErrorHeader(FndBuffer buffer, FndManualDecisionCollection decisions)
   at Ifs.Fnd.AccessProvider.FndConnection.UnMarshalResponseHeader(Stream responseStream, FndManualDecisionCollection decisions)
   at Ifs.Fnd.AccessProvider.FndConnection.HandleHttpSuccessResult(HttpWebResponse result, FndManualDecisionCollection decisions, String operation, FndBodyType responseBodyType, Object responseBody)
   at Ifs.Fnd.AccessProvider.FndConnection.InvokeInternal(Object requestBody, Object responseBody, String intface, String operation, FndRequestContext requestContext, FndManualDecisionCollection decisions, Boolean forcedSync, Boolean integrationGateway)
   --- End of inner exception stack trace ---
   at Ifs.Fnd.AccessProvider.FndConnection.InvokeInternal(Object requestBody, Object responseBody, String intface, String operation, FndRequestContext requestContext, FndManualDecisionCollection decisions, Boolean forcedSync, Boolean integrationGateway)
   at Ifs.Fnd.AccessProvider.FndConnection.InvokeInternal(String intface, String operation, Object requestBody, Object responseBody, FndRequestContext requestContext, Boolean forcedSync, Boolean integrationGateway)
   at Ifs.Fnd.AccessProvider.PLSQL.FndPLSQLCommandCollection.Invoke()
   at Ifs.Fnd.AccessProvider.PLSQL.FndPLSQLCommand.ExecuteNonQuery()
   at Ifs.Fnd.AccessProvider.PLSQL.FndPLSQLSelectCommandReader.Read(Boolean prepare)
   at Ifs.Fnd.Data.ADONetProvider.FndAPCommand.ExecuteDbDataReader(CommandBehavior behavior)

 

F
Rank
Userlevel 6
Badge +19

Hello

Try this please.  Enter the company (in capital letters) of yours and see if there is any result.

SELECT el.*
         FROM   ext_inc_inv_load_info_tab el, ext_inc_inv_head_tab eh
         WHERE  el.company          = eh.company
         AND    el.load_id          = eh.load_id
         AND    el.company          = '&COMPANY'
         AND    el.ext_inc_inv_load_state != '5'  -- Created

 

https://superp.solutions/
F
Rank
Userlevel 6
Badge +19

Thanks Gumab,

 

I tried to enter the code you gave me but I got an error:
 

 

 

You can use the code in 2 ways.  First method is like below, you must enter parameters correctly.

 

BEGIN

Ext_Inc_Inv_Load_Info_API.Check_Non_Created_Inv_Exist(company_,accounting_year_, accounting_period_,start_date_, end_date_);

END;

 

If this is difficult to use for you then use the one I shared above.

https://superp.solutions/
JJäthing
Rank
Userlevel 4
Badge +9

@gumabs: My knowledge of SQL is not up to your standard, I don’t know how to fix the issue.

The code you copied above seems to be lacking something but I can’t identify what.

A
Rank
Badge +2

Hi,

 

The code above is really just checking in two places for non created invoices:

 

1. “External Supplier Invoice Load Information” 

  1. “External Supplier Invoices”

If both those windows don’t contain any non created invoices (within the date range of the period you are closing) you should be all clear.

Bear in mind that the external file templates allow for variation in voucher date so it could be that you have loaded invoices that have a transaction date in a different period but the voucher date in the period you are trying to close, causing the issue.

 

Steve

JJäthing
Rank
Userlevel 4
Badge +9

Thank you gumabs and ArcStephA both!

I simplified the code to this:

Select * FROM   ext_inc_inv_load_info_tab el, ext_inc_inv_head_tab eh
         WHERE   el.company          = eh.company
         AND    el.load_id          = eh.load_id
         AND    el.company          ='10'
         AND    el.ext_inc_inv_load_state != '5'  -- Created
         AND    eh.transaction_date BETWEEN to_date('2023-09-01','YYYY-MM-DD') AND to_date('2023-10-01','YYYY-MM-DD')

And got one line returned and that with the load date September 28 2009. Someone seems to have entered the erroneous transaction date and invoice date both to September 9 2023.

It didn’t occur to me to look for the error in a transaction loaded in the year 2009.

Reply


Terms & ConditionsCookie settings