I have a view where multiple JOB PROGRESS ID is attached to 1 JOB_ID. So if there are 2 JOB PROGRESS ID there are 2 status_id. SO I want to get the latest status id based on job porgress id desc. Below is the view.
CREATE OR REPLACE VIEW "APP_FIPINVRITL"."CMP_PANINDIA_VIEW_AGING_UPD"
("CIRCLE", "MP", "SPAN_TYPE", "SPAN_LINK_ID", "NE_LENGTH", "ROUTE_ACCEPTED", "LIT_ACCEPTED", "MISSING_ASBUILT", "STATUS", "OFFERED_DATE", "HOTO_ACCEPTED_DATE", "LIT_ACCEPTED_DATE", "ASSIGNED_BY", "ASSIGNED_TO", "JOB_PROGRESS_FLAG", "ROUTE_APPROVED_BY_CMM", "LIT_APPROVED_BY_CMM", "CMM_APPROVED_DATE", "REOFFERFLAG", "NO_OF_DAYS_AGING", "REJECT_REMARKS") AS
WITH main_data AS (
SELECT DISTINCT
mzb.jiostatename,
mzb.jiostatecode,
mzb.maintenancezonename,
mzb.maintenancezonecode
FROM
r4g_lb.mantainenceboundary_evw mzb
--dual
ORDER BY
mzb.jiostatename
), hotostatus AS (
SELECT
sm.status_id,
sm.status_name AS status
FROM
APP_FIPINVRITL.tbl_fiber_inv_status_master sm
), jobs_data AS (
SELECT DISTINCT
jb.job_id,
nvl(
jb.span_id,
'NA'
) AS span_id,
nvl(
jb.link_id,
'NA'
) AS link_id,
nvl(
jb.fsa_id,
'NA'
) AS fsa_id,
CASE
WHEN jb.span_type = 'INTERCITY' THEN
jb.span_id
WHEN jb.span_type = 'INTRACITY' THEN
jb.link_id
WHEN jb.span_type = 'ENTERPRISE' THEN
jb.link_id
WHEN jb.span_type = 'FTTX' THEN
jb.fsa_id
END AS clubed_span,
jb.maintenancezonecode AS maintenance_code,
jb.created_date AS offered_date,
nvl(
jb.maint_zone_ne_span_length,
0
) AS ne_length,
nvl(
jb.missing_abd_length,
0
) AS missing_abd_length,
jb.span_type,
jb.job_flag,
jb.reofferflag
FROM
APP_FIPINVRITL.tbl_fiber_inv_jobs jb
/* WHERE NOT EXISTS
(SELECT SPAN_LINK_ID
FROM APP_FIPINVRITL.TBL_FIBER_INV_DROPSPAN kb
WHERE jb.SPAN_ID=kb.SPAN_LINK_ID
)
OR NOT EXISTS
(SELECT SPAN_LINK_ID
FROM TBL_FIBER_INV_DROPSPAN.TBL_FIBER_INV_DROPSPAN kb
WHERE jb.SPAN_ID=kb.SPAN_LINK_ID
) */
), jobprogress_data AS (
SELECT
t.job_id,
t.status_id,
t.hoto_actual_length,
t.lit_actual_length,
t.hoto_offered_date,
t.hoto_accepted_date,
t.lit_accepted_date,
t.job_progress_flag,
t.route_accepted_by_cmm,
t.lit_accepted_by_cmm,
t.cmm_approved_date,
t.modified_date,
t.approv_reject_remark
FROM
(
SELECT DISTINCT
jbp.job_progress_id,
jbp.job_id,
ROW_NUMBER()
OVER(PARTITION BY jbp.job_id
ORDER BY
jbp.job_id
) AS srno,
jbp.status_id,
SUM(nvl(
(
CASE
WHEN jbp.rejected_by LIKE('%')
AND jbp.ums_group_ass_to_name LIKE('Construction_Engineer_OL')
AND jbp.job_progress_flag = 0 THEN
0
ELSE
jbp.hoto_actual_length
END
),
0
))
OVER(PARTITION BY jbp.job_id
ORDER BY
jbp.job_id
) AS hoto_actual_length,
SUM(nvl(
(
CASE
WHEN jbp.rejected_by LIKE('%')
AND jbp.ums_group_ass_to_name LIKE('Construction_Engineer_OL')
AND jbp.job_progress_flag = 0 THEN
0
ELSE
jbp.lit_actual_length
END
),
0
))
OVER(PARTITION BY jbp.job_id
ORDER BY
jbp.job_id
) AS lit_actual_length,
hoto_offered_date,
MAX(decode(
jbp.hoto_acceptence_date,
NULL,
'01-JAN-01',
jbp.hoto_acceptence_date
))
OVER(PARTITION BY jbp.job_id
ORDER BY
jbp.job_id
) AS hoto_accepted_date,
MAX(decode(
jbp.lit_acceptence_date,
NULL,
'01-JAN-01',
jbp.lit_acceptence_date
))
OVER(PARTITION BY jbp.job_id
ORDER BY
jbp.job_id
) AS lit_accepted_date,
jbp.job_progress_flag,
SUM(nvl(
(
CASE
WHEN jbp.rejected_by LIKE('%')
AND jbp.ums_group_ass_to_name LIKE('Construction_Engineer_OL')
OR jbp.job_progress_flag = 0 THEN
0
ELSE
jbp.hoto_actual_length
END
),
0
))
OVER(PARTITION BY jbp.job_id
ORDER BY
jbp.job_id
) AS route_accepted_by_cmm,
SUM(nvl(
(
CASE
WHEN jbp.rejected_by LIKE('%')
AND jbp.ums_group_ass_to_name LIKE('Construction_Engineer_OL')
--OR jbp.job_progress_flag = 0 THEN
OR jbp.job_progress_flag = 1 THEN
0
ELSE
jbp.lit_actual_length
END
),
0
))
OVER(PARTITION BY jbp.job_id
ORDER BY
jbp.job_id
) AS lit_accepted_by_cmm,
CASE
WHEN jbp.ums_group_ass_by_name LIKE ( 'CMM_OL' ) THEN
jbp.approved_date
ELSE
NULL
END AS cmm_approved_date,
jbp.modified_date,
jbp.approv_reject_remark
FROM
APP_FIPINVRITL.tbl_fiber_inv_job_progress jbp
ORDER BY
jbp.modified_date ASC
) t
WHERE
t.srno = 1
), assigndata AS (
SELECT
job_id,
assigned_by,
assigned_to
FROM
(
SELECT DISTINCT
x.job_id,
decode(
y.ums_group_ass_by_name,
NULL,
'NA',
y.ums_group_ass_by_name
) AS assigned_by,
decode(
y.ums_group_ass_to_name,
NULL,
'NA',
y.ums_group_ass_to_name
) AS assigned_to
FROM
(
SELECT
y1.job_id,
y1.ums_group_ass_to_date,
y1.job_progress_flag
FROM
(
SELECT
jp1.job_id,
jp1.job_progress_flag,
ROW_NUMBER()
OVER(PARTITION BY jp1.job_id
ORDER BY
jp1.job_id
) srno,
MAX(decode(
jp1.ums_group_ass_to_date,
NULL,
TO_DATE('01-01-1801',
'DD-MM-YYYY'),
jp1.ums_group_ass_to_date
)) AS ums_group_ass_to_date
FROM
APP_FIPINVRITL.tbl_fiber_inv_job_progress jp1
GROUP BY
jp1.job_id,
jp1.job_progress_flag
ORDER BY
jp1.job_id
) y1
WHERE
y1.srno = 1
) x,
APP_FIPINVRITL.tbl_fiber_inv_job_progress y
WHERE
x.job_id = y.job_id
AND (
CASE
WHEN x.ums_group_ass_to_date = TO_DATE('01-01-1801', 'DD-MM-YYYY') THEN
1
ELSE
CASE
WHEN x.ums_group_ass_to_date = y.ums_group_ass_to_date THEN
1
ELSE
0
END
END
) = 1
AND x.job_progress_flag = y.job_progress_flag
)
ORDER BY
job_id
)
SELECT DISTINCT
md.jiostatename AS circle,
md.maintenancezonename
|| '/'
|| jd.maintenance_code AS mp,
jd.span_type AS span_type,
jd.clubed_span AS span_link_id,
jd.ne_length,
jpd.hoto_actual_length AS route_accepted,
jpd.lit_actual_length AS lit_accepted,
jd.missing_abd_length AS missing_asbuilt,
hs.status,
jd.offered_date,
decode(
jpd.hoto_accepted_date,
'01-JAN-01',
'NOT PRESENT',
jpd.hoto_accepted_date
) AS hoto_accepted_date,
decode(
jpd.lit_accepted_date,
'01-JAN-01',
'NOT PRESENT',
jpd.lit_accepted_date
) AS lit_accepted_date,
ad.assigned_by,
ad.assigned_to,
jpd.job_progress_flag,
jpd.route_accepted_by_cmm AS route_approved_by_cmm,
jpd.lit_accepted_by_cmm AS lit_approved_by_cmm,
jpd.cmm_approved_date,
jd.reofferflag,
round(
(sysdate - jpd.modified_date),
0
) AS "JPD.MODIFIED_DATE",
jpd.approv_reject_remark
FROM
jobs_data jd
LEFT JOIN main_data md ON md.maintenancezonecode = jd.maintenance_code
LEFT JOIN jobprogress_data jpd ON jpd.job_id = jd.job_id
LEFT JOIN assigndata ad ON ad.job_id = jd.job_id
LEFT JOIN hotostatus hs ON hs.status_id = jpd.status_id
WHERE
jd.ne_length > 0
ORDER BY
span_type,
circle,
mp;