Quick Report not showing data to a user

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? 

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

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.