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;

Thanks Gumab,

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

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)

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

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.

@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.

Hi,

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

1. “External Supplier Invoice Load Information” 

2. “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

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.