I have 2 tables TBL_FIBER_INV_JOBS and TBL_FIBER_INV_JOB_PROGRESS.

Here are the table descriptions for both tables:

TBL_FIBER_INV_JOBS (master table):

Name                      Null?    Type           
------------------------- -------- -------------- 
JOB_ID                    NOT NULL NUMBER         
SPAN_ID                            NVARCHAR2(100) 
LINK_ID                            NVARCHAR2(100) 
CREATED_BY                         NVARCHAR2(200) 
CREATED_DATE              NOT NULL DATE           
MAINTENANCEZONECODE                NVARCHAR2(50)  
MAINTENANCEZONENAME                NVARCHAR2(100) 
MAINT_ZONE_NE_SPAN_LENGTH          NUMBER(10,4)   
SPAN_TYPE                          NVARCHAR2(20)  
JOB_FLAG                           NUMBER         
MISSING_ABD_LENGTH                 NUMBER(38,10)  
REOFFERFLAG                        VARCHAR2(10)   

TBL_FIBER_INV_JOB_PROGRESS (child table)

Name                   Null?    Type           
---------------------- -------- -------------- 
JOB_PROGRESS_ID        NOT NULL NUMBER         
JOB_ID                          NUMBER         
STATUS_ID                       NUMBER 
APPROVED_BY                     NVARCHAR2(200) 
APPROVED_DATE                   DATE           
REJECTED_BY                     NVARCHAR2(200) 
REJECTED_DATE                   DATE           
APPROV_REJECT_REMARK            NVARCHAR2(255) 
DELAY_REASON                    NVARCHAR2(255) 
ISABDMISSING                    NUMBER         
HOTO_OFFERED_LENGTH             NUMBER         
LIT_OFFERED_LENGTH              NUMBER         
HOTO_ACTUAL_LENGTH              NUMBER         
LIT_ACTUAL_LENGTH               NUMBER         
ABD_COMPLETED_LENGTH            NUMBER         
NE_SPAN_LENGTH                  NUMBER(10,4)   
CREATED_BY                      NVARCHAR2(200) 
CREATED_DATE           NOT NULL DATE           
MODIFIED_BY                     NVARCHAR2(200) 
MODIFIED_DATE                   DATE           
UMS_GROUP_ASS_BY_ID             NUMBER         
UMS_GROUP_ASS_BY_NAME           NVARCHAR2(200) 
UMS_GROUP_ASS_TO_ID             NUMBER         
UMS_GROUP_ASS_TO_NAME           NVARCHAR2(200) 
UMS_GROUP_ASS_TO_DATE           DATE           
JOB_PROGRESS_FLAG      NOT NULL NUMBER 

The case is there can be multiple JOB_ID in the child table with unique JOB_PROGESS_ID.

What I want is a procedure where I always need to get the max JOB_PROGESS_ID of that JOB_ID with one condition of child table which is:

UMS_GROUP_ASS_BY_NAME = 'CMM' AND
UMS_GROUP_ASS_TO_NAME IS NULL

I want only 3 columns from the master table: JOB_ID, SPAN_ID, LINK_ID

I am unable to fetch the maximum JOB_PROGRESS_ID for the particular JOB_ID. Please suggest

UPDATE

I tried with this query but it does not return what I need:

select max(job_progress_id), hoto_actual_length 
from tbl_fiber_inv_job_progress 
where job_id = 86753
  and ums_group_ass_by_name = 'CMM' 
  and ums_group_ass_to_name is null  
group by hoto_actual_length;


select 
    max(job_id), span_id, job_flag, nvl(missing_abd_length, 0) missing_abd_length, 
    maint_zone_ne_span_length, maintenancezonecode  
from 
    tbl_fiber_inv_jobs 
where 
    job_id = 86753 
    and job_flag = 0 
    and span_type <> 'FTTX' 
group by 
    span_id, job_flag, missing_abd_length, maint_zone_ne_span_length, maintenancezonecode; -- 187375