create or replace package pkg_eligibility is -- Author : Caio Luppi -- Purpose : Eligibility process database interactions COMM_TYPE_RESIDENTIAL constant varchar2(12) := 'RESIDENTIAL'; COMM_TYPE_MAILING constant varchar2(8) := 'MAILING'; COMM_TYPE_HOME_PHONE constant varchar2(10) := 'HOMEPHONE'; COMM_TYPE_WORK_PHONE constant varchar2(10) := 'WORKPHONE'; DEFAUL_QUESTION_VALUE constant number := 0; DEFAULT_PURPOSE_SELECTED constant number := 0; IMAGE_TYPE_SYSTEM_IMAGE constant varchar2(17) := 'PhotoSystemImage'; SELECTED_IMAGE_ID constant number := 1; SELECTED_OPTIN_ID constant number := 1; SELECTED_NEWS_ID constant number := 1; /** * Gets the next valid wnp id for a given company */ function get_next_wnp_id(p_company_id in varchar2, p_relashionship_code in varchar2) return varchar2; /** * Creates or updates a member with the provided info. */ procedure set_eligibility_member(p_company_id in varchar2, p_family_id in varchar2, p_employee_network_id in out varchar2, p_member_first_name in varchar2, p_member_last_name in varchar2, p_middle_initial in varchar2, p_date_of_birth in date, p_gender in varchar2, p_marital_status_code in varchar2, p_relationship_code in varchar2, p_is_active in varchar2, p_nsh in varchar2, p_residential_address1 in varchar2, p_residential_address2 in varchar2, p_residential_city in varchar2, p_residential_state in varchar2, p_residential_zip in varchar2, p_residential_country in varchar2, p_mailing_address1 in varchar2, p_mailing_address2 in varchar2, p_mailing_city in varchar2, p_mailing_state in varchar2, p_mailing_zip in varchar2, p_mailing_country in varchar2, p_emp_alternt_id in varchar2, p_home_phone_number in varchar2, p_home_pref_phone_ind in varchar2, p_work_phone_number in varchar2, p_work_pref_phone_ind in varchar2, p_email_address in varchar2, p_preferred_email_ind in varchar2, p_email_address_ind in varchar2, p_email_is_primary in varchar2, p_ssn in varchar2, p_branch_id in varchar2, p_employment_start_date in date, p_employment_end_date in date, p_eligibility_start_date in date, p_eligibility_end_date in date, p_coverage_efective_date in date, p_coverage_termination_date in date, p_custom1 in varchar2, p_custom2 in varchar2, p_custom3 in varchar2, p_custom4 in varchar2, p_custom5 in varchar2, p_custom6 in varchar2, p_custom7 in varchar2, p_custom8 in varchar2, p_custom9 in varchar2, p_custom10 in varchar2, p_email_changed out varchar2, p_phone_changed out varchar2, p_user_created out varchar2, p_wnp_id out varchar2); end pkg_eligibility; / create or replace package body pkg_eligibility is /** * Gets the next valid wnp id for a given company */ function get_next_wnp_id(p_company_id in varchar2, p_relashionship_code in varchar2) return varchar2 is v_wnp_id wnp_member_mstr.wnp_id%type; v_company_star_code wnp_adm_company_profile.company_star_code%type; begin -- gets the next wnp id select to_number(max(substr(wnp_id, 2, 7))) into v_wnp_id from wnp_member_mstr where company_id = p_company_id; -- gets the company star code select company_star_code into v_company_star_code from wnp_adm_company_profile where company_id = p_company_id; -- if it's the first record, set zero if (v_wnp_id is null) then v_wnp_id := 0; end if; v_wnp_id := v_company_star_code || lpad(v_wnp_id + 1, 7, '0') || p_relashionship_code; return v_wnp_id; end get_next_wnp_id; /** * Returns the wnp_id for a given user, if it exists. */ function get_user_wnp_id(p_company_id in varchar2, p_relationship_id in number, p_family_id in varchar2) return varchar2 is v_wnp_id wnp_member_mstr.wnp_id%type; begin -- try to get the wnp_id if the user exists begin select min(m.wnp_id) into v_wnp_id from wnp_member_mstr m, wnp_member_ids i where m.wnp_id = i.wnp_id and m.company_id = p_company_id and m.relationship_id = p_relationship_id and i.family_id = p_family_id; exception when no_data_found then -- if there is no user with the given information, let's return null v_wnp_id := null; end; return v_wnp_id; end get_user_wnp_id; /** * Returns a relashionship id for a given code. */ function get_relashionship_id(p_relationship_code in varchar2) return wnp_relationship_mstr.relationship_id%type is v_relationship_id wnp_relationship_mstr.relationship_id%type; begin -- try to get the wnp_id if the user exists begin select relationship_id into v_relationship_id from wnp_relationship_mstr where relationship = p_relationship_code; exception when no_data_found then raise_application_error(-20001, 'Invalid relashionship code: ' || p_relationship_code); end; return v_relationship_id; end get_relashionship_id; /** * Returns a marital status id for a given code. */ function get_marital_status_id(p_marital_status_code in varchar2) return wnp_marital_status_mstr.marital_status_id%type is v_marital_status_id wnp_marital_status_mstr.marital_status_id%type; begin begin if (p_marital_status_code is not null) then select marital_status_id into v_marital_status_id from wnp_marital_status_mstr where marital_status = p_marital_status_code; else v_marital_status_id := null; end if; exception when no_data_found then raise_application_error(-20001, 'Invalid marital status code: ' || p_marital_status_code); end; return v_marital_status_id; end get_marital_status_id; /** * Gets the communication type id. If it doesn't exist, creates it. */ function get_communication_type_id(p_communication_type in varchar2) return varchar2 is v_communication_type_id wnp_member_address.communication_type_id%type; begin -- gets the communication type id begin select min(wct.communication_type_id) into v_communication_type_id from wnp_communication_type_mstr wct where wct.communication_type = p_communication_type; exception when no_data_found then -- if it doesn't exist creates one. select communication_type_id_seq.nextval into v_communication_type_id from dual; insert into wnp_communication_type_mstr (communication_type_id, communication_type) values (v_communication_type_id, p_communication_type); end; return v_communication_type_id; end get_communication_type_id; /** * Saves the user's address. */ procedure save_address(p_wnp_id in varchar2, p_address1 in varchar2, p_address2 in varchar2, p_city in varchar2, p_state in varchar2, p_zip in varchar2, p_country in varchar2, p_communication_type in varchar2) is v_communication_type_id wnp_member_address.communication_type_id%type; v_address_id wnp_member_address.address_id%type; begin v_communication_type_id := get_communication_type_id(p_communication_type); begin -- gets the address id select wma.address_id into v_address_id from wnp_member_address wma where wma.communication_type_id = v_communication_type_id and wma.wnp_id = p_wnp_id; -- if it exists, let's update it update wnp_address_mstr set address1 = p_address1, address2 = p_address2, city = p_city, state = p_state, zip = p_zip, country = p_country where address_id = v_address_id; exception when no_data_found then select address_id_seq.nextval into v_address_id from dual; -- if it doesn't exist creates one. insert into wnp_address_mstr (address_id, address1, address2, city, state, zip, country) values (v_address_id, p_address1, p_address2, p_city, p_state, p_zip, p_country); -- finally associate the address and communication type to the user... insert into wnp_member_address (wnp_id, address_id, communication_type_id) values (p_wnp_id, v_address_id, v_communication_type_id); end; end save_address; /** * Saves the user's phone. */ function save_phone(p_wnp_id in varchar2, p_phone_number in varchar2, p_preferred_phone_ind in varchar2, p_communication_type in varchar2) return boolean is v_communication_type_id wnp_member_address.communication_type_id%type; v_phone_id wnp_member_phone.phone_id%type; v_phone_number wnp_phone_mstr.phone_number%type; v_changed_phone boolean := false; begin v_communication_type_id := get_communication_type_id(p_communication_type); begin -- gets the phone id select wmt.phone_id, wpm.phone_number into v_phone_id, v_phone_number from wnp_member_phone wmt, wnp_phone_mstr wpm where wmt.communication_type_id = v_communication_type_id and wmt.phone_id = wpm.phone_id and wmt.wnp_id = p_wnp_id; -- sets if the phone number has changed if (v_phone_number <> p_phone_number) then v_changed_phone := true; end if; -- if it exists, let's update it update wnp_member_phone set preferred_phone_ind = p_preferred_phone_ind, communication_type_id = v_communication_type_id where wnp_id = p_wnp_id and phone_id = v_phone_id; -- and update the phone number update wnp_phone_mstr set phone_number = p_phone_number where phone_id = v_phone_id; exception when no_data_found then select phone_id_seq.nextval into v_phone_id from dual; -- if it doesn't exist creates one. insert into wnp_phone_mstr (phone_id, phone_number) values (v_phone_id, p_phone_number); -- finally associate the phone and communication type to the user... insert into wnp_member_phone (wnp_id, phone_id, preferred_phone_ind, communication_type_id) values (p_wnp_id, v_phone_id, p_preferred_phone_ind, v_communication_type_id); end; return v_changed_phone; end save_phone; /** * Saves the user's e-mail. */ function save_email(p_wnp_id in varchar2, p_email_address in varchar2, p_preferred_email_ind in varchar2, p_email_address_ind in varchar2, p_is_primary in varchar2) return boolean is v_email_id wnp_member_emails.email_unique_id%type; v_email_address wnp_member_emails.email_address%type; v_changed_email boolean := false; begin begin -- gets the email id select wme.email_unique_id, wme.email_address into v_email_id, v_email_address from wnp_member_emails wme where wme.wnp_id = p_wnp_id and wme.is_primary = p_is_primary; -- sets if the email address has changed if (v_email_address <> p_email_address) then v_changed_email := true; end if; -- if it exists, let's update it update wnp_member_emails set email_address = p_email_address, is_primary = p_is_primary, preferred_email_ind = p_preferred_email_ind, email_address_ind = p_email_address_ind where email_unique_id = v_email_id; exception when no_data_found then select email_unique_id_seq.nextval into v_email_id from dual; -- if it doesn't exist creates one. insert into wnp_member_emails (email_unique_id, wnp_id, email_address, is_primary, preferred_email_ind, email_address_ind) values (v_email_id, p_wnp_id, p_email_address, p_is_primary, p_preferred_email_ind, p_email_address_ind); end; return v_changed_email; end save_email; /** * Inserts a new member. */ procedure insert_member(p_wnp_id in varchar2, p_employee_network_id in varchar2, p_company_id in varchar2, p_member_first_name in varchar2, p_member_last_name in varchar2, p_middle_initial in varchar2, p_date_of_birth in date, p_gender in varchar2, p_marital_status_id in number, p_relationship_id in number, p_is_active in char, p_nsh in varchar2) as begin insert into wnp_member_mstr (wnp_id, employee_network_id, company_id, member_first_name, member_last_name, middle_initial, date_of_birth, gender, marital_status_id, relationship_id, is_active, NSH) values (p_wnp_id, p_employee_network_id, p_company_id, p_member_first_name, p_member_last_name, p_middle_initial, p_date_of_birth, p_gender, p_marital_status_id, p_relationship_id, p_is_active, p_nsh); end insert_member; /** * Updates an existing member. */ procedure update_member(p_wnp_id in varchar2, p_company_id in varchar2, p_member_first_name in varchar2, p_member_last_name in varchar2, p_middle_initial in varchar2, p_date_of_birth in date, p_gender in varchar2, p_marital_status_id in number, p_relationship_id in number, p_is_active in char) as begin update wnp_member_mstr set company_id = p_company_id, member_first_name = p_member_first_name, member_last_name = p_member_last_name, middle_initial = p_middle_initial, date_of_birth = p_date_of_birth, gender = p_gender, marital_status_id = p_marital_status_id, relationship_id = p_relationship_id, is_active = p_is_active where wnp_id = p_wnp_id; end update_member; /** * Inserts new member in the member_ids table. */ procedure insert_member_ids(p_wnp_id in varchar2, p_hmi_id in varchar2, p_ssn in varchar2, p_family_id in varchar2, p_emp_alternt_id in varchar2) as begin insert into wnp_member_ids (wnp_id, hmi_id, ssn, family_id, emp_alternt_id) values (p_wnp_id, p_hmi_id, p_ssn, p_family_id, p_emp_alternt_id); end insert_member_ids; /** * Updates an existing member in the member_ids table. */ procedure update_member_ids(p_wnp_id in varchar2, p_hmi_id in varchar2, p_ssn in varchar2, p_family_id in varchar2, p_emp_alternt_id in varchar2) as begin update wnp_member_ids set hmi_id = p_hmi_id, ssn = p_ssn, family_id = p_family_id, emp_alternt_id = p_emp_alternt_id where wnp_id = p_wnp_id; end update_member_ids; /** * Inserts new member in the member_job_details table. */ procedure insert_member_job_details(p_wnp_id in varchar2, p_branch_id in varchar2, p_employment_start_date in date, p_employment_end_date in date) as begin insert into wnp_member_job_details (wnp_id, branch_id, employment_start_date, employment_end_date) values (p_wnp_id, p_branch_id, p_employment_start_date, p_employment_end_date); end insert_member_job_details; /** * Updates an existing member in the member_job_details table. */ procedure update_member_job_details(p_wnp_id in varchar2, p_branch_id in varchar2, p_employment_start_date in date, p_employment_end_date in date) as begin update wnp_member_job_details set branch_id = p_branch_id, employment_start_date = p_employment_start_date, employment_end_date = p_employment_end_date where wnp_id = p_wnp_id; end update_member_job_details; /** * Inserts new member in the member_eligibility table. */ procedure insert_member_eligibility(p_wnp_id in varchar2, p_eligibility_start_date in date, p_eligibility_end_date in date) as begin insert into wnp_member_eligibility (wnp_id, eligibility_start_date, eligibility_end_date) values (p_wnp_id, p_eligibility_start_date, p_eligibility_end_date); end insert_member_eligibility; /** * Updates an existing member in the member_eligibility table. */ procedure update_member_eligibility(p_wnp_id in varchar2, p_eligibility_start_date in date, p_eligibility_end_date in date) as begin update wnp_member_eligibility set eligibility_start_date = p_eligibility_start_date, eligibility_end_date = p_eligibility_end_date where wnp_id = p_wnp_id; end update_member_eligibility; /** * Inserts new member in the member_coverage table. */ procedure insert_member_coverage(p_wnp_id in varchar2, p_coverage_efective_date in date, p_coverage_termination_date in date) as begin insert into wnp_member_coverage (wnp_id, coverage_efective_date, coverage_termination_date) values (p_wnp_id, p_coverage_efective_date, p_coverage_termination_date); end insert_member_coverage; /** * Updates an existing member in the member_coverage table. */ procedure update_member_coverage(p_wnp_id in varchar2, p_coverage_efective_date in date, p_coverage_termination_date in date) as begin update wnp_member_coverage set coverage_efective_date = p_coverage_efective_date, coverage_termination_date = p_coverage_termination_date where wnp_id = p_wnp_id; end update_member_coverage; /** * Inserts new member in the member_misc table. */ procedure insert_member_misc(p_wnp_id in varchar2, p_custom1 in varchar2, p_custom2 in varchar2, p_custom3 in varchar2, p_custom4 in varchar2, p_custom5 in varchar2, p_custom6 in varchar2, p_custom7 in varchar2, p_custom8 in varchar2, p_custom9 in varchar2, p_custom10 in varchar2) as begin insert into wnp_member_misc (wnp_id, custom1, custom2, custom3, custom4, custom5, custom6, custom7, custom8, custom9, custom10) values (p_wnp_id, p_custom1, p_custom2, p_custom3, p_custom4, p_custom5, p_custom6, p_custom7, p_custom8, p_custom9, p_custom10); end insert_member_misc; /** * Inserts new member in the member_misc table. */ procedure update_member_misc(p_wnp_id in varchar2, p_custom1 in varchar2, p_custom2 in varchar2, p_custom3 in varchar2, p_custom4 in varchar2, p_custom5 in varchar2, p_custom6 in varchar2, p_custom7 in varchar2, p_custom8 in varchar2, p_custom9 in varchar2, p_custom10 in varchar2) as begin update wnp_member_misc set custom1 = p_custom1, custom2 = p_custom2, custom3 = p_custom3, custom4 = p_custom4, custom5 = p_custom5, custom6 = p_custom6, custom7 = p_custom7, custom8 = p_custom8, custom9 = p_custom9, custom10 = p_custom10 where wnp_id = p_wnp_id; end update_member_misc; /** * Inserts purpose optins */ procedure insert_purpose_mstr(p_wnp_id in varchar2) as begin insert into wnp_user_purpose (purpose_id, wnp_id, purpose_selected) select purpose_id, p_wnp_id, DEFAULT_PURPOSE_SELECTED from wnp_purpose_mstr; end insert_purpose_mstr; /** * Inserts selected images */ procedure insert_image_mstr(p_wnp_id in varchar2) as begin insert into wnp_user_photo_image (wnp_id, image_id, image_type, image_selected) select p_wnp_id, image_id, IMAGE_TYPE_SYSTEM_IMAGE, decode(image_id, SELECTED_IMAGE_ID, 1, 0) from wnp_photo_image_mstr where image_type = IMAGE_TYPE_SYSTEM_IMAGE; end insert_image_mstr; /** * Inserts optin */ procedure insert_optin_mstr(p_wnp_id in varchar2) as begin insert into wnp_user_optin (wnp_id, optin_id, optin_selected) values (p_wnp_id, SELECTED_OPTIN_ID, 1); end insert_optin_mstr; /** * Inserts news preferences */ procedure insert_news_preferences(p_wnp_id in varchar2) as begin insert into wnp_user_health_news_pref (wnp_id, news_pref_id, news_pref_selected) select p_wnp_id, news_pref_id, decode(news_pref_id, SELECTED_NEWS_ID, 1, 0) from wnp_health_news_pref_mstr; end insert_news_preferences; /** * Inserts questions preferences */ procedure insert_user_questions(p_wnp_id in varchar2) as begin insert into wnp_user_questions (wnp_id, question_id, section_id, question_value) select p_wnp_id, question_id, section_id, DEFAUL_QUESTION_VALUE from wnp_questions_mstr; end insert_user_questions; /** * Creates or updates a member with the provided info. */ procedure set_eligibility_member(p_company_id in varchar2, p_family_id in varchar2, p_employee_network_id in out varchar2, p_member_first_name in varchar2, p_member_last_name in varchar2, p_middle_initial in varchar2, p_date_of_birth in date, p_gender in varchar2, p_marital_status_code in varchar2, p_relationship_code in varchar2, p_is_active in varchar2, p_nsh in varchar2, p_residential_address1 in varchar2, p_residential_address2 in varchar2, p_residential_city in varchar2, p_residential_state in varchar2, p_residential_zip in varchar2, p_residential_country in varchar2, p_mailing_address1 in varchar2, p_mailing_address2 in varchar2, p_mailing_city in varchar2, p_mailing_state in varchar2, p_mailing_zip in varchar2, p_mailing_country in varchar2, p_emp_alternt_id in varchar2, p_home_phone_number in varchar2, p_home_pref_phone_ind in varchar2, p_work_phone_number in varchar2, p_work_pref_phone_ind in varchar2, p_email_address in varchar2, p_preferred_email_ind in varchar2, p_email_address_ind in varchar2, p_email_is_primary in varchar2, p_ssn in varchar2, p_branch_id in varchar2, p_employment_start_date in date, p_employment_end_date in date, p_eligibility_start_date in date, p_eligibility_end_date in date, p_coverage_efective_date in date, p_coverage_termination_date in date, p_custom1 in varchar2, p_custom2 in varchar2, p_custom3 in varchar2, p_custom4 in varchar2, p_custom5 in varchar2, p_custom6 in varchar2, p_custom7 in varchar2, p_custom8 in varchar2, p_custom9 in varchar2, p_custom10 in varchar2, p_email_changed out varchar2, p_phone_changed out varchar2, p_user_created out varchar2, p_wnp_id out varchar2) is v_relationship_id wnp_relationship_mstr.relationship_id%type; v_marital_status_id wnp_marital_status_mstr.marital_status_id%type; v_phone_changed boolean := false; v_email_changed boolean := false; v_user_created boolean := false; begin -- gets the relashionship id v_relationship_id := get_relashionship_id(p_relationship_code); -- gets the marital status id v_marital_status_id := get_marital_status_id(p_marital_status_code); -- gets the wnp id if the user exists p_wnp_id := get_user_wnp_id(p_company_id, v_relationship_id, p_family_id); if (p_wnp_id is null) then -- gets the next valid wnp id. p_wnp_id := get_next_wnp_id(p_company_id, p_relationship_code); -- sets that a user has been created v_user_created := true; -- inserts the member insert_member(p_wnp_id, p_employee_network_id, p_company_id, p_member_first_name, p_member_last_name, p_middle_initial, p_date_of_birth, p_gender, v_marital_status_id, v_relationship_id, p_is_active, p_nsh); -- inserts misc information insert_member_ids(p_wnp_id, p_wnp_id, p_ssn, p_family_id, p_emp_alternt_id); -- inserts member eligibility information insert_member_eligibility(p_wnp_id, p_eligibility_start_date, p_eligibility_end_date); -- inserts job details info insert_member_job_details(p_wnp_id, p_branch_id, p_employment_start_date, p_employment_end_date); -- inserts member coverage data insert_member_coverage(p_wnp_id, p_coverage_efective_date, p_coverage_termination_date); -- inserts member misc data insert_member_misc(p_wnp_id, p_custom1, p_custom2, p_custom3, p_custom4, p_custom5, p_custom6, p_custom7, p_custom8, p_custom9, p_custom10); -- automatically inserts purposes optins insert_purpose_mstr(p_wnp_id); -- automatically inserts images selected insert_image_mstr(p_wnp_id); -- inserts optin insert_optin_mstr(p_wnp_id); -- inserts news preferences insert_news_preferences(p_wnp_id); -- inserts user questions insert_user_questions(p_wnp_id); else -- updates the existing member update_member(p_wnp_id, p_company_id, p_member_first_name, p_member_last_name, p_middle_initial, p_date_of_birth, p_gender, v_marital_status_id, v_relationship_id, p_is_active); -- updates the network id select employee_network_id into p_employee_network_id from wnp_member_mstr where wnp_id = p_wnp_id; -- update misc information update_member_ids(p_wnp_id, p_wnp_id, p_ssn, p_family_id, p_emp_alternt_id); -- updates member eligibility information update_member_eligibility(p_wnp_id, p_eligibility_start_date, p_eligibility_end_date); -- updates job details info update_member_job_details(p_wnp_id, p_branch_id, p_employment_start_date, p_employment_end_date); -- updates member coverage data update_member_coverage(p_wnp_id, p_coverage_efective_date, p_coverage_termination_date); -- updates member misc info update_member_misc(p_wnp_id, p_custom1, p_custom2, p_custom3, p_custom4, p_custom5, p_custom6, p_custom7, p_custom8, p_custom9, p_custom10); end if; -- saves residential address save_address(p_wnp_id, p_residential_address1, p_residential_address2, p_residential_city, p_residential_state, p_residential_zip, p_residential_country, COMM_TYPE_RESIDENTIAL); -- saves mailing address save_address(p_wnp_id, p_mailing_address1, p_mailing_address2, p_mailing_city, p_mailing_state, p_mailing_zip, p_mailing_country, COMM_TYPE_MAILING); -- saves home phone number v_phone_changed := save_phone(p_wnp_id, p_home_phone_number, p_home_pref_phone_ind, COMM_TYPE_HOME_PHONE); p_phone_changed := 'N'; if (v_phone_changed = true) then p_phone_changed := 'Y'; end if; -- saves work phone number v_phone_changed := save_phone(p_wnp_id, p_work_phone_number, p_work_pref_phone_ind, COMM_TYPE_WORK_PHONE); if (v_phone_changed = true) then p_phone_changed := 'Y'; end if; -- saves e-mail v_email_changed := save_email(p_wnp_id, p_email_address, p_preferred_email_ind, p_email_address_ind, p_email_is_primary); if (v_email_changed = true) then p_email_changed := 'Y'; else p_email_changed := 'N'; end if; if (v_user_created = true) then p_user_created := 'Y'; else p_user_created := 'N'; end if; end set_eligibility_member; end pkg_eligibility; /