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

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
;

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 

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.