Untitled

--CREATE OR REPLACE VIEW EQUIP_OBJECT_MEAS_GROUP AS
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,
               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 m1
        WHERE  (m1.test_pnt_seq, m1.parameter_code,  m1.values_seq)IN (SELECT m2.test_pnt_seq,
                                                                                           m2.parameter_code,
                                                                                           --MAX(m2.reg_date),
                                                                                           MAX(m2.values_seq) KEEP (DENSE_RANK  FIRST ORDER BY m2.reg_date DESC)
                                                                                    FROM   equipment_object_meas_tab m2
                                                                                    --WHERE  m1.test_pnt_seq = m2.test_pnt_seq
                                                                                    --AND    m1.parameter_code = m2.parameter_code
                                                                                    GROUP  BY m2.test_pnt_seq,
                                                                                              m2.parameter_code)) m3
WHERE (EXISTS (SELECT 1 FROM user_allowed_site_pub u1 WHERE (u1.site) IN (SELECT contract FROM equipment_object_tab eob1 WHERE eob1.equipment_object_seq = m3.equipment_object_seq)) OR (m3.equipment_object_seq IS NULL))
UNION ALL
(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 p1
 WHERE  p1.last_value IS NULL
 AND    p1.reg_date IS NULL
 AND    NOT EXISTS (SELECT 1
                    FROM   equipment_object_meas_tab m4
                    WHERE  m4.test_pnt_seq = p1.test_pnt_seq
                    AND    m4.parameter_code = p1.parameter_code)
 AND   (EXISTS (SELECT 1 FROM user_allowed_site_pub u2 WHERE (u2.site) IN (SELECT contract FROM equipment_object_tab eob2 WHERE eob2.equipment_object_seq = p1.equipment_object_seq)) OR (p1.equipment_object_seq IS NULL)))