Solved

Excel Plugin Error: ORA-01841: (full) year must be between -4713 and +9999, and not be 0

  • 15 January 2021
  • 5 replies
  • 606 views

Userlevel 4
Badge +9

Hi all, 

I made a Quick Report, which works fine when I view the report in IFS. But when I try to export the report to Excel, I get an error in Excel: Excel Plugin Error: ORA-01841: (full) year must be between -4713 and +9999, and not be 0. 

Does anyone know how to solve this issue? Below my SQL Statement. 

 

SELECT 
             order_no "Order No", 
             Purchase_Order_API.Get_State(ORDER_NO) "PO Status",
             State "Line status",
             line_no "Line No",
             release_no "Release No",
             part_no "Part No",
             description "Description",
             buy_qty_due "Qty",
             buy_unit_meas "UoM",
             buy_unit_price "Price per unit",
             Purchase_Order_Line_API.Get_Total_In_Order_Curr(Order_No,Line_No,Release_No) "Total amount", 
             CF$_COORDINATOR_NAME "Name", 
             project_id "Project ID",
             Project_API.Get_Name(PROJECT_ID) "Project Name",
             date_entered "Date entered"
FROM purchase_order_line_all_cfv
WHERE OBJSTATE <> (select PURCHASE_ORDER_LINE_API.FINITE_STATE_ENCODE__('Cancelled') from dual) 
AND date_entered BETWEEN trunc(to_date( '#START_OF_LAST_WEEK#', 'YYYY-MM-DD-HH24:MI:SS' )) and trunc(to_date( '#END_OF_LAST_WEEK#', 'YYYY-MM-DD-HH24:MI:SS' )) + ( 1 - 1/ ( 60*60*24 ) )
ORDER BY order_no

icon

Best answer by dhlelk 18 January 2021, 09:05

View original

This topic has been closed for comments

5 replies

Userlevel 6
Badge +15

Hi @BZNSIMON,

The reason is the usage of Client Implementation Type Context Substitution Variables in the server.

When you run the above quick report in IFS it works fine as a client task since the framework substitutes the actual date instead instead of #START_OF_LAST_WEEK# which is a Client Implementation Type Context Substitution Variable.
But, when you try to export the report to Excel it becomes a server task, and the framework is unable to resolve a value for #START_OF_LAST_WEEK# which is a Client Implementation Type Context Substitution Variable.

Navigate to "Context Substitution Variables" window (Solution Manager\User Interface\Context Substitution Variables) to find more about the Context Substitution Variables and their Implementation Types.

You may use Context_Substitution_Var_API.Get_Client_Value__('START_OF_LAST_WEEK')
instead of '#START_OF_LAST_WEEK#' in your SQL Statement.

Cheers !
Dhananjaya.
 

SELECT order_no "Order No",
Purchase_Order_API.Get_State(order_no) "PO Status",
state "Line status",
line_no "Line No",
release_no "Release No",
part_no "Part No",
description "Description",
buy_qty_due "Qty",
buy_unit_meas "UoM",
buy_unit_price "Price per unit",
Purchase_Order_Line_API.Get_Total_In_Order_Curr(order_no, line_no, release_no) "Total amount",
cf$_coordinator_name "Name",
project_id "Project ID",
Project_API.Get_Name(project_id) "Project Name",
date_entered "Date entered"
FROM purchase_order_line_all_cfv
WHERE objstate <> (SELECT PURCHASE_ORDER_LINE_API.FINITE_STATE_ENCODE__('Cancelled')
FROM dual)
AND date_entered BETWEEN TRUNC(TO_DATE(Context_Substitution_Var_API.Get_Client_Value__('START_OF_LAST_WEEK'), 'YYYY-MM-DD-HH24:MI:SS'))
AND TRUNC(TO_DATE(Context_Substitution_Var_API.Get_Client_Value__('END_OF_LAST_WEEK'), 'YYYY-MM-DD-HH24:MI:SS')) + (1 - 1 / (60 * 60 * 24))
ORDER BY order_no
;

 

 

Userlevel 4
Badge +9

Hi @dhlelk , 

 

Thank you for your help. But now I got another issue; when I export the report to Excel, it results in an empty Excel sheet with the message ‘No Data Found!!’. When I change the report back to the original SQL Statement and view it in IFS, I see that there is data present. So there is still something wrong with the SQL Statement. Do you know what it is? 

 

Kind regards, 

Simon 

Userlevel 6
Badge +15

Hi @dhlelk , 

 

Thank you for your help. But now I got another issue; when I export the report to Excel, it results in an empty Excel sheet with the message ‘No Data Found!!’. When I change the report back to the original SQL Statement and view it in IFS, I see that there is data present. So there is still something wrong with the SQL Statement. Do you know what it is? 

 

Kind regards, 

Simon 

Hi @BZNSIMON,

The function Context_Substitution_Var_API.Get_Client_Value__ returns a DATE type value,
Hence try removing the TO_DATE function on your where clause.

SELECT order_no "Order No",
Purchase_Order_API.Get_State(order_no) "PO Status",
state "Line status",
line_no "Line No",
release_no "Release No",
part_no "Part No",
description "Description",
buy_qty_due "Qty",
buy_unit_meas "UoM",
buy_unit_price "Price per unit",
Purchase_Order_Line_API.Get_Total_In_Order_Curr(order_no, line_no, release_no) "Total amount",
cf$_coordinator_name "Name",
project_id "Project ID",
Project_API.Get_Name(project_id) "Project Name",
date_entered "Date entered"
FROM purchase_order_line_all_cfv
WHERE objstate <> (SELECT PURCHASE_ORDER_LINE_API.FINITE_STATE_ENCODE__('Cancelled')
FROM dual)
AND date_entered BETWEEN TRUNC(Context_Substitution_Var_API.Get_Client_Value__('START_OF_LAST_WEEK'))
AND TRUNC(Context_Substitution_Var_API.Get_Client_Value__('END_OF_LAST_WEEK')) + (1 - 1 / (60 * 60 * 24))
ORDER BY order_no
;

Cheers !
Dhananjaya.

Userlevel 4
Badge +9

Thank you @dhlelk ! Your solution worked and returned the correct data. 

 

Thanks for helping out!  

 

Userlevel 6
Badge +15

Thank you @dhlelk ! Your solution worked and returned the correct data. 

 

Thanks for helping out!  

 

Hi @BZNSIMON,

You are welcome and I'm happy to help 😊

Cheers !
Dhananjaya.