<code>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;</code>