Untitled

SELECT values_seq                                    values_seq,
       test_pnt_seq                                  test_pnt_seq,
       equipment_object_seq                          equipment_object_seq,
       Equipment_Object_API.Get_Contract(equipment_object_seq) contract,       
       NVL(Equipment_Object_API.Get_Mch_Code(equipment_object_seq), Resource_Util_API.Get_Resource_Id(resource_seq)) mch_code,
       test_point_id                                 test_point_id,
       parameter_code                                parameter_code,
       test_sequence_id                              test_sequence_id,
       measured_value                                measured_value,
       reg_date                                      reg_date,
       remark                                        remark,
       Measurement_Type_API.Decode(measurement_type) measurement_type,
       measurement_type                              measurement_type_db,
       measurement_note                              measurement_note,
       recorded_value                                recorded_value,
       meter_id                                      meter_id,
       resource_seq                                  resource_seq,
       TO_NUMBER(NULL)                                          new_condition_rating,
       TO_NUMBER(NULL)                                          new_recorded_value,
       TO_DATE(NULL)                                          new_reg_date,       
       rowkey                                        objkey,
       to_char(rowversion)                           objversion,
       rowid                                         objid
FROM ( SELECT values_seq,
       test_pnt_seq,
       equipment_object_seq,
       Equipment_Object_API.Get_Contract(equipment_object_seq) contract,       
       NVL(Equipment_Object_API.Get_Mch_Code(equipment_object_seq), Resource_Util_API.Get_Resource_Id(resource_seq)) mch_code,                  
       test_point_id,               
       parameter_code,         
       test_sequence_id,         
       measured_value,          
       reg_date,                     
       remark,                       
       measurement_type,
       measurement_note, 
       recorded_value,
       meter_id,
       resource_seq,
       rowkey,
       rowversion,
       rowid        
    
       FROM EQUIPMENT_OBJECT_MEAS_TAB t1
        WHERE (test_pnt_seq, parameter_code, reg_date, values_seq) IN 
        ( SELECT t2.test_pnt_seq,
               t2.parameter_code,
               MAX(t2.reg_date),
               MAX(t2.values_seq)
        FROM EQUIPMENT_OBJECT_MEAS_TAB t2
        WHERE t1.test_pnt_seq = t2.test_pnt_seq
          AND t1.parameter_code = t2.parameter_code
        GROUP BY test_pnt_seq,
                 parameter_code
    ) )    p            
WHERE( EXISTS (SELECT 1 FROM user_allowed_site_pub g WHERE (g.site) IN (SELECT contract from equipment_object_tab z  where z.equipment_object_seq = p.equipment_object_seq)) or (equipment_object_seq IS NULL) ) 
--(1 = User_Allowed_Site_API.Is_Authorized(Equipment_Object_API.Get_Contract(equipment_object_seq)) or equipment_object_seq IS NULL)
UNION
(SELECT NULL,
       test_pnt_seq                      test_pnt_seq,
       equipment_object_seq              equipment_object_seq,
       Equipment_Object_API.Get_Contract(equipment_object_seq)  contract,       
       NVL(Equipment_Object_API.Get_Mch_Code(equipment_object_seq), Resource_Util_API.Get_Resource_Id(resource_seq))  mch_code,
       test_point_id                     test_point_id,
       parameter_code                    parameter_code,
       NULL,
       NULL,
       NULL,
       NULL,
       NULL,
       NULL,
       NULL,
       NULL,       
       NULL,
       resource_seq                      resource_seq,
       NULL,
       NULL,
       NULL,      
       NULL,
       NULL,
       NULL
FROM   equipment_object_param_tab V
WHERE    
last_value IS NULL
AND    reg_date IS NULL
AND NOT EXISTS(SELECT 1 FROM EQUIPMENT_OBJECT_MEAS_TAB X WHERE X.TEST_PNT_SEQ = V.TEST_PNT_SEQ AND X.PARAMETER_CODE = V.PARAMETER_CODE)
 AND ( EXISTS (SELECT 1 FROM user_allowed_site_pub g WHERE (g.site) IN (SELECT contract from equipment_object_tab z  where z.equipment_object_seq = V.equipment_object_seq)) or (equipment_object_seq IS NULL) ) 


);