Hello.
We have a business reporter which is not working and I would need to review the SQL query; what’s the way to get access to that BR? I know it’s name (SFD_BR_QM_001_REP), I thought I could find it via SQL developper, as an view for the IFSINFO user but I can’t find it.
Thanks by advance.
IFS APPS 10 UPD 5
Can’t be too sure as I’m not super familiar with Business Reporter but I’d suggest looking up the LAST_SQL_STATEMENT column in View DSOD_SET_EXEC_PARAM
I found the sql query which is having an issue but I don’t understand the origin of the issue. The error occurs in this sql query
SELECT 'a' FROM ifsapp.NON_CONFORMANCE_REPORT_CFV LEFT JOIN (SELECT ifsapp.SHOP_ORDER_OPERATION_JOIN.WORK_CENTER_NO,ifsapp.NCR_OBJECT_CONNECTION_CFV.NCR_NO, ifsapp.NCR_OBJECT_CONNECTION_CFV.LU_NAME, ifsapp.NCR_OBJECT_CONNECTION_CFV.KEY_REF FROM ifsapp.NCR_OBJECT_CONNECTION_CFV LEFT JOIN ifsapp.SHOP_ORDER_OPERATION_JOIN ON ( ifsapp.SHOP_ORDER_OPERATION_JOIN.ORDER_NO = REPLACE(substr(ifsapp.OBJECT_CONNECTION_SYS.Get_Instance_Description(ifsapp.NCR_OBJECT_CONNECTION_CFV.LU_NAME,NULL,ifsapp.NCR_OBJECT_CONNECTION_CFV.KEY_REF), instr(ifsapp.OBJECT_CONNECTION_SYS.Get_Instance_Description(ifsapp.NCR_OBJECT_CONNECTION_CFV.LU_NAME,NULL,ifsapp.NCR_OBJECT_CONNECTION_CFV.KEY_REF), ':',1,2)+1, instr(ifsapp.OBJECT_CONNECTION_SYS.Get_Instance_Description(ifsapp.NCR_OBJECT_CONNECTION_CFV.LU_NAME,NULL,ifsapp.NCR_OBJECT_CONNECTION_CFV.KEY_REF), ',',1,2)-instr(ifsapp.OBJECT_CONNECTION_SYS.Get_Instance_Description(ifsapp.NCR_OBJECT_CONNECTION_CFV.LU_NAME,NULL,ifsapp.NCR_OBJECT_CONNECTION_CFV.KEY_REF), ':',1,2)-1),' ',NULL) AND ifsapp.SHOP_ORDER_OPERATION_JOIN.OPERATION_NO = REPLACE(substr(ifsapp.OBJECT_CONNECTION_SYS.Get_Instance_Description(ifsapp.NCR_OBJECT_CONNECTION_CFV.LU_NAME,NULL,ifsapp.NCR_OBJECT_CONNECTION_CFV.KEY_REF), instr(ifsapp.OBJECT_CONNECTION_SYS.Get_Instance_Description(ifsapp.NCR_OBJECT_CONNECTION_CFV.LU_NAME,NULL,ifsapp.NCR_OBJECT_CONNECTION_CFV.KEY_REF), ':',1,1)+1, instr(ifsapp.OBJECT_CONNECTION_SYS.Get_Instance_Description(ifsapp.NCR_OBJECT_CONNECTION_CFV.LU_NAME,NULL,ifsapp.NCR_OBJECT_CONNECTION_CFV.KEY_REF), ',',1,1)-instr(ifsapp.OBJECT_CONNECTION_SYS.Get_Instance_Description(ifsapp.NCR_OBJECT_CONNECTION_CFV.LU_NAME,NULL,ifsapp.NCR_OBJECT_CONNECTION_CFV.KEY_REF), ':',1,1)-1),' ',NULL) AND ifsapp.SHOP_ORDER_OPERATION_JOIN.RELEASE_NO = REPLACE(substr(ifsapp.OBJECT_CONNECTION_SYS.Get_Instance_Description(ifsapp.NCR_OBJECT_CONNECTION_CFV.LU_NAME,NULL,ifsapp.NCR_OBJECT_CONNECTION_CFV.KEY_REF), instr(ifsapp.OBJECT_CONNECTION_SYS.Get_Instance_Description(ifsapp.NCR_OBJECT_CONNECTION_CFV.LU_NAME,NULL,ifsapp.NCR_OBJECT_CONNECTION_CFV.KEY_REF), ':',1,3)+1, instr(ifsapp.OBJECT_CONNECTION_SYS.Get_Instance_Description(ifsapp.NCR_OBJECT_CONNECTION_CFV.LU_NAME,NULL,ifsapp.NCR_OBJECT_CONNECTION_CFV.KEY_REF), ',',1,3)-instr(ifsapp.OBJECT_CONNECTION_SYS.Get_Instance_Description(ifsapp.NCR_OBJECT_CONNECTION_CFV.LU_NAME,NULL,ifsapp.NCR_OBJECT_CONNECTION_CFV.KEY_REF), ':',1,3)-1),' ',NULL) AND ifsapp.SHOP_ORDER_OPERATION_JOIN.SEQUENCE_NO = REPLACE(substr(ifsapp.OBJECT_CONNECTION_SYS.Get_Instance_Description(ifsapp.NCR_OBJECT_CONNECTION_CFV.LU_NAME,NULL,ifsapp.NCR_OBJECT_CONNECTION_CFV.KEY_REF), instr(ifsapp.OBJECT_CONNECTION_SYS.Get_Instance_Description(ifsapp.NCR_OBJECT_CONNECTION_CFV.LU_NAME,NULL,ifsapp.NCR_OBJECT_CONNECTION_CFV.KEY_REF), ':',1,4)+1, length(ifsapp.OBJECT_CONNECTION_SYS.Get_Instance_Description(ifsapp.NCR_OBJECT_CONNECTION_CFV.LU_NAME,NULL,ifsapp.NCR_OBJECT_CONNECTION_CFV.KEY_REF))-instr(ifsapp.OBJECT_CONNECTION_SYS.Get_Instance_Description(ifsapp.NCR_OBJECT_CONNECTION_CFV.LU_NAME,NULL,ifsapp.NCR_OBJECT_CONNECTION_CFV.KEY_REF), ':',1,4)),' ',NULL) ) WHERE CF$_SFD_CF_QM_013_DB = 'OCCURENCE') T1 ON (T1.NCR_NO = ifsapp.NON_CONFORMANCE_REPORT_CFV.NCR_NO ) LEFT JOIN (SELECT NCR_NO,LU_NAME,KEY_REF FROM ifsapp.NCR_OBJECT_CONNECTION_CFV WHERE CF$_SFD_CF_QM_013_DB = 'DETECTION') T2 ON (T2.NCR_NO = ifsapp.NON_CONFORMANCE_REPORT_CFV.NCR_NO) LEFT JOIN (SELECT T3.NCR_NO, T3.MRB_NUMBER, ifsapp.INVENTORY_TRANSACTION_HIST2.LOT_BATCH_NO, ifsapp.INVENTORY_TRANSACTION_HIST2.DATE_CREATED FROM ifsapp.INVENTORY_TRANSACTION_HIST2 INNER JOIN (SELECT DISTINCT ifsapp.NCR_OBJECT_CONNECTION_CFV.NCR_NO, ifsapp.MRB_PART_DETAIL.MRB_NUMBER, ifsapp.MRB_PART_DETAIL.LOT_BATCH_NO FROM ifsapp.MRB_PART_DETAIL INNER JOIN ifsapp.NCR_OBJECT_CONNECTION_CFV ON (to_char(ifsapp.MRB_PART_DETAIL.MRB_NUMBER) = ifsapp.CLIENT_SYS.Get_Key_Reference_Value(NCR_OBJECT_CONNECTION_CFV.KEY_REF,'MRB_NUMBER') AND ifsapp.NCR_OBJECT_CONNECTION_CFV.Lu_NAME = 'MrbHead' AND ifsapp.MRB_PART_DETAIL.LOT_BATCH_NO <> '*' AND ifsapp.NCR_OBJECT_CONNECTION_CFV.NCR_NO is not null)) T3 ON ifsapp.INVENTORY_TRANSACTION_HIST2.LOT_BATCH_NO = T3.LOT_BATCH_NO AND ifsapp.INVENTORY_TRANSACTION_HIST2.TRANSACTION_CODE = 'OOREC') T4 ON to_char(T4.NCR_NO) = to_char(ifsapp.NON_CONFORMANCE_REPORT_CFV.NCR_NO) where NON_CONFORMANCE_REPORT_CFV.DATE_CREATED>=to_date('01/06/2025', 'dd/MM/yyyy')
When I remove the last join, the query works, so the issue occurs on here :
LEFT JOIN (SELECT T3.NCR_NO, T3.MRB_NUMBER, ifsapp.INVENTORY_TRANSACTION_HIST2.LOT_BATCH_NO, ifsapp.INVENTORY_TRANSACTION_HIST2.DATE_CREATED FROM ifsapp.INVENTORY_TRANSACTION_HIST2 INNER JOIN (SELECT DISTINCT ifsapp.NCR_OBJECT_CONNECTION_CFV.NCR_NO, ifsapp.MRB_PART_DETAIL.MRB_NUMBER, ifsapp.MRB_PART_DETAIL.LOT_BATCH_NO FROM ifsapp.MRB_PART_DETAIL INNER JOIN ifsapp.NCR_OBJECT_CONNECTION_CFV ON (to_char(ifsapp.MRB_PART_DETAIL.MRB_NUMBER) = ifsapp.CLIENT_SYS.Get_Key_Reference_Value(NCR_OBJECT_CONNECTION_CFV.KEY_REF,'MRB_NUMBER') AND ifsapp.NCR_OBJECT_CONNECTION_CFV.Lu_NAME = 'MrbHead' AND ifsapp.MRB_PART_DETAIL.LOT_BATCH_NO <> '*' AND ifsapp.NCR_OBJECT_CONNECTION_CFV.NCR_NO is not null)) T3 ON ifsapp.INVENTORY_TRANSACTION_HIST2.LOT_BATCH_NO = T3.LOT_BATCH_NO AND ifsapp.INVENTORY_TRANSACTION_HIST2.TRANSACTION_CODE = 'OOREC') T4 ON to_char(T4.NCR_NO) = to_char(ifsapp.NON_CONFORMANCE_REPORT_CFV.NCR_NO)
but strangely, I can run this query with no issue. The error is :
"unable to convert string value containing %s to a number: %s"
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.