Question

Quick Report not showing data to a user

  • 15 September 2020
  • 3 replies
  • 36 views

Userlevel 2
Badge +6

Hi

I have created a Quick Report(SQL Statement Type) and granted access to a user permission set

When user goes to “View Report” then is getting “No data found” Information Message

 

 

any ideas why user is not getting data?

 

Thanks,

Narsi.


3 replies

Userlevel 6
Badge +11

A view in your query might be checking and filtering rows based on the logged in user. i.e. show data rows only for the user’s company, etc.

 

what is the query? 

Userlevel 2
Badge +6

@Rusiru

 

Query:

select s.EMPLOYEE_ID, COMPANY_EMP_API.Get_Name(s.company,s.EMPLOYEE_ID) AS Name,
s.DURATION*60 AS "Actual(Minutes)", 
CASE 
    WHEN (SELECT SUM(DURATION) FROM SHOP_FLOOR_CLOCKINGS_UIV WHERE ORDER_NO = s.ORDER_NO AND RELEASE_NO = s.RELEASE_NO AND SEQUENCE_NO = s.SEQUENCE_NO AND OPERATION_NO = s.OPERATION_NO) = 0 
        THEN 0
    ELSE 
    ROUND((s.DURATION/(SELECT SUM(DURATION) FROM SHOP_FLOOR_CLOCKINGS_UIV WHERE ORDER_NO = s.ORDER_NO AND RELEASE_NO = s.RELEASE_NO AND SEQUENCE_NO = s.SEQUENCE_NO AND OPERATION_NO = s.OPERATION_NO))*o.PLAN_LABOR_RUN_TIME*60, 0)
END AS "Expected(Munites)",
CASE 
    WHEN (SELECT SUM(DURATION) FROM SHOP_FLOOR_CLOCKINGS_UIV WHERE ORDER_NO = s.ORDER_NO AND RELEASE_NO = s.RELEASE_NO AND SEQUENCE_NO = s.SEQUENCE_NO AND OPERATION_NO = s.OPERATION_NO) = 0
    THEN 0.00
WHEN o.PLAN_QTY = 0 then 0.00
    ELSE ROUND((o.REPORTED_QTY/o.PLAN_QTY)*(o.PLAN_LABOR_RUN_TIME/(SELECT SUM(DURATION) FROM SHOP_FLOOR_CLOCKINGS_UIV WHERE ORDER_NO = s.ORDER_NO AND RELEASE_NO = s.RELEASE_NO AND SEQUENCE_NO = s.SEQUENCE_NO AND OPERATION_NO = s.OPERATION_NO))*100,2) 
END AS "Efficiency(%)",
o.ORDER_NO, o.RELEASE_NO, o.SEQUENCE_NO, o.OPERATION_NO, o.OPERATION_DESC, Oper_Status_Code_API.Decode(SHOP_ORDER_OPERATION_API.Get_Oper_Status_Code_Db(o.order_no,o.release_no,o.sequence_no,o.OPERATION_NO)) AS Status,
Shop_Order_Operation_API.Get_Op_Sequence_No(o.ORDER_NO, o.RELEASE_NO, o.SEQUENCE_NO, o.OPERATION_NO) AS "Operation Sequence", o.PART_NO,
INVENTORY_PART_API.Get_Description(o.Contract,o.PART_NO) AS Description, INVENTORY_PART_API.Get_Part_Product_Code(o.Contract,o.PART_NO) AS Product,
INVENTORY_PART_API.Get_Part_Product_Family(o.Contract,o.PART_NO) AS "Product Family", o.WORK_CENTER_NO, o.WORK_CENTER_DESC, o.CONTRACT, o.LABOR_CLASS_NO, o.LABOR_CLASS_DESC, o.REAL_FINISHED, o.PLAN_QTY, o.REPORTED_QTY, s.CLOCKING_TYPE, s.TIME_TYPE, s.CREW_SIZE
from OPERATION_STATISTIC o LEFT JOIN SHOP_FLOOR_CLOCKINGS_UIV s ON o.ORDER_NO = s.ORDER_NO AND o.RELEASE_NO = s.RELEASE_NO AND o.SEQUENCE_NO = s.SEQUENCE_NO AND o.OPERATION_NO = s.OPERATION_NO WHERE o.CONTRACT = '101' AND Oper_Status_Code_API.Decode(SHOP_ORDER_OPERATION_API.Get_Oper_Status_Code_Db(o.order_no,o.release_no,o.sequence_no,o.OPERATION_NO)) = 'Closed' AND s.CLOCKING_TYPE = 'Labour' AND s.TIME_TYPE = 'Run Time' AND o.LABOR_CLASS_NO = '&OPERATION_STATISTIC.LABOR_CLASS_NO' 

 

 

Userlevel 6
Badge +11

Hi Narsi,

 

its very long query so not easy to check each db object. But for example following db objects have row based security - filters rows returned based on logged in user. 

 

OPERATION_STATISTIC (notice the filter condition inside this view - “WHERE  EXISTS (SELECT 1 FROM user_allowed_site_pub WHERE operation_statistic_tab.contract = site)) t0”). Inside this user_allowed_site_pub it has the condition - WHERE  userid = (SELECT fnd_user FROM fnd_session)

 

so you need to make sure your user is connected to a valid employee for him to see rows. 

 

There can be other db views/objects that have different filtering withing them - they all need to be checked if the above doesn’t work alone. 

Reply