create or replace view delq_view (DIVISION,ENTITY,REGION,BO,BRANCH,ASSET_GROUP_CODE,SSET_GROUP_DESCRIPTION,CUSTOMER_NAME,UCIC_CODE,CUSTOMER_ID,CONTRACT_ID,PROJECT_ID,OS, booking_fees,FUTURE_PRIN,ACCRUED_CHARGES,RPA,TOTAL_OS,gross_receivables,BUCKET,CUSTOMER_DELINQUENCY_STATUS,AGREEMENT_DATE,ASSET_DESC, CLASSIFICATION,RBI_CLASSIFICATION,USG_CLASSIFICATION,GROUP_CODE,REGNO,INVOICE_NO,ENGINE_NO,CHASSIS_NO,CREDIT_OFFICER,BUSINESS_OFFICER, NEXT_DUE_CREATION_DT,ABS,FLEET_SIZE,INSURANCE_POLICYNO,AFC,NON_ACCRUED_CHARGES,ADD_1,ADD_2,ADD_3,ADD_4,CITY,PIN_CODE,PHONE_NO,CONTACT_PERSON, AMOUNT_RECEIVED,DATE_RECEIVED,DUE_GENERATED,DUE_COLLECTED,DMA_CODE,MARKET_VALUE,RBI_PROVISION_AMOUNT,ADJUSTED_TOTAL_OS,MONTH_OS,PORTFOLIO,LEGACY_ID, STATUS,UMFC_BALANCE,INTEREST_ACCRUED_NOT_DUE,REPOSSESSED_FLAG,REPOSSESSED_FLAG,NEXT_EMI,payment_mode,maturity_dt,noc_date,portfolio,plantandmachinery,PAN_NO,LoantoValueRatio,SRTFC_Flag,DEREG_AMT,LEGAL_ENTITY ) as SELECT pd_desc1 DIVISION, me_enty_name ENTITY, pm_reg_cd REGION, mpi_dealer_id BO, mpi_city_cd BRANCH, macd_asset_group ASSET_GROUP_CODE, pfg_grp_Desc SSET_GROUP_DESCRIPTION, mc_cust_name CUSTOMER_NAME, mc_ucic_cd UCIC_CODE, mpi_cust_id CUSTOMER_ID, mpi_con_id CONTRACT_ID, mpi_proj_id PROJECT_ID, DECODE(NVL(asset_rownum,1),1,mpf_inst_dues_balance,0) OS, DECODE(NVL(asset_rownum,1),1,mpf_dues_balance -mpf_inst_dues_balance,0) booking_fees, DECODE(NVL(asset_rownum,1),1,mpf_prin_balance - mpf_inst_prin_balance,0) FUTURE_PRIN, DECODE(NVL(asset_rownum,1),1,mpf_oo_acc_chg_os,0) ACCRUED_CHARGES, DECODE(NVL(asset_rownum,1),1, -mpf_rpa,0) RPA, DECODE(NVL(asset_rownum,1),1,(mpf_dues_balance + mpf_prin_balance - mpf_inst_prin_balance + mpf_oo_acc_chg_os - mpf_rpa),0) TOTAL_OS, DECODE(NVL(asset_rownum,1),1, NVL( (SELECT ROUND((SUM ( DECODE (NVL (mpi_repo_flg, 'N'), 'Y', DECODE (NVL (mpf_writeoff_amt, 0), 0, 0, DECODE (NVL (mpi.rec_st, 'X'), 'C', 0, (SELECT NVL(SUM ( NVL (mrs.mrs_prn_amt, 0 ) + NVL (mrs.mrs_int_amt, 0 ) + NVL (mrs.mrs_inst_tax, 0 ) ),0) FROM m_repmt_schd mrs WHERE mpi_cust_id = mrs.mrs_cust_id AND mpi_con_id = mrs.mrs_con_id AND mpi_proj_id = mrs.mrs_proj_id AND mrs_inst_due_dt > pc_business_dt AND mrs.mrs_pymt_flag = 'Y' AND NVL (mrs.mrs_tot_due, 0) != 0 ) ) ), DECODE (NVL (mpi.rec_st, 'X'), 'C', 0, (SELECT NVL(SUM ( NVL (mrs.mrs_prn_amt, 0) + NVL (mrs.mrs_int_amt, 0) + NVL (mrs.mrs_inst_tax, 0) ),0) FROM m_repmt_schd mrs WHERE mpi_cust_id = mrs.mrs_cust_id AND mpi_con_id = mrs.mrs_con_id AND mpi_proj_id = mrs.mrs_proj_id AND mrs_inst_due_dt > pc_business_dt AND mrs.mrs_pymt_flag = 'Y' AND NVL (mrs.mrs_tot_due, 0) != 0 ) ) )) + ( DECODE (NVL (mpi_repo_flg, 'N'), 'Y', 0, NVL (mpfv.mpf_dues_balance, 0) ) + NVL (mpf_oo_acc_chg_os, 0) ) - mpf_rpa), 2 ) FROM DUAL ),0),0) gross_receivables, DECODE(NVL(mpi_dpd,0),0,'Current',DECODE(SIGN(29-NVL(mpi_dpd,0)),-1,DECODE(SIGN(59-NVL(mpi_dpd,0)),-1,DECODE(SIGN(89-NVL(mpi_dpd,0)),-1,DECODE(SIGN(180-NVL(mpi_dpd,0)),-1, DECODE(SIGN(360-NVL(mpi_dpd,0)),-1,'360+','181-360'),'090-180'),'060-089'),'030-059'),'000-029')) BUCKET, CustomerBuck.cust_buc CUSTOMER_DELINQUENCY_STATUS, mpi_st_dt AGREEMENT_DATE, macd_asset_desc ASSET_DESC, mpi_dq_stat_cd CLASSIFICATION, mpi_rbi_dq_stat_cd RBI_CLASSIFICATION, mpi_usg_dq_stat_cd USG_CLASSIFICATION, mpi_group_code GROUP_CODE, NVL(med_regno,mvd_regno) REGNO, macd_invoice_no INVOICE_NO, NVL(med_engine_no,mvd_engine_no) ENGINE_NO, NVL(med_chas_no,mvd_chas_no) CHASSIS_NO, act_by CREDIT_OFFICER, NVL(business_officer,mc_rel_mgr) BUSINESS_OFFICER, DECODE(SIGN(mpi_mat_dt-pc_business_dt),1,mpf_next_due_cre_dt,'') NEXT_DUE_CREATION_DT, DECODE(mpi_sold_flag,'S',msd_secu_id,' ') ABS, NVL(fleet.vehsize,0) FLEET_SIZE, mid_policy_no INSURANCE_POLICYNO, DECODE(NVL(asset_rownum,1),1,mpf_oo_late_chg_os,0) AFC, DECODE(NVL(asset_rownum,1),1,mpf_oo_non_acc_chg_os,0) NON_ACCRUED_CHARGES, mpi_dpd DPD, Mca_add_ln1 ADD_1, Mca_add_ln2 ADD_2, Mca_add_ln3 ADD_3, Mca_add_ln4 ADD_4, mca_city CITY, Mca_zip_cd PIN_CODE, Mca_tel1 PHONE_NO, Mc_cont_per1 CONTACT_PERSON, DECODE(NVL(asset_rownum,1),1,(Mpf_dues_paid + mpf_rpa),0) AMOUNT_RECEIVED, mpf_last_pymt_dt DATE_RECEIVED, DECODE(NVL(asset_rownum,1),1,Mpf_tot_dues,0) DUE_GENERATED, DECODE(NVL(asset_rownum,1),1,Mpf_dues_paid,0) DUE_COLLECTED, Mpi_dma_id DMA_CODE, DECODE(NVL(asset_rownum,1),1,Macd_asset_rev_fmv,0) MARKET_VALUE, DECODE(NVL(asset_rownum,1),1,mpf_rbi_net_prov_os,0) RBI_PROVISION_AMOUNT, DECODE(NVL(asset_rownum,1),1,mpf_dues_paid,0) ADJUSTED_TOTAL_OS, mpi_buck_stat MONTH_OS, 'NEW' PORTFOLIO, mpi_legacy_id LEGACY_ID, DECODE(mpi.rec_st,'A','LIVE','CLOSED') STATUS, DECODE(NVL(asset_rownum,1),1,NVL(mpf_umfc,0),0) UMFC_BALANCE, DECODE(NVL(asset_rownum,1),1,DECODE(SIGN(mpi_mat_dt-pc_business_dt),1,NVL((mpf_int_accrued - mpf_inst_int_due),0),''),0) INTEREST_ACCRUED_NOT_DUE, NVL(mpi_repo_flg,'N') REPOSSESSED_FLAG, asset_rownum asset, DECODE(NVL(asset_rownum,1),1,DECODE(SIGN(mpi_mat_dt-pc_business_dt),1,mrs_inst_due,''),0) NEXT_EMI, mpi_pay_mode payment_mode, mpi_mat_dt maturity_dt, hn_noc_issue_date noc_date, NVL(mpi_proposal_id,'NEW') portfolio, NVL(p.inv_plant_mc,0) plantandmachinery, NVL(MC_PAN_NO_ADDRESS,'NA') PAN_NO, (SELECT ROUND(NVL(SUM((MDI_ACT_DISB)/(MACD_ASSET_NML_VALUE)),0),1) FROM m_asset_cost_details, m_disb_info, m_asset_proj_details a WHERE macd_asset_id =a.mapd_asset_id AND macd_cust_id =mdi_cust_id AND MACD_ASSET_NML_VALUE!=0 AND mdi_cust_id =a.MAPD_CUST_ID AND mdi_con_id =a.MAPD_CON_ID AND mdi_proj_id =a.MAPD_PROJ_ID AND mdi_cust_id =MPI_CUST_ID AND mdi_con_id =MPI_CON_ID AND mdi_proj_id =MPI_PROJ_ID ) LoantoValueRatio, NVL(MPI_SRTF_SOLD_FLG,'N') SRTFC_Flag, (SELECT NVL(MPF_USG_INT_DERG_IAND,0) FROM m_proj_finbal WHERE mpf_cust_id=mpi.mpi_cust_id AND mpf_con_id =mpi.mpi_con_id AND mpf_proj_id =mpi.mpi_proj_id ) DEREG_AMT , NVL(mc_entity,'NA') LEGAL_ENTITY FROM p_ctrl, p_mapping, m_cust_add, m_cust, m_proj_other_details, m_proj_finbal_view mpfv, m_secu_dtls, m_repmt_schd , (SELECT mpi_cust_id mpt, DECODE(MAX(NVL(mpi_dpd,0)),0,'Current',DECODE(SIGN(29-MAX(NVL(mpi_dpd,0))),-1,DECODE(SIGN(59-MAX(NVL(mpi_dpd,0))),-1,DECODE(SIGN(89-MAX(NVL(mpi_dpd,0))),-1,DECODE(SIGN(179-MAX(NVL(mpi_dpd,0))),-1,DECODE(SIGN(359-MAX(NVL(mpi_dpd,0))),-1,'360+DPD','180+DPD'),'90+DPD'),'60+DPD'),'30+DPD'),'0-29DPD')) cust_buc FROM m_proj_info WHERE rec_st='A' GROUP BY mpi_cust_id ) CustomerBuck , (SELECT app_id, first_name ||' ' ||middle_name ||' ' ||last_name business_officer FROM ocr_appl_mb, ocr_user_mb WHERE user_id=do_name ) busi, m_proj_info mpi, p_division, m_enty, (SELECT act_by, cust_id, contract_id, proj_id FROM (SELECT rank() over(partition BY agreement_no order by seq_num DESC) credit_rownum, first_name ||' ' ||middle_name ||' ' ||last_name act_by, cust_id, contract_id, proj_id FROM ocr_mds_assets_dl, ocr_deal_log odl, ocr_user_mb WHERE id = app_id AND upper(stg_name) ='CREDIT APPROVAL' AND upper(odl.status)='CO APPROVAL PENDING' AND (upper(action) ='RECOMMEND' OR upper(action) ='APPROVE') AND id_type ='APP_ID' AND act_by =user_id ) WHERE credit_Rownum = 1 UNION ALL SELECT act_by, cust_id, contract_id, proj_id FROM (SELECT RANK () OVER (PARTITION BY cust_id, contract_id, proj_id ORDER BY seq_num DESC) credit_rownum, first_name ||' ' ||middle_name ||' ' ||last_name act_by, cust_id, contract_id, proj_id FROM ocr_mds_entry_dl, ocr_deal_log odl, ocr_user_mb WHERE id = app_id AND upper(stg_name) ='PITCHAPPROVAL' AND upper(odl.status)='PITCH APPROVAL PENDING' AND upper(action) ='APPROVE' AND id_type ='APP_ID' AND act_by =user_id ) WHERE credit_Rownum = 1 ) credit, (SELECT cust_id, SUM(NVL(veh_own,0)+NVL(veh_family_attached,0)+NVL(veh_non_family_attached,0)) vehsize FROM ocr_cust_fleet_mb GROUP BY cust_id ) fleet , (SELECT rank() over (partition BY mapd_cust_id ||mapd_con_id ||mapd_proj_id order by mapd_asset_id) asset_rownum, mapd_cust_id, mapd_con_id, mapd_proj_id, macd_asset_group, macd_asset_rev_fmv, pfg_grp_Desc , macd_asset_desc, med_regno, mvd_regno, macd_invoice_no, med_engine_no, mvd_engine_no, med_chas_no, mvd_chas_no, mid_policy_no FROM p_fa_grp, m_equipment_details, m_vehicle_details, m_insurance_details, m_asset_cost_Details, m_asset_proj_details WHERE macd_asset_group = pfg_grp_cd AND mapd_cust_id =mid_cust_id(+) AND mapd_asset_id = mid_asset_id(+) AND mapd_asset_id = macd_asset_id AND mapd_cust_id = macd_cust_id AND macd_cust_id = med_cust_id(+) AND macd_asset_id = med_asset_id(+) AND macd_cust_id = mvd_cust_id(+) AND macd_asset_id = mvd_asset_id(+) AND macd_object_financed IN ('C','T') ) asset, (SELECT hn_cust_id, hn_con_id, hn_proj_id, MAX(hn_noc_issue_date) hn_noc_issue_date FROM h_noc GROUP BY hn_cust_id, hn_con_id, hn_proj_id ) noc, ocr_appl_mb p WHERE mpi_city_cd = pm_branch_cd AND pm_enty_cd = me_enty_cd AND mpod_division = pd_cd AND mpi_cust_id = CustomerBuck.mpt(+) AND mpi_cust_id = fleet.cust_id(+) AND mpi_cust_id = ALL(mpf_cust_id,mpod_cust_id,mc_cust_id,mca_cust_id) AND mpi_con_id = ALL(mpf_con_id,mpod_con_id) AND mpi_proj_id = ALL(mpf_proj_id,mpod_proj_id) AND NVL(mc_mailing_address,'OFFICE') = mca_add_type AND mpi_cust_id = credit.cust_id(+) AND mpi_con_id = credit.contract_id(+) AND mpi_proj_id = credit.proj_id(+) AND mpi_cust_id = msd_cust_id(+) AND mpi_con_id = msd_con_id(+) AND mpi_proj_id = msd_proj_id(+) AND msd_flg(+) = 'S' AND mpi_cust_id = asset.mapd_cust_id(+) AND mpi_con_id = asset.mapd_con_id(+) AND mpi_proj_id = asset.mapd_proj_id(+) AND mpod_application_id =busi.app_id(+) AND mpf_cust_id = mrs_cust_id(+) AND mpf_con_id = mrs_con_id(+) AND mpf_proj_id = mrs_proj_id(+) AND mpf_next_due_cre_dt = mrs_inst_due_dt(+) AND mpi_cust_id = hn_cust_id(+) AND mpi_con_id = hn_con_id(+) AND mpi_proj_id = hn_proj_id(+) AND busi.app_id = p.app_id(+)