get maximum progressid of child table for jobid from master table Oracle procedure

I have 2 tables named as TBL_FIBER_INV_JOBS and TBL_FIBER_INV_JOB_PROGRESS. Below is the table desciption of both the table

TBL_FIBER_INV_JOBS (Master Table) & TBL_FIBER_INV_JOB_PROGRESS (Child Table)

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

So the case is there can be multiple JOB_ID in child table with unique JOB_PROGESS_ID. Now what I want is

A procedure where I always want the max JOB_PROGESS_ID of that JOB_ID with one condition of child table which is

Condition of child table:- UMS_GROUP_ASS_BY_NAME = 'CMM' and UMS_GROUP_ASS_TO_NAME IS NULL. I want only 3 columns from Master table which is JOB_ID, SPAN_ID, LINK_ID

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

UPDATE

I tried with below query but it is not accurate to which I want.

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

Solution

Is it not join (along with aggregation and some filters)?

  SELECT j.job_id,
         j.span_id,
         j.link_id,
         MAX (p.job_progress_id) job_progress_id
    FROM tbl_fiber_inv_jobs j JOIN tbl_fiber_inv_job_progress p ON p.job_id = j.job_id
   WHERE     p.ums_group_ass_by_name = 'CMM'
         AND p.ums_group_ass_to_name IS NULL
GROUP BY j.job_id, j.span_id, j.link_id;

Or, perhaps using a CTE?

WITH
   temp
   AS
      -- get MAX job_progress_id for every job_id (filter applied)
      (  SELECT MAX (p.job_progress_id) job_progress_id, p.job_id
           FROM tbl_fiber_inv_job_progress p
          WHERE     p.ums_group_ass_by_name = 'CMM'
                AND p.ums_group_ass_to_name IS NULL
       GROUP BY p.job_id)
-- get columns from the master table joined with the temp CTE on a common column (job_id)
SELECT j.job_id,
       j.span_id,
       j.link_id,
       t.job_progress_id
  FROM tbl_fiber_inv_jobs j JOIN temp t ON t.job_id = j.job_id;

[EDIT] You commented that you need data from yet another (history) table. This is how I understood it:

WITH
   temp
   AS
      -- get MAX job_progress_id for every job_id (filter applied).
      -- for ne_span_length, join history with progress table on common columns
      (  SELECT p.job_id,
                MAX (p.job_progress_id) job_progress_id,
                MAX (GREATEST (p.ne_span_length, h.ne_span_length)) ne_span_length
           FROM tbl_fiber_inv_job_progress p
                JOIN tbl_fiber_inv_job_progress_h
                   ON     h.job_id = p.job_id
                      AND ...                --> additional join columns, if any
          WHERE     p.ums_group_ass_by_name = 'CMM'
                AND p.ums_group_ass_to_name IS NULL
       GROUP BY p.job_id)
-- get columns from the master table joined with the temp CTE on a common column (job_id)
SELECT j.job_id,
       j.span_id,
       j.link_id,
       t.job_progress_id,
       t.ne_span_length
  FROM tbl_fiber_inv_jobs j JOIN temp t ON t.job_id = j.job_id;