WITH RUCS_ACTUALES AS (
      SELECT distinct numruc_val
      FROM e_perm_aws.t_fact_vpc_agg_sunat_reniec 
      WHERE 1 = 1 
      AND periodo_val = (SELECT MAX(periodo_val) FROM e_perm_aws.t_fact_vpc_agg_sunat_reniec)
      and numruc_val not in 
        ('99100875D693E6E517C198F93BB48FAAE1719CC5F5ABB251F29927088DB130AF','33AB063FC4BD2EFBCA9095D32FE4D5C7DB51A75967E4CEB850C51A24CF5B0109'
    ,'C8AA77634A1A18895D4ABA02FDFC3166300DB32D23F1A1C6D6EC015CD3B79849','2EB80F2364324A2002A42A2D2D2F5C1505CEA96DB537E1E28C2A8D6A14951B3F'
    ,'A5BC35EBC11299703EEB435F1B407DBC9DE5878993B1DEF919F65C54C05DFA9E','B3B7BED64BB783289879315D37FC0EB354B138A4FB23D9E278959C22EEDA1E6C'
    ,'C097FC654892F91A88071834749C73CEC7CE8906C28700B8F5769BA4BA9BB38A','A35B7691CDC74B7763D8D6E76FD6217CAEFC752C5B3DD8D38DD268864817A4E8'
    ,'C98C2B30A186AD2104C334058DAEA48A34AAF56354551C305D0FC58B1DAD5A75','37B09F2B8F5922BEF98AFCB30F67B75525D151CC48709C533F33DF839F2C0DFF'
    ,'202EDCA9B582FBFB8EEF3C962E345946BAEE33FA3C48499894398C4CE903DF7B','A6C39D925674CD5BF5752112A8F2BE72AECE9AFDC9D52A00AA2CBB1E44E2FD43'
    ,'EA70D6E3AD5686A4611452356FC54E8F00F39B39D81D4FB1013697CEBA5CCBA4','1DAB2B346EA7CD85C4C8AD9B97D5B6449809AC9974FF6F9359230BBD50918E40'
    ,'3D1FB6B90992EF31CEC8A5E1D70B2C2DAA31B2F20D17F4737F2091C33D36A8E8','3D2A55895F5D7CF636F26C962AC499980DA1541A3D7C934B09C918F6FE79567F'
    ,'6C8511D0FDE72AA49B27EB6ECF199B7CC526FDFB065D71EEBBCC45755F025FA5','B5FADCAE267084A4032236BB339D064B73D49DC55B2D033201D4D9FE344E77E5'
    ,'5FD52C8BE1F1000B44F8E694A4C7C1AA66990847EB2F7818C5D55C2C0A956D4F','0A72AFFAA899ECF5220D8F18DB5C7CC21F938ED821DD43FE6304C78B906E6A88'
    ,'32E409315B2FC31842FD990049B8982B1CF0566200343984233C5007D06D9A46','E348A28E9D182794EBCC5010169C13A0BCB95A98EE71575D9E89FEBA231FF237'
    ,'58B7660D1BABE687D913918F0632B0665D59677320F243FB86C1CC1DE059E5D9','D48E58CF03013C7BE225BEE47792F6375F98099A2EABEF5C69B1023F7F2A6E9B'
    ,'2B29BF73B06237406EC014526AC14A9BB14BD20C48FA22C90320416677CFD590','913CC5B6877A84EE53C0F510D6F4FB3C28C16ECA906D01509F1E7973173BCF74'
    ,'ACDC8A45BD6A17367573B75534ACCB4F849669ADD6BBA5A78AA251479FE94D3F','6A36904431C651646B58FCE2D910838DAF048F5B2BE503FB9C09FF283AA24D60'
    ,'FD36F66C003241E2B1345517484276A1C9130AB9997ED5FCDBA84CCE439AD02C','42B40499D80F0A05AB35FD04F598A3E90FBE0290B2CA0521F126994CE7A2E182'
    ,'E4D1639E470E9428D0AA69A2C5015908893965C1FC06F583177B54E88807533C','E1A3CCB6D2CA69BFC5D5B3EEC901F98735FCAAD47D7F2B3E4ABA82A0A8B7F91F'
    ,'D7A4600D9948B0C0C660DC1F29C57F56F30FF597DB544FBB4133D60020738480','755BDE73B22B7930622BC7CF8F74F0E87144D4947C2D5C510B4B90F97AADFC0F'
    ,'9B5808A6B5958D12A954B897550FD29C70F5D14C39AD675C4C8B5AB99DE4065D','CC56FAC26B80AD14A7D359AFDD718B7A6731AB4309CC12066F48F7780E526631'
    ,'9717A9596FF75CAA66D9B640F8FCA34A420D809C51074DC570E925260FD29645','1DAD928560FC9214ED881D3FC198BDE0FF14CC7DDC85BD6148EAA546A0A425B8'
    ,'DA9D4938F2BD1600A5D65D64A78D597EA9D33757F57A70229AFFDD54F6C01138')
)
SELECT Z.*
FROM(
     SELECT key_value, key_value_ruc_pn,
            row_number() over(partition by key_value order by (CASE WHEN RR.numruc_val IS NULL THEN 0 ELSE 1 END) desc, fecinformacion desc) ORDEN_RUC
     FROM e_perm_aws.t_mst_encrip_dni_ruc_pn DR
     LEFT JOIN RUCS_ACTUALES RR 
     ON DR.key_value_ruc_pn = RR.numruc_val
) Z
WHERE Z.ORDEN_RUC = 1