gke_arce_rupe2.rupe

Parameters

Name	Type	Mode
$1	ARRAY	IN

Definition

DECLARE
    cods ALIAS FOR $1;
    provid INTEGER;
    codfiscal varchar(100);
    rowcount INTEGER; 
    usuid INTEGER;
    usu_identificacionid INTEGER;
BEGIN
	FOREACH codfiscal IN ARRAY cods LOOP
	    RAISE INFO 'Delete prov %', codfiscal;
	    
	    SELECT INTO provid prv_id FROM rupe.rupe_proveedores WHERE prv_cod_fiscal = codfiscal;
	    
	    IF NOT FOUND THEN
		RAISE WARNING '% not found', codfiscal;
	    END IF;
	    -- schema AUDIT
	    DELETE FROM audit.rupe_rel_cont_atrib_aud WHERE rca_con_id in (SELECT con_id FROM rupe.rupe_contactos_proveedor WHERE con_proveedores_prv_id  = provid);
	    GET DIAGNOSTICS rowcount = ROW_COUNT;
	    RAISE INFO 'rupe_rel_cont_atrib_aud % rows deleted', rowcount;
	    
	    DELETE FROM audit.rupe_roles_en_proveedor_contacto_proveedor_aud WHERE rcp_contacto_proveedor_con_id in (SELECT con_id FROM rupe.rupe_contactos_proveedor WHERE con_proveedores_prv_id  = provid);
	    GET DIAGNOSTICS rowcount = ROW_COUNT;
	    RAISE INFO 'rupe_roles_en_proveedor_contacto_proveedor_aud % rows deleted', rowcount;
	    
	    DELETE FROM audit.rupe_contactos_proveedor_aud WHERE con_proveedores_prv_id  = provid;
	    GET DIAGNOSTICS rowcount = ROW_COUNT;
	    RAISE INFO 'rupe_contactos_proveedor_aud % rows deleted', rowcount;
	    
	    DELETE FROM audit.rupe_vinculaciones_sice_proveedor_aud WHERE vso_proveedor_prv_id = provid;
	    GET DIAGNOSTICS rowcount = ROW_COUNT;
	    RAISE INFO 'rupe_vinculaciones_sice_proveedor_aud % rows deleted', rowcount;
	    
	    DELETE FROM audit.rupe_documentos_electronicos_proveedores_aud WHERE deo_proveedor_prv_id = provid;
	    GET DIAGNOSTICS rowcount = ROW_COUNT;
	    RAISE INFO 'rupe_documentos_electronicos_proveedores_aud % rows deleted', rowcount;

	    DELETE FROM audit.rupe_hechos_documentos_electronicos_aud WHERE hdc_hecho_hch_id in (SELECT hch_id FROM rupe.rupe_hechos WHERE hch_proveedor_prv_id = provid);
	    GET DIAGNOSTICS rowcount = ROW_COUNT;
	    RAISE INFO 'rupe_hechos_documentos_electronicos_aud % rows deleted', rowcount;
	    
	    DELETE FROM audit.rupe_documentos_electronicos_aud WHERE doc_id in (SELECT deo_documento_electronico_doc_id FROM rupe.rupe_documentos_electronicos_proveedores WHERE deo_proveedor_prv_id = provid);
	    GET DIAGNOSTICS rowcount = ROW_COUNT;
	    RAISE INFO 'rupe_documentos_electronicos_aud % rows deleted', rowcount;
	    
	    DELETE FROM audit.rupe_alcances_cuentas_bancarias_aud WHERE acb_cuenta_bancaria_cnt_id  in (SELECT cnt_id FROM audit.rupe_cuentas_bancarias_aud WHERE cnt_proveedor_prv_id = provid);
	    GET DIAGNOSTICS rowcount = ROW_COUNT;
	    RAISE INFO 'rupe_alcances_cuentas_bancarias_aud % rows deleted', rowcount;
	    
	    DELETE FROM audit.rupe_cuentas_bancarias_aud WHERE cnt_proveedor_prv_id = provid;
	    GET DIAGNOSTICS rowcount = ROW_COUNT;
	    RAISE INFO 'rupe_cuentas_bancarias_aud % rows deleted', rowcount;
	    
	    DELETE FROM audit.rupe_identificaciones_proveedor_aud WHERE ido_proveedor_prv_id = provid;
	    GET DIAGNOSTICS rowcount = ROW_COUNT;
	    RAISE INFO 'rupe_identificaciones_proveedor_aud % rows deleted', rowcount;
	    
	    DELETE FROM audit.rupe_revocacion_sancion_aud WHERE rev_san_id in (SELECT hch_id FROM rupe.rupe_hechos WHERE hch_proveedor_prv_id = provid);
	    GET DIAGNOSTICS rowcount = ROW_COUNT;
	    RAISE INFO 'rupe_revocacion_sancion_aud % rows deleted', rowcount;
	    
	    DELETE FROM audit.rupe_hechos_aud WHERE hch_proveedor_prv_id = provid;
	    GET DIAGNOSTICS rowcount = ROW_COUNT;
	    RAISE INFO 'rupe_hechos_aud % rows deleted', rowcount;
	    
	    DELETE FROM audit.rupe_cumplimientos_aud WHERE cump_prov_id = provid;
	    GET DIAGNOSTICS rowcount = ROW_COUNT;
	    RAISE INFO 'rupe_cumplimientos_aud % rows deleted', rowcount;
	    
	    DELETE FROM audit.rupe_vencimientos_certificado_aud WHERE vce_proveedor_prv_id = provid;              
	    GET DIAGNOSTICS rowcount = ROW_COUNT;
	    RAISE INFO 'rupe_vencimientos_certificado_aud % rows deleted', rowcount;
	    
	    DELETE FROM audit.rupe_datos_comunicacion_proveedor_aud WHERE dco_proveedor_prv_id = provid;
	    GET DIAGNOSTICS rowcount = ROW_COUNT;
	    RAISE INFO 'rupe_datos_comunicacion_proveedor_aud % rows deleted', rowcount;
	    

	    -- schema RUPE
	    DELETE FROM rupe.rupe_rel_cont_atrib WHERE rca_con_id in (SELECT con_id FROM rupe.rupe_contactos_proveedor WHERE con_proveedores_prv_id  = provid); 
	    GET DIAGNOSTICS rowcount = ROW_COUNT;
	    RAISE INFO 'rupe_rel_cont_atrib % rows deleted', rowcount;
	
	    DELETE FROM rupe.rupe_roles_en_proveedor_contacto_proveedor WHERE rcp_contacto_proveedor_con_id in (SELECT con_id FROM rupe.rupe_contactos_proveedor WHERE con_proveedores_prv_id  = provid);
	    GET DIAGNOSTICS rowcount = ROW_COUNT;
	    RAISE INFO 'rupe_roles_en_proveedor_contacto_proveedor % rows deleted', rowcount;
	    
	    DELETE FROM rupe.rupe_contactos_proveedor WHERE con_proveedores_prv_id  = provid;
	    GET DIAGNOSTICS rowcount = ROW_COUNT;
	    RAISE INFO 'rupe_contactos_proveedor % rows deleted', rowcount;
	    
	    DELETE FROM rupe.rupe_vinculaciones_sice_proveedor WHERE vso_proveedor_prv_id = provid;
	    GET DIAGNOSTICS rowcount = ROW_COUNT;
	    RAISE INFO 'rupe_vinculaciones_sice_proveedor % rows deleted', rowcount;
	    
	    DELETE FROM rupe.rupe_documentos_electronicos_proveedores WHERE deo_proveedor_prv_id = provid;
	    GET DIAGNOSTICS rowcount = ROW_COUNT;
	    RAISE INFO 'rupe_documentos_electronicos_proveedores % rows deleted', rowcount;
	    
	    DELETE FROM rupe.rupe_hechos_documentos_electronicos WHERE hdc_hecho_hch_id in (SELECT hch_id FROM rupe.rupe_hechos WHERE hch_proveedor_prv_id = provid);
	    GET DIAGNOSTICS rowcount = ROW_COUNT;
	    RAISE INFO 'rupe_hechos_documentos_electronicos % rows deleted', rowcount;
	    
	    DELETE FROM rupe.rupe_documentos_electronicos WHERE doc_id in (SELECT deo_documento_electronico_doc_id FROM rupe.rupe_documentos_electronicos_proveedores WHERE deo_proveedor_prv_id = provid);
	    GET DIAGNOSTICS rowcount = ROW_COUNT;
	    RAISE INFO 'rupe_documentos_electronicos % rows deleted', rowcount;
	    
	    DELETE FROM rupe.rupe_alcances_cuentas_bancarias  WHERE acb_cuenta_bancaria_cnt_id  in (SELECT cnt_id FROM rupe.rupe_cuentas_bancarias WHERE cnt_proveedor_prv_id = provid);
	    GET DIAGNOSTICS rowcount = ROW_COUNT;
	    RAISE INFO 'rupe_alcances_cuentas_bancarias % rows deleted', rowcount;
	    
	    DELETE FROM rupe.rupe_cuentas_bancarias WHERE cnt_proveedor_prv_id = provid;
	    GET DIAGNOSTICS rowcount = ROW_COUNT;
	    RAISE INFO 'rupe_cuentas_bancarias % rows deleted', rowcount;
	    
	    DELETE FROM rupe.rupe_identificaciones_proveedor WHERE ido_proveedor_prv_id = provid;
	    GET DIAGNOSTICS rowcount = ROW_COUNT;
	    RAISE INFO 'rupe_identificaciones_proveedor % rows deleted', rowcount;
	    
	    DELETE FROM rupe.rupe_revocacion_sancion WHERE rev_san_id in (SELECT hch_id FROM rupe.rupe_hechos WHERE hch_proveedor_prv_id = provid);
	    GET DIAGNOSTICS rowcount = ROW_COUNT;
	    RAISE INFO 'rupe_revocacion_sancion % rows deleted', rowcount;
	    
	    DELETE FROM rupe.rupe_hechos WHERE hch_proveedor_prv_id = provid;
	    GET DIAGNOSTICS rowcount = ROW_COUNT;
	    RAISE INFO 'rupe_hechos % rows deleted', rowcount;
	    
	    DELETE FROM rupe.rupe_cumplimientos WHERE cump_prov_id = provid;            
	    GET DIAGNOSTICS rowcount = ROW_COUNT;
	    RAISE INFO 'rupe_cumplimientos % rows deleted', rowcount;
	    
	    DELETE FROM rupe.rupe_vencimientos_certificado WHERE vce_proveedor_prv_id = provid;
	    GET DIAGNOSTICS rowcount = ROW_COUNT;
	    RAISE INFO 'rupe_vencimientos_certificado % rows deleted', rowcount;
	    
	    DELETE FROM rupe.rupe_datos_comunicacion_proveedor WHERE dco_proveedor_prv_id = provid;
	    GET DIAGNOSTICS rowcount = ROW_COUNT;
	    RAISE INFO 'rupe_datos_comunicacion_proveedor % rows deleted', rowcount;

	    SELECT INTO usuid prv_usuario_usu_id FROM rupe.rupe_proveedores WHERE prv_id = provid;
	    
	    DELETE FROM rupe.rupe_proveedores WHERE prv_id = provid;
	    GET DIAGNOSTICS rowcount = ROW_COUNT;
	    RAISE INFO 'rupe_proveedores % rows deleted', rowcount;
	    
	    SELECT INTO usu_identificacionid usu_identificacion_idu_id FROM rupe.rupe_usuarios WHERE usu_id = usuid;
            
	    
	    DELETE FROM audit.rupe_rel_usu_rol_aud WHERE rur_usu_id = usuid;
	    GET DIAGNOSTICS rowcount = ROW_COUNT;
	    RAISE INFO 'rupe_rel_usu_rol_aud % rows deleted', rowcount;
	    
	    DELETE FROM audit.rupe_usuarios_aud WHERE usu_id = usuid;
	    GET DIAGNOSTICS rowcount = ROW_COUNT;
	    RAISE INFO 'rupe_usuarios_aud % rows deleted', rowcount;
	    

	    DELETE FROM rupe.rupe_rel_usu_rol WHERE rur_usu_id = usuid;
	    GET DIAGNOSTICS rowcount = ROW_COUNT;
	    RAISE INFO 'rupe_rel_usu_rol % rows deleted', rowcount;
	    
	    DELETE FROM rupe.rupe_usuarios WHERE usu_id = usuid;
	    GET DIAGNOSTICS rowcount = ROW_COUNT;
	    RAISE INFO 'rupe_usuarios % rows deleted', rowcount;
	    
	    DELETE FROM rupe.rupe_identificaciones_usuario WHERE idu_id = usu_identificacionid;
	    GET DIAGNOSTICS rowcount = ROW_COUNT;
	    RAISE INFO 'rupe_identificaciones_usuario % rows deleted', rowcount;
	    
	    DELETE FROM audit.rupe_identificaciones_usuario_aud WHERE idu_id = usu_identificacionid;
	    GET DIAGNOSTICS rowcount = ROW_COUNT;
	    RAISE INFO 'rupe_identificaciones_usuario_aud % rows deleted', rowcount;
	    
	    
	END loop;

END;

delete_prov(character varying[])

Parameters

Definition