create or replace PROCEDURE PRC_AFW_USERS ( i_operation IN CHAR DEFAULT NULL, i_option IN CHAR DEFAULT NULL, io_codusuario IN OUT NUMBER, i_identity_type IN CHAR DEFAULT NULL, i_uname IN VARCHAR2 DEFAULT NULL, i_username IN VARCHAR2 DEFAULT NULL, i_clave IN VARCHAR2 DEFAULT NULL, i_coddepto IN VARCHAR2 DEFAULT NULL, i_google_address IN VARCHAR2 DEFAULT NULL, i_address1 IN VARCHAR2 DEFAULT NULL, i_address2 IN VARCHAR2 DEFAULT NULL, i_address3 IN VARCHAR2 DEFAULT NULL, i_phone1 IN VARCHAR2 DEFAULT NULL, i_phone2 IN VARCHAR2 DEFAULT NULL, i_phone3 IN VARCHAR2 DEFAULT NULL, i_ext IN VARCHAR2 DEFAULT NULL, i_fax IN VARCHAR2 DEFAULT NULL, i_mobil IN VARCHAR2 DEFAULT NULL, i_email IN VARCHAR2 DEFAULT NULL, i_usuacomments IN VARCHAR2 DEFAULT NULL, i_user_image IN VARCHAR2 DEFAULT NULL, i_user_language IN VARCHAR2 DEFAULT NULL, i_identity_number IN VARCHAR2 DEFAULT NULL, i_fl_str_query IN VARCHAR2 DEFAULT NULL, i_fecexpiration IN DATE DEFAULT NULL, i_user_birthdate IN DATE DEFAULT NULL, i_codperfil IN NUMBER DEFAULT NULL, i_codjefeinm IN NUMBER DEFAULT NULL, i_nivelconoc IN NUMBER DEFAULT NULL, i_usertype IN NUMBER DEFAULT NULL, i_casetype IN NUMBER DEFAULT NULL, i_cha_id IN NUMBER DEFAULT NULL, i_ula IN NUMBER DEFAULT NULL, i_nivel IN NUMBER DEFAULT NULL, i_uss_id IN NUMBER DEFAULT NULL, i_countryid IN NUMBER DEFAULT NULL, i_cityid IN NUMBER DEFAULT NULL, i_siteid IN NUMBER DEFAULT NULL, i_buildingid IN NUMBER DEFAULT NULL, i_floorid IN NUMBER DEFAULT NULL, i_companyid IN NUMBER DEFAULT NULL, i_numberdepid IN NUMBER DEFAULT NULL, i_user_active IN NUMBER DEFAULT NULL, i_profileid IN NUMBER DEFAULT NULL, i_roleid IN NUMBER DEFAULT NULL, i_project_id IN NUMBER DEFAULT NULL, i_adu_field1 IN VARCHAR2 DEFAULT NULL, i_adu_field2 IN VARCHAR2 DEFAULT NULL, i_adu_field3 IN VARCHAR2 DEFAULT NULL, i_adu_field4 IN VARCHAR2 DEFAULT NULL, i_adu_field5 IN VARCHAR2 DEFAULT NULL, i_adu_field6 IN VARCHAR2 DEFAULT NULL, i_adu_field7 IN VARCHAR2 DEFAULT NULL, i_adu_field8 IN VARCHAR2 DEFAULT NULL, i_adu_field9 IN VARCHAR2 DEFAULT NULL, i_adu_field10 IN VARCHAR2 DEFAULT NULL, i_filter IN VARCHAR2 DEFAULT NULL, i_active IN NUMBER DEFAULT NULL, i_latitude IN DECIMAL DEFAULT 0, i_longitude IN DECIMAL DEFAULT 0, i_language IN NUMBER DEFAULT 2, o_err_cod OUT NUMBER, o_err_msg OUT VARCHAR2, cur_out OUT PK_ASC_OBJECTS.asc_return_data ) AS /* Variables locales de trabajo */ v_codusuario NUMBER (10, 0); v_res NUMBER (10, 0); v_exist NUMBER (10, 0); v_select VARCHAR2 (4000); v_err_cod NUMBER; v_type NUMBER; v_password_change SMALLINT; v_set_value VARCHAR2 (254); v_err_msg VARCHAR2 (255); v_cur_out PK_ASC_OBJECTS.asc_return_data; v_label VARCHAR2 (4000); BEGIN OPEN cur_out FOR SELECT NULL FROM DUAL; BEGIN /* Creacion de usuarios */ IF i_operation = 'I' THEN IF i_option = 'A' THEN BEGIN /* Identificacador unico del registro */ ASC_SEQUENCE_ADM ( i_table => 'USUARIOS', o_nextvalue => v_codusuario, o_err_cod => o_err_cod, o_err_msg => o_err_msg ); SELECT FUN_TRYCASTINT (setvalue) INTO v_set_value FROM SETINGENERAL WHERE setid = 2205; --Change password on first login required INSERT INTO USUARIOS ( codusuario, uname, username, codperfil, clave, fecexpiracion, coddepto, address1, address2, address3, phone1, phone2, phone3, ext, fax, mobil, email, codjefeinm, nivelconoc, usertype, casetype, cha_id, usuacomments, ula, nivel, uss_id, countryid, cityid, siteid, buildingid, floorid, companyid, numberdepid, user_active, user_birthdate, user_image, user_language, profileid, identity_type, identity_number, usr_roles, google_address, password_change_date, Latitude, Longitude, Last_Modified_Date ) VALUES ( v_codusuario, i_uname, i_username, i_codperfil, i_clave, i_fecexpiration, i_coddepto, i_address1, i_address2, i_address3, i_phone1, i_phone2, i_phone3, i_ext, i_fax, i_mobil, i_email, i_codjefeinm, i_nivelconoc, i_usertype, i_casetype, i_cha_id, i_usuacomments, i_ula, i_nivel, i_uss_id, i_countryid, i_cityid, i_siteid, i_buildingid, i_floorid, i_companyid, i_numberdepid, i_user_active, i_user_birthdate, i_user_image, i_user_language, i_profileid, i_identity_type, i_identity_number, i_roleid, i_google_address, CASE WHEN v_set_value = 1 THEN NULL ELSE SYSDATE END, i_latitude, i_longitude, SYSDATE ); INSERT INTO ADDITIONALDATA_USER ( codusuario, adu_field1, adu_field2, adu_field3, adu_field4, adu_field5, adu_field6, adu_field7, adu_field8, adu_field9, adu_field10 ) VALUES ( v_codusuario, i_adu_field1, i_adu_field2, i_adu_field3, i_adu_field4, i_adu_field5, i_adu_field6, i_adu_field7, i_adu_field8, i_adu_field9, i_adu_field10 ); IF i_roleid = 1 OR i_roleid = 2 THEN BEGIN INSERT INTO ROLE_USERS ( rol_id, codusuario ) VALUES ( i_roleid, v_codusuario ); END; ELSIF i_roleid = 3 THEN BEGIN INSERT INTO ROLE_USERS ( ROL_ID, CODUSUARIO ) VALUES ( 1, v_codusuario ); INSERT INTO ROLE_USERS ( ROL_ID, CODUSUARIO ) VALUES ( 2, v_codusuario ); END; END IF; io_codusuario := v_codusuario; END; END IF; IF i_option = 'C' THEN INSERT INTO ASDK_USER_VENDOR ( user_id, vendor_id ) VALUES ( io_codusuario, i_companyid ); END IF; IF i_option = 'D' THEN INSERT INTO ASDK_USUARIOXPROJECT ( fl_int_usuario_id, fl_int_project_id ) VALUES ( io_codusuario, i_project_id ); END IF; END IF; /* Operacion de actualizacion */ IF i_operation = 'U' THEN IF i_option = 'A' THEN SELECT FUN_TRYCASTINT (setvalue) INTO v_set_value FROM SETINGENERAL WHERE setid = 2205; --Change password on first login required SELECT CASE WHEN NVL(i_clave, clave) <> clave THEN 1 ELSE 0 END, CASE WHEN i_uname IS NOT NULL AND uname <> i_uname THEN 'UNAME' ELSE '' END INTO v_password_change, v_label FROM USUARIOS WHERE codusuario = io_codusuario; UPDATE USUARIOS SET uname = NVL (i_uname, uname), username = NVL (i_username, username), coddepto = NVL (i_coddepto, coddepto), fecexpiracion = NVL (i_fecexpiration, fecexpiracion), address1 = NVL (i_address1, address1), address2 = NVL (i_address2, address2), address3 = NVL (i_address3, address3), phone1 = NVL (i_phone1, phone1), phone2 = NVL (i_phone2, phone2), phone3 = NVL (i_phone3, phone3), ext = NVL (i_ext, ext), fax = NVL (i_fax, fax), mobil = NVL (i_mobil, mobil), email = NVL (i_email, email), codjefeinm = NVL (i_codjefeinm, codjefeinm), nivelconoc = NVL (i_nivelconoc, nivelconoc), usertype = NVL (i_usertype, usertype), casetype = NVL (i_casetype, casetype), cha_id = NVL (i_cha_id, cha_id), usuacomments = NVL (i_usuacomments, usuacomments), ula = NVL (i_ula, ula), nivel = NVL (i_nivel, nivel), uss_id = NVL (i_uss_id, uss_id), countryid = NVL (i_countryid, countryid), cityid = NVL (i_cityid, cityid), siteid = NVL (i_siteid, siteid), buildingid = NVL (i_buildingid, buildingid), floorid = NVL (i_floorid, floorid), companyid = NVL (i_companyid, companyid), numberdepid = NVL (i_numberdepid, numberdepid), user_birthdate = NVL (i_user_birthdate, user_birthdate), user_language = NVL (i_user_language, user_language), profileid = NVL (i_profileid, profileid), identity_type = NVL (i_identity_type, identity_type), identity_number = NVL (i_identity_number, identity_number), codperfil = NVL (i_codperfil, codperfil), google_address = NVL (i_google_address, google_address), Latitude = NVL(i_latitude, Latitude), Longitude = NVL(i_longitude, Longitude), Last_Modified_Date = SYSDATE, user_image = nvl (decode (i_user_image, 'sinfoto.jpg', null, i_user_image), user_image), clave = nvl (i_clave, clave), password_change_date = CASE WHEN NVL(TRIM(i_clave), clave) <> clave THEN SYSDATE ELSE password_change_date END WHERE CODUSUARIO = io_codusuario; IF v_label IS NOT NULL THEN PRC_ASDK_RULES_LABELS ( i_source_label => v_label ); END IF; UPDATE ADDITIONALDATA_USER SET adu_field1 = NVL (i_adu_field1, adu_field1), adu_field2 = NVL (i_adu_field2, adu_field2), adu_field3 = NVL (i_adu_field3, adu_field3), adu_field4 = NVL (i_adu_field4, adu_field4), adu_field5 = NVL (i_adu_field5, adu_field5), adu_field6 = NVL (i_adu_field6, adu_field6), adu_field7 = NVL (i_adu_field7, adu_field7), adu_field8 = NVL (i_adu_field8, adu_field8), adu_field9 = NVL (i_adu_field9, adu_field9), adu_field10 = NVL (i_adu_field10, adu_field10) WHERE codusuario = io_codusuario; DELETE ROLE_USERS WHERE codusuario = io_codusuario; IF i_roleid = 1 OR i_roleid = 2 THEN INSERT INTO ROLE_USERS ( rol_id, codusuario ) VALUES ( i_roleid, io_codusuario ); ELSIF i_roleid = 3 THEN INSERT INTO ROLE_USERS ( rol_id, codusuario ) VALUES ( 1, io_codusuario ); INSERT INTO ROLE_USERS ( rol_id, codusuario ) VALUES ( 2, io_codusuario ); END IF; IF v_password_change = 1 THEN SELECT FUN_TRYCASTINT (setvalue) INTO v_set_value FROM SETINGENERAL WHERE setid = 2204; --Max number of password history SELECT COUNT (1) INTO v_exist FROM AFW_PASSWORD_HISTORY WHERE user_id = io_codusuario; INSERT INTO AFW_PASSWORD_HISTORY (id, user_id, password, change_date ) SELECT SEQ_AFW_PASSWORD_HISTORY.NEXTVAL, io_codusuario, i_clave, SYSDATE FROM DUAL WHERE NOT EXISTS ( SELECT * FROM AFW_PASSWORD_HISTORY WHERE user_id = io_codusuario AND password = i_clave ); v_exist := v_exist + SQL%ROWCOUNT - ABS (v_set_value); DELETE FROM AFW_PASSWORD_HISTORY WHERE id IN (SELECT id FROM (SELECT id, ROW_NUMBER () OVER (ORDER BY id DESC) row_n FROM AFW_PASSWORD_HISTORY WHERE v_exist > 0 AND user_id = io_codusuario) WHERE row_n > ABS (v_set_value)); COMMIT; END IF; END IF; END IF; IF i_operation = 'D' THEN IF i_option = 'A' THEN PRC_ASS_SEARCH_RESULT ( i_operation => 'D', i_option => 'D', i_sere_user_id => io_codusuario, cur_out => v_cur_out, o_err_cod => v_err_cod, o_err_msg => v_err_msg ); DELETE USUARIOS WHERE codusuario = io_codusuario; END IF; IF i_option = 'B' THEN DELETE ASDK_USER_VENDOR WHERE user_id = io_codusuario; END IF; IF i_option = 'C' THEN DELETE ASDK_USER_VENDOR WHERE user_id = io_codusuario AND vendor_id = i_companyid; END IF; IF i_option = 'D' THEN IF i_active IS NOT NULL THEN UPDATE ASDK_USUARIOXPROJECT SET project_visible = i_active WHERE fl_int_usuario_id = io_codusuario AND fl_int_project_id = i_project_id; ELSE DELETE ASDK_USUARIOXPROJECT WHERE fl_int_usuario_id = io_codusuario AND fl_int_project_id = i_project_id; END IF; END IF; END IF; IF i_operation = 'S' THEN IF i_option = 'A' THEN OPEN cur_out FOR SELECT u.codusuario, uname, username, codperfil, ( SELECT USUARIOS.uname FROM USUARIOS WHERE CODUSUARIO = u.CodJefeInm ) des_jefeinmedato, ( SELECT nomperfil FROM PERFILES WHERE codperfil = u.codperfil ) des_perfil, coddepto, ( SELECT d.code_additional FROM AFW_CATALOG m, AFW_CATALOG_CODE d WHERE m.cata_id = d.code_cata_id AND m.cata_description = 'DEPARTMENT' AND d.code_value = LTRIM(RTRIM(u.coddepto)) ) des_departamento, address1, address2, address3, phone1, phone2, phone3, ext, fax, mobil, email, codjefeinm, nivelconoc, usertype, casetype, cha_id, clave, ( SELECT d.code_additional FROM AFW_CATALOG m, AFW_CATALOG_CODE d WHERE m.cata_id = d.code_cata_id AND m.cata_description = 'CHARGES' AND d.code_value = LTRIM(RTRIM(u.cha_id)) ) des_cargo, usuacomments, ula, nivel, uss_id, ( SELECT USER_STATUS.uss_name FROM USER_STATUS WHERE USER_STATUS.uss_id = u.uss_id) des_user_status, countryid, ( SELECT d.code_additional FROM AFW_CATALOG m, AFW_CATALOG_CODE d WHERE m.cata_id = d.code_cata_id AND m.cata_description = 'COUNTRY' AND d.code_value = LTRIM(RTRIM(u.countryid)) ) des_country, cityid, ( SELECT d.code_additional FROM AFW_CATALOG m, AFW_CATALOG_CODE d WHERE m.cata_id = d.code_cata_id AND m.cata_description = 'CITY' AND d.code_value = LTRIM(RTRIM(u.cityid)) ) des_city, siteid, ( SELECT d.code_additional FROM AFW_CATALOG m, AFW_CATALOG_CODE d WHERE m.cata_id = d.code_cata_id AND m.cata_description = 'SITE' AND d.code_value = LTRIM(RTRIM(u.siteid)) ) des_site, BUILDINGID, ( SELECT d.code_additional FROM AFW_CATALOG m, AFW_CATALOG_CODE d WHERE m.cata_id = d.code_cata_id AND m.cata_description = 'BUILDING' AND d.code_value = LTRIM(RTRIM(u.buildingid)) ) des_building, FLOORID, ( SELECT d.code_additional FROM AFW_CATALOG m, AFW_CATALOG_CODE d WHERE m.cata_id = d.code_cata_id AND m.cata_description = 'FLOOR' AND d.code_value = LTRIM(RTRIM(u.floorid)) ) des_floor, COMPANYID, (SELECT d.code_additional FROM AFW_CATALOG m, AFW_CATALOG_CODE d WHERE m.cata_id = d.code_cata_id AND m.cata_description = 'COMPANY' AND d.code_value = LTRIM(RTRIM(u.companyid)) ) des_company, NUMBERDEPID, ( SELECT d.code_additional FROM AFW_CATALOG m, AFW_CATALOG_CODE d WHERE m.cata_id = d.code_cata_id AND m.cata_description = 'NUMBERDEP' AND d.code_value = LTRIM(RTRIM(u.numberdepid)) ) des_numberdep, USER_ACTIVE, ( SELECT d.code_additional FROM AFW_CATALOG m, AFW_CATALOG_CODE d WHERE m.cata_id = d.code_cata_id AND m.cata_description = 'ACTIVE' AND d.code_value = LTRIM(RTRIM(u.user_active)) ) des_user_active, user_birthdate, user_image, profileid, identity_type, ( SELECT d.code_additional FROM AFW_CATALOG m, AFW_CATALOG_CODE d WHERE m.cata_id = d.code_cata_id AND m.cata_description = 'IDENTIFICATIONTYPE' AND d.code_value = LTRIM(RTRIM(u.identity_type)) ) des_identification, identity_number, adu_field1, adu_field2, adu_field3, adu_field4, adu_field5, adu_field6, adu_field7, adu_field8, adu_field9, adu_field10, FUN_ASDK_ROLE_USER (io_codusuario) role, google_address, Latitude, Longitude, g.group_id special_group_id FROM USUARIOS u LEFT JOIN ADDITIONALDATA_USER a ON u.codusuario = a.codusuario LEFT OUTER JOIN ASDK_SPECIAL_GROUP_USER G ON G.USER_ID = u.CODUSUARIO WHERE u.codusuario = io_codusuario; END IF; IF i_option = 'B' THEN OPEN cur_out FOR WITH cte_shift_users AS ( SELECT user_id, group_id, active, project_id FROM V_ASDK_SHIFT_USERS WHERE project_id = i_project_id ) SELECT u.codusuario, u.username, u.uname FROM USUARIOS u INNER JOIN GROUP_USERS gu ON u.codusuario = gu.codusuario LEFT JOIN cte_shift_users c ON c.user_id = gu.codusuario AND c.GROUP_ID = gu.grp_id WHERE u.user_active = 1 AND u.usr_roles IN (1, 3) AND gu.grp_id = i_profileid AND ( c.user_id IS NULL OR c.active = 1 ) ORDER BY u.uname; END IF; IF i_option = 'C' THEN OPEN cur_out FOR SELECT codperfil, nomperfil FROM PERFILES; END IF; /* Consulta de usuarios asociados a un contrato - ASM */ IF i_option = 'D' THEN v_select := 'SELECT DISTINCT u.codusuario, u.uname, u.username, u.email FROM USUARIOS u WHERE ' || i_fl_str_query || ' AND u.user_active = 1 ORDER BY u.uname '; OPEN cur_out FOR v_select; END IF; /* Opcion para Listar todos los usuarios */ IF i_option = 'E' THEN OPEN cur_out FOR SELECT u.codusuario, uname, username, codperfil, ( SELECT d.code_additional FROM AFW_CATALOG m, AFW_CATALOG_CODE d WHERE m.cata_id = d.code_cata_id AND m.cata_description = 'PERFIL' AND d.code_value = LTRIM(RTRIM(u.IDENTITY_TYPE)) ) des_perfil, coddepto, ( SELECT d.code_additional FROM AFW_CATALOG m, AFW_CATALOG_CODE d WHERE m.cata_id = d.code_cata_id AND m.cata_description = 'DEPARTMENT' AND d.code_value = LTRIM(RTRIM(u.IDENTITY_TYPE)) ) des_departamento, address1, address2, address3, phone1, phone2, phone3, ext, fax, mobil, email, codjefeinm, nivelconoc, usertype, casetype, cha_id, ( SELECT d.code_additional FROM AFW_CATALOG m, AFW_CATALOG_CODE d WHERE m.cata_id = d.code_cata_id AND m.cata_description = 'CHARGES' AND d.code_value = LTRIM(RTRIM(u.identity_type)) ) des_cargo, usuacomments, ula, nivel, uss_id, ( SELECT d.code_additional FROM AFW_CATALOG m, AFW_CATALOG_CODE d WHERE m.cata_id = d.code_cata_id AND m.cata_description = 'USER_STATUS' AND d.code_value = LTRIM(RTRIM(u.identity_type)) ) des_user_status, COUNTRYID, ( SELECT d.code_additional FROM AFW_CATALOG m, AFW_CATALOG_CODE d WHERE m.cata_id = d.code_cata_id AND m.cata_description = 'COUNTRY' AND d.code_value = LTRIM(RTRIM(u.identity_type)) ) des_country, CITYID, ( SELECT d.code_additional FROM AFW_CATALOG m, AFW_CATALOG_CODE d WHERE m.cata_id = d.code_cata_id AND m.cata_description = 'CITY' AND d.code_value = LTRIM(RTRIM(u.identity_type)) ) des_city, SITEID, ( SELECT d.code_additional FROM AFW_CATALOG m, AFW_CATALOG_CODE d WHERE m.cata_id = d.code_cata_id AND m.cata_description = 'SITE' AND d.code_value = LTRIM(RTRIM(u.identity_type)) ) des_site, BUILDINGID, ( SELECT d.code_additional FROM AFW_CATALOG m, AFW_CATALOG_CODE d WHERE m.cata_id = d.code_cata_id AND m.cata_description = 'BUILDING' AND d.code_value = LTRIM(RTRIM(u.Identity_type)) ) des_building, FLOORID, ( SELECT d.code_additional FROM AFW_CATALOG m, AFW_CATALOG_CODE d WHERE m.cata_id = d.code_cata_id AND m.cata_description = 'FLOOR' AND d.code_value = LTRIM(RTRIM(u.identity_type)) ) des_floor, COMPANYID, ( SELECT d.code_additional FROM AFW_CATALOG m, AFW_CATALOG_CODE d WHERE m.cata_id = d.code_cata_id AND m.cata_description = 'COMPANY' AND d.code_value = LTRIM(RTRIM(u.identity_type)) ) des_company, NUMBERDEPID, ( SELECT d.code_additional FROM AFW_CATALOG m, AFW_CATALOG_CODE d WHERE m.cata_id = d.code_cata_id AND m.cata_description = 'NUMBERDEP' AND d.code_value = LTRIM(RTRIM(u.identity_type)) ) des_numberdep, USER_ACTIVE, ( SELECT d.code_additional FROM AFW_CATALOG m, AFW_CATALOG_CODE d WHERE m.cata_id = d.code_cata_id AND m.cata_description = 'ACTIVE' AND d.code_value = LTRIM(RTRIM(u.identity_type)) ) des_user_active, user_birthdate, user_image, profileid, ( SELECT d.code_additional FROM AFW_CATALOG m, AFW_CATALOG_CODE d WHERE m.cata_id = d.code_cata_id AND m.cata_description = 'IDENTIFICATIONTYPE' AND d.code_value = LTRIM(RTRIM(u.identity_type)) ) des_identification, identity_number, adu_field1, adu_field2, adu_field3, adu_field4, adu_field5, adu_field6, adu_field7, adu_field8, adu_field9, adu_field10 FROM USUARIOS U LEFT JOIN ADDITIONALDATA_USER a ON u.codusuario = a.codusuario; END IF; IF i_option = 'F' THEN OPEN cur_out FOR SELECT DISTINCT USUARIOS.codusuario, USUARIOS.username, USUARIOS.uname FROM USUARIOS INNER JOIN GROUP_USERS ON USUARIOS.codusuario = GROUP_USERS.codusuario INNER JOIN ASDK_GROUPSXPROJECT ON GROUP_USERS.grp_id = ASDK_GROUPSXPROJECT.fl_int_group_id WHERE USUARIOS.user_active = 1 AND ASDK_GROUPSXPROJECT.fl_int_project_id = i_project_id ORDER BY USUARIOS.uname; END IF; IF i_option = 'G' THEN OPEN cur_out FOR SELECT USUARIOS.codusuario, USUARIOS.uname, USUARIOS.username, USUARIOS.user_active, USUARIOS.codperfil, USUARIOS.email FROM USUARIOS INNER JOIN ASDK_USUARIOXPROJECT ON USUARIOS.codusuario = ASDK_USUARIOXPROJECT.fl_int_usuario_id WHERE ASDK_USUARIOXPROJECT.fl_int_project_id = i_project_id ORDER BY USUARIOS.uname; END IF; IF i_option = 'H' THEN OPEN cur_out FOR SELECT uss_id, uss_name FROM USER_STATUS; END IF; IF i_option = 'I' THEN -- Consulta los usuarios asociados a una compa?ia, recibiendo como parametro el id del vendor OPEN cur_out FOR SELECT codusuario, uname, username FROM USUARIOS, ASDK_USER_VENDOR WHERE ASDK_USER_VENDOR.user_id = USUARIOS.codusuario AND ASDK_USER_VENDOR.vendor_id = i_companyid; END IF; IF i_option = 'J' THEN OPEN cur_out FOR SELECT a.flin_id, a.flin_column_name, FN_ASC_LABELS(a.column_caption, i_language) AS flin_column_caption, a.flin_name_table, a.flin_mandatory flin_mandatory_master, '0' CheckMandatory, CASE b.fl_int_project_id WHEN i_project_id THEN NULL ELSE b.fl_str_caption END Caption, CASE WHEN b.fl_int_project_id = i_project_id AND a.flin_mandatory = 1 THEN 1 WHEN b.fl_int_project_id = i_project_id THEN b.flin_mandatory ELSE NULL END flin_mandatory_detail, CASE b.fl_int_project_id WHEN i_project_id THEN b.fl_int_field_id ELSE NULL END fl_int_field_id, a.fl_str_caption flin_caption_master, ( CASE WHEN EXISTS ( SELECT * FROM ASDK_CONCEPT_FIELD cf, ASDK_CONCEPT_FIELD_TRANSLATION ft WHERE cf.cfi_int_id = ft.cfi_int_id AND cf.cfi_str_interface_field_name = UPPER('USUARIOS_' || a.flin_column_name) AND ft.project_id = i_project_id AND ft.lang_id = i_language ) THEN ( SELECT ft.cft_str_translation FROM ASDK_CONCEPT_FIELD cf, ASDK_CONCEPT_FIELD_TRANSLATION ft WHERE cf.cfi_int_id = ft.cfi_int_id AND cf.cfi_str_interface_field_name = UPPER ('USUARIOS_' || a.flin_column_name) AND ft.project_id = i_project_id AND ft.lang_id = i_language AND rownum = 1) ELSE CASE b.fl_int_project_id WHEN i_project_id THEN b.fl_str_caption ELSE NULL END END ) AS flin_caption_detail, CASE b.fl_int_project_id WHEN i_project_id THEN b.fl_int_visible ELSE NULL END fl_int_visible, CASE b.fl_int_project_id WHEN i_project_id THEN b.fl_int_order_field ELSE NULL END fl_int_order_field, CASE b.fl_int_project_id WHEN i_project_id THEN i_project_id ELSE NULL END Project_id, column_caption FROM AFW_FIELDS_INTERFACE A LEFT JOIN AFW_INTERFACE_PROJECTS b ON b.fl_int_field_id = a.flin_id WHERE flin_name_table = 'USUARIOS' AND fl_int_project_id = i_project_id ORDER BY flin_caption_detail; END IF; IF i_option = 'K' THEN OPEN cur_out FOR SELECT a.flin_id, a.flin_column_name, FN_ASC_LABELS(a.column_caption, i_language) flin_column_caption, a.flin_name_table, a.flin_mandatory flin_mandatory_master, '0' CheckMandatory, NULL Caption, 0 flin_mandatory_detail, a.flin_id fl_int_field_id, a.fl_str_caption flin_caption_master, FN_ASC_LABELS (a.column_caption, i_language) flin_caption_detail, 1 fl_int_visible, NULL fl_int_order_field, NULL Project_id, column_caption FROM AFW_FIELDS_INTERFACE A WHERE flin_name_table = 'USUARIOS' ORDER BY a.flin_id; END IF; IF i_option = 'L' THEN IF i_filter IS NULL OR LTRIM(RTRIM(i_filter)) = '' OR i_filter = '1=1' OR i_filter = '1 = 1' THEN v_select := 'SELECT DISTINCT USUARIOS.codusuario, USUARIOS.uname, USUARIOS.username, USUARIOS.email FROM USUARIOS LEFT OUTER JOIN ADDITIONALDATA_USER ON USUARIOS.codusuario = ADDITIONALDATA_USER.codusuario WHERE USUARIOS.user_active = 1 AND rownum <= 1000 ORDER BY USUARIOS.uname'; OPEN cur_out FOR v_select; ELSE v_select := 'SELECT DISTINCT USUARIOS.codusuario, USUARIOS.uname, USUARIOS.username, USUARIOS.email FROM USUARIOS LEFT OUTER JOIN ADDITIONALDATA_USER ON USUARIOS.codusuario = ADDITIONALDATA_USER.codusuario WHERE ' || i_filter || ' AND USUARIOS.user_active = 1 ORDER BY USUARIOS.uname'; OPEN cur_out FOR v_select; END IF; END IF; IF i_option = 'M' THEN OPEN cur_out FOR SELECT USUARIOS.codusuario, USUARIOS.uname, USUARIOS.username, USUARIOS.usertype FROM USUARIOS INNER JOIN ROLE_USERS ON ROLE_USERS.codusuario = USUARIOS.codusuario WHERE USUARIOS.user_active = 1 AND ROLE_USERS.rol_id = i_roleid ORDER BY USUARIOS.codusuario; END IF; IF i_option = 'N' THEN IF i_filter IS NULL OR LTRIM(RTRIM(i_filter)) = '' OR i_filter = '1=1' OR i_filter = '1 = 1' THEN v_select := 'SELECT DISTINCT USUARIOS.codusuario, USUARIOS.uname, USUARIOS.username, USUARIOS.email FROM USUARIOS LEFT OUTER JOIN ADDITIONALDATA_USER ON USUARIOS.codusuario = ADDITIONALDATA_USER.codusuario INNER JOIN ASDK_USUARIOXPROJECT ON USUARIOS.codusuario = ASDK_USUARIOXPROJECT.fl_int_usuario_id LEFT OUTER JOIN ASDK_USER_VENDOR on ASDK_USER_VENDOR.user_id = USUARIOS.codusuario WHERE USUARIOS.user_active = 1 AND rownum <= 1000 ORDER BY USUARIOS.uname'; OPEN cur_out FOR v_select; ELSE v_select := 'SELECT DISTINCT USUARIOS.codusuario, USUARIOS.uname, USUARIOS.username, USUARIOS.email FROM USUARIOS LEFT OUTER JOIN ADDITIONALDATA_USER ON USUARIOS.codusuario = ADDITIONALDATA_USER.codusuario INNER JOIN ASDK_USUARIOXPROJECT ON USUARIOS.codusuario = ASDK_USUARIOXPROJECT.fl_int_usuario_id LEFT OUTER JOIN ASDK_USER_VENDOR on ASDK_USER_VENDOR.user_id = USUARIOS.codusuario WHERE ' || i_filter || ' AND USUARIOS.user_active = 1 ORDER BY USUARIOS.uname'; OPEN cur_out FOR v_select; END IF; END IF; IF i_option = 'O' THEN IF i_filter IS NULL OR LTRIM(RTRIM(i_filter)) = '' OR i_filter = '1=1' OR i_filter = '1 = 1' THEN v_select := 'SELECT DISTINCT USUARIOS.codusuario, USUARIOS.uname, USUARIOS.username, USUARIOS.email FROM USUARIOS LEFT OUTER JOIN ADDITIONALDATA_USER ON USUARIOS.codusuario = ADDITIONALDATA_USER.codusuario INNER JOIN GROUP_USERS ON USUARIOS.codusuario = GROUP_USERS.codusuario INNER JOIN ASDK_GROUPSXPROJECT ON GROUP_USERS.grp_id = ASDK_GROUPSXPROJECT.fl_int_group_id WHERE USUARIOS.user_active = 1 AND rownum <= 1000 ORDER BY USUARIOS.uname'; OPEN cur_out FOR v_select; ELSE v_select := 'SELECT DISTINCT USUARIOS.codusuario, USUARIOS.uname, USUARIOS.username, USUARIOS.email FROM USUARIOS LEFT OUTER JOIN ADDITIONALDATA_USER ON USUARIOS.codusuario = ADDITIONALDATA_USER.codusuario INNER JOIN GROUP_USERS ON USUARIOS.codusuario = GROUP_USERS.codusuario INNER JOIN ASDK_GROUPSXPROJECT ON GROUP_USERS.grp_id = ASDK_GROUPSXPROJECT.fl_int_group_id WHERE ' || i_filter || ' AND USUARIOS.user_active = 1 ORDER BY USUARIOS.uname'; OPEN cur_out FOR v_select; END IF; END IF; IF i_option = 'P' THEN IF i_filter IS NULL OR LTRIM(RTRIM(i_filter)) = '' OR i_filter = '1=1' OR i_filter = '1 = 1' THEN v_select := 'SELECT DISTINCT USUARIOS.codusuario, USUARIOS.uname, USUARIOS.username, USUARIOS.email FROM USUARIOS WHERE USUARIOS.codusuario IN ( SELECT USUARIOS.codusuario FROM USUARIOS LEFT OUTER JOIN ADDITIONALDATA_USER ON USUARIOS.codusuario = ADDITIONALDATA_USER.codusuario INNER JOIN ASDK_USUARIOXPROJECT ON USUARIOS.codusuario = ASDK_USUARIOXPROJECT.fl_int_usuario_id WHERE USUARIOS.user_active = 1 UNION SELECT USUARIOS.codusuario FROM USUARIOS LEFT OUTER JOIN ADDITIONALDATA_USER ON USUARIOS.codusuario = ADDITIONALDATA_USER.codusuario INNER JOIN GROUP_USERS ON USUARIOS.codusuario = GROUP_USERS.codusuario INNER JOIN ASDK_GROUPSXPROJECT ON GROUP_USERS.grp_id = ASDK_GROUPSXPROJECT.fl_int_group_id WHERE USUARIOS.user_active = 1 AND rownum <= 1000 ) ORDER BY USUARIOS.uname'; OPEN cur_out FOR v_select; ELSE v_select := 'SELECT DISTINCT USUARIOS.codusuario, USUARIOS.uname, USUARIOS.username, USUARIOS.email FROM USUARIOS WHERE USUARIOS.codusuario IN ( SELECT USUARIOS.codusuario FROM USUARIOS LEFT OUTER JOIN ADDITIONALDATA_USER ON USUARIOS.codusuario = ADDITIONALDATA_USER.codusuario INNER JOIN ASDK_USUARIOXPROJECT ON USUARIOS.codusuario = ASDK_USUARIOXPROJECT.fl_int_usuario_id WHERE ' || i_filter || ' AND USUARIOS.user_active = 1 UNION SELECT USUARIOS.codusuario FROM USUARIOS LEFT OUTER JOIN ADDITIONALDATA_USER ON USUARIOS.codusuario = ADDITIONALDATA_USER.codusuario INNER JOIN GROUP_USERS ON USUARIOS.codusuario = GROUP_USERS.codusuario INNER JOIN ASDK_GROUPSXPROJECT ON GROUP_USERS.grp_id = ASDK_GROUPSXPROJECT.fl_int_group_id WHERE ' || i_filter || ' AND USUARIOS.user_active = 1 ) ORDER BY USUARIOS.uname'; OPEN cur_out FOR v_select; END IF; END IF; IF i_option = 'Q' THEN OPEN cur_out FOR SELECT codusuario, uname, username FROM USUARIOS; END IF; IF i_option = 'R' THEN IF i_filter IS NULL OR LTRIM(RTRIM(i_filter)) = '' OR i_filter = '1=1' OR i_filter = '1 = 1' THEN v_select := 'SELECT USUARIOS.codusuario, USUARIOS.uname, USUARIOS.username, USUARIOS.email FROM USUARIOS WHERE USUARIOS.codusuario IN ( SELECT USUARIOS.codusuario FROM USUARIOS INNER JOIN GROUP_USERS ON USUARIOS.codusuario = GROUP_USERS.codusuario INNER JOIN ASDK_GROUPSXPROJECT ON GROUP_USERS.grp_id = ASDK_GROUPSXPROJECT.fl_int_group_id WHERE USUARIOS.user_active = 1 AND rownum <= 1000 AND ASDK_GROUPSXPROJECT.fl_int_project_id = ' || TO_CHAR (i_project_id) || ' UNION SELECT USUARIOS.codusuario FROM USUARIOS INNER JOIN ASDK_USUARIOXPROJECT ON USUARIOS.codusuario = ASDK_USUARIOXPROJECT.fl_int_usuario_id WHERE USUARIOS.user_active = 1 AND rownum <= 1000 AND ASDK_USUARIOXPROJECT.fl_int_project_id = ' || TO_CHAR (i_project_id) || ' ) ORDER BY UNAME'; OPEN cur_out FOR v_select; ELSE v_select := 'SELECT USUARIOS.codusuario, USUARIOS.uname, USUARIOS.username, USUARIOS.email FROM USUARIOS WHERE USUARIOS.codusuario IN ( SELECT USUARIOS.codusuario FROM USUARIOS INNER JOIN GROUP_USERS ON USUARIOS.codusuario = GROUP_USERS.codusuario INNER JOIN ASDK_GROUPSXPROJECT ON GROUP_USERS.grp_id = ASDK_GROUPSXPROJECT.fl_int_group_id WHERE ' || i_filter || ' AND USUARIOS.user_active = 1 AND rownum <= 1000 AND ASDK_GROUPSXPROJECT.fl_int_project_id = ' || TO_CHAR (i_project_id) || ' UNION SELECT USUARIOS.codusuario FROM USUARIOS INNER JOIN ASDK_USUARIOXPROJECT ON USUARIOS.codusuario = ASDK_USUARIOXPROJECT.fl_int_usuario_id WHERE ' || i_filter || ' AND USUARIOS.user_active = 1 AND rownum <= 1000 AND ASDK_USUARIOXPROJECT.fl_int_project_id = ' || TO_CHAR (i_project_id) || ') ORDER BY UNAME'; OPEN cur_out FOR v_select; END IF; END IF; IF i_option = 'S' THEN OPEN cur_out FOR SELECT USUARIOS.codusuario, USUARIOS.uname, USUARIOS.username, USUARIOS.user_active, USUARIOS.codperfil, USUARIOS.email FROM USUARIOS INNER JOIN GROUP_USERS ON USUARIOS.codusuario = GROUP_USERS.codusuario INNER JOIN ASDK_GROUPSXPROJECT ON GROUP_USERS.grp_id = ASDK_GROUPSXPROJECT.fl_int_group_id WHERE USUARIOS.user_active = 1 AND ASDK_GROUPSXPROJECT.fl_int_project_id = i_project_id UNION SELECT USUARIOS.codusuario, USUARIOS.uname, USUARIOS.username, USUARIOS.user_active, USUARIOS.codperfil, USUARIOS.email FROM USUARIOS INNER JOIN ASDK_USUARIOXPROJECT ON USUARIOS.codusuario = ASDK_USUARIOXPROJECT.fl_int_usuario_id WHERE USUARIOS.user_active = 1 AND ASDK_USUARIOXPROJECT.fl_int_project_id = i_project_id ORDER BY UNAME; END IF; IF i_option = 'T' THEN IF i_casetype = 1 THEN SELECT setvalue INTO v_type FROM ASDK_PROJECT_SETTINGS WHERE setid = 1 AND fl_int_project_id = i_project_id; END IF; IF i_casetype = 2 THEN SELECT setvalue INTO v_type FROM ASDK_PROJECT_SETTINGS WHERE setid = 34 AND fl_int_project_id = i_project_id; END IF; IF i_casetype = 4 THEN SELECT setvalue INTO v_type FROM ASDK_PROJECT_SETTINGS WHERE setid = 21 AND fl_int_project_id = i_project_id; END IF; IF i_casetype = 3 THEN SELECT setvalue INTO v_type FROM ASDK_PROJECT_SETTINGS WHERE setid = 7 AND fl_int_project_id = i_project_id; END IF; IF v_type = 3 THEN OPEN cur_out FOR SELECT USUARIOS.codusuario FROM grouphd INNER JOIN USUARIOS ON GROUPHD.GRP_USER = USUARIOS.codusuario WHERE grp_id = i_profileid AND USUARIOS.user_active = 1 ORDER BY USUARIOS.uname; ELSE OPEN cur_out FOR SELECT USUARIOS.codusuario, USUARIOS.uname, USUARIOS.username, USUARIOS.user_active, USUARIOS.codperfil, USUARIOS.email FROM USUARIOS INNER JOIN ASDK_USUARIOXPROJECT ON USUARIOS.codusuario = ASDK_USUARIOXPROJECT.fl_int_usuario_id WHERE ASDK_USUARIOXPROJECT.fl_int_project_id = i_project_id AND rownum = 1 ORDER BY USUARIOS.uname; END IF; END IF; IF i_option = 'V' THEN OPEN cur_out FOR SELECT FUN_AFW_NEEDED_PASSWORD_CHANGE (io_codusuario) change_password_required FROM DUAL; END IF; IF i_option = 'W' THEN v_set_value := FUN_AFW_CHANGE_PASSWORD_DENIED (io_codusuario, i_clave); OPEN cur_out FOR SELECT TO_NUMBER(SUBSTR(v_set_value, 0, INSTR (v_set_value, '|', 0) + 1)) change_password_denied, TO_NUMBER(REPLACE(v_set_value, SUBSTR(v_set_value, 0, INSTR(v_set_value, '|', 0) + 1) || '|', '')) value FROM DUAL; END IF; END IF; IF i_option = 'U' THEN OPEN cur_out FOR SELECT SUM (AMOUNT) AS amount FROM ( SELECT COUNT (1) AS amount FROM ASDK_INCIDENT WHERE inci_responsible_id = io_codusuario AND inci_final_status = 1 UNION SELECT COUNT (1) AS amount FROM ASDK_CHANGE WHERE chan_responsible_id = io_codusuario AND chan_final_status = 1 UNION SELECT COUNT (1) AS amount FROM ASDK_PROBLEM WHERE prob_responsible_id = io_codusuario AND prob_final_status = 1 UNION SELECT COUNT (1) AS amount FROM ASDK_SERVICE_CALL WHERE serv_responsible_id = io_codusuario AND serv_final_status = 1 UNION SELECT COUNT (1) AS amount FROM ASDK_TASK JOIN ASDK_CHANGE_TASK ON ASDK_TASK.id_task = ASDK_CHANGE_TASK.task_id JOIN ASDK_CHANGE ON ASDK_CHANGE_TASK.chan_id = ASDK_CHANGE.chan_id WHERE RESPONSABLEID = io_codusuario AND FUN_ASDK_STATUS_CHILD (task_status) > 0 UNION SELECT COUNT (1) AS amount FROM ASDK_TASK JOIN ASDK_INCIDENT_TASK ON ASDK_TASK.id_task = ASDK_INCIDENT_TASK.task_id JOIN ASDK_INCIDENT ON ASDK_INCIDENT_TASK.inci_id = ASDK_INCIDENT.inci_id WHERE responsableid = io_codusuario AND FUN_ASDK_STATUS_CHILD (task_status) > 0 UNION SELECT COUNT (1) AS amount FROM ASDK_TASK JOIN ASDK_SERVICECALL_TASK ON ASDK_TASK.id_task = ASDK_SERVICECALL_TASK.task_id JOIN ASDK_SERVICE_CALL ON ASDK_SERVICE_CALL.serv_id = ASDK_SERVICECALL_TASK.serv_id WHERE responsableid = io_codusuario AND FUN_ASDK_STATUS_CHILD (task_status) > 0 UNION SELECT COUNT (1) AS amount FROM ASDK_TASK JOIN ASDK_PROBLEM_TASK ON ASDK_TASK.id_task = ASDK_PROBLEM_TASK.task_id JOIN ASDK_PROBLEM ON ASDK_PROBLEM_TASK.prob_id = ASDK_PROBLEM.prob_id WHERE RESPONSABLEID = io_codusuario AND FUN_ASDK_STATUS_CHILD (task_status) > 0 UNION SELECT COUNT (1) AS amount FROM ASDK_VOTING WHERE voti_user_id = io_codusuario UNION SELECT COUNT (1) AS amount FROM ASS_ARTICLE LEFT JOIN ASS_PROJECT_ARTICLE ON ASS_ARTICLE.fl_int_solution_id = ASS_PROJECT_ARTICLE.prar_solution_id WHERE fl_int_responsible_id = io_codusuario UNION SELECT COUNT (1) AS amount FROM GROUP_USERS WHERE CODUSUARIO = io_codusuario ) thetable; END IF; IF i_option = 'Z' THEN OPEN cur_out FOR WITH USERS AS ( SELECT USER_ID, GROUP_ID, ACTIVE, PROJECT_ID FROM V_ASDK_SHIFT_USERS WHERE PROJECT_ID = i_project_id ) SELECT U.CODUSUARIO, U.USERNAME, U.UNAME FROM USUARIOS U INNER JOIN GROUP_USERS GU ON U.CODUSUARIO = GU.CODUSUARIO LEFT JOIN USERS C ON C.USER_ID = GU.CODUSUARIO AND C.GROUP_ID = GU.GRP_ID WHERE U.USER_ACTIVE = 1 AND U.USR_ROLES IN(1,3) AND GU.GRP_ID = i_profileid AND (C.USER_ID IS NULL OR C.ACTIVE = 1) AND U.CODUSUARIO NOT IN (SELECT "UserId" FROM AFW_CHAT_PERSON INNER JOIN AFW_CHAT_PROJECT_AGENT ON AFW_CHAT_PROJECT_AGENT."PersonId" = AFW_CHAT_PERSON."Id" AND AFW_CHAT_PROJECT_AGENT."Active" = 1 INNER JOIN AFW_CHAT_PROJECT ON AFW_CHAT_PROJECT."Id" = AFW_CHAT_PROJECT_AGENT."ProjectId" AND AFW_CHAT_PROJECT."CodeProject" = i_project_id) ORDER BY U.UNAME; END IF; END; EXCEPTION WHEN OTHERS THEN EXCEPTION_HANDLER ( i_procedure_operation => i_operation, i_procedure_option => i_option, i_error_sentence => 'STATIC', i_error_procedure => 'PRC_AFW_USERS', o_return_number => o_err_cod, o_return_message => o_err_msg ); END PRC_AFW_USERS;