CREATE OR REPLACE PACKAGE mip_parties AS

  FUNCTION is_tripartite(p_user_id IN VARCHAR2) RETURN BOOLEAN; 
  FUNCTION get_user_id(p_username IN VARCHAR2) RETURN NUMBER;
  FUNCTION get_user_role(p_username IN VARCHAR2) RETURN VARCHAR2;
  --
  /*
   FUNCTION check_user_role
   - checks the role keycode given exists for a supplied username.
   - This function searches the parties table for a matching username and role
   - if found then true is returned.
   %param p_username - the name of the user you want to get the id for.
   %param p_role - the role you wish to check against
  */
  FUNCTION check_user_role(p_username IN VARCHAR2, p_role IN VARCHAR2) RETURN BOOLEAN;
  --
  /** get the password created date for the given username and password
  
  %return DATE the date the password was created for authenticated username and password combination
  
  */
  FUNCTION get_user_password_created(p_username IN VARCHAR2,
                                     p_password IN VARCHAR2) RETURN DATE;
  --
  FUNCTION has_supplier(p_username IN VARCHAR2, p_supplierid IN VARCHAR)
    RETURN BOOLEAN;
  
  PROCEDURE add_partyaddress(p_addr_code IN VARCHAR2, p_prty_id IN INTEGER);
   
  PROCEDURE add_partyrole(p_role_code IN VARCHAR2, p_prty_id IN NUMBER);
  PROCEDURE add_partycontactmech(p_contact_mech IN VARCHAR2,
                                 p_prty_id      IN NUMBER);
  PROCEDURE add_party(p_role                     IN VARCHAR2,
                      p_username                 IN VARCHAR2,
                      p_created_by               IN VARCHAR2,
                      p_name                     IN VARCHAR2,
                      p_mkpt_ref                 IN VARCHAR2,
                      p_shortcode                IN VARCHAR2,
                      p_lt_7b_contract_ref       IN VARCHAR2,
                      p_gt_7b_contract_ref       IN VARCHAR2,
                      p_adversarial_contract_ref IN VARCHAR2,
                      p_manu_ref                 IN VARCHAR2,
                      p_description              IN VARCHAR2,
                      p_status                   IN VARCHAR2,
                      p_first_name               IN VARCHAR2,
                      p_last_name                IN VARCHAR2,
                      p_personal_title           IN VARCHAR2,
                      p_comments                 IN VARCHAR2,
                      p_addresscode              IN VARCHAR2,
                      p_partyrole                IN VARCHAR2,
                      p_partycontactmech         IN VARCHAR2);

  --
  /** allows new parties to be created in the system.
  
  %param  p_manu_ref  the manufacturer's unique reference
  %param  p_created_by  the user that created the party
  %param  p_shortcode the mktp short code
  %param  p_name  the organization's name
  %param  p_description description of the manufacturer
  %param  p_created_on  when the party was created
  %param  p_mktp_ref  the mktp's unique reference
  %param  p_lt_7b_contract_ref  
  %param  p_gt_7b_contract_ref  
  %param  p_adversarial_contract_ref  
  %param  p_username  the username
  %param  p_status  the status of the user
  %param  p_first_name  the user's first name
  %param  p_last_name the user's last name
  %param  p_personal_title  the user's title
  %param  p_comments  any comments on the user
  %param  p_id  the unique party id
  %param  p_prty_type the prty type
  
  */
  PROCEDURE create_party(p_manu_ref                 IN VARCHAR2,
                         p_created_by               IN VARCHAR2,
                         p_shortcode                IN VARCHAR2,
                         p_name                     IN VARCHAR2,
                         p_description              IN VARCHAR2,
                         p_mktp_ref                 IN VARCHAR2,
                         p_lt_7b_contract_ref       IN VARCHAR2,
                         p_gt_7b_contract_ref       IN VARCHAR2,
                         p_adversarial_contract_ref IN VARCHAR2,
                         p_username                 IN VARCHAR2,
                         p_first_name               IN VARCHAR2,
                         p_last_name                IN VARCHAR2,
                         p_personal_title           IN VARCHAR2,
                         p_comments                 IN VARCHAR2,
                         p_id                       IN INTEGER,
                         p_prty_type                IN VARCHAR2,
                         p_tripartite_member        IN VARCHAR2,
                         p_password                 IN VARCHAR2,
                         p_expire_pword             IN NUMBER);
   --
   
   /**
      allows the creation of addresses in the system.
      
      %param p_address address%ROWTYPE - a record of the address table.
     */
   PROCEDURE add_address(p_address addresses%ROWTYPE);
   --
   
   /**
      allows an address in the system to be associated with a party
      
      %param p_addr_code - the ID of the address
      %param p_prty_id - the ID of the party
      %param p_addr_type - the type of address (HOME/OFFICE)
     */
   PROCEDURE create_party_address(p_addr_code IN addresses.code%TYPE,
                                 p_prty_id   IN parties.id%TYPE,
                                 p_addr_type IN VARCHAR2);
   --
   
   
   
   /** 
     allows party contact details to be stored in the system
     
     %param p_prty_id - the ID of the party
     %param p_contact_mech_type - the type of contact mechanism being created
     %param p_contact_value - the value of the contact mechanism
     
    */
   PROCEDURE create_party_contact_details(p_prty_id           IN parties.id%TYPE,
                                          p_contact_mech_type IN VARCHAR2,
                                          p_contact_value     IN VARCHAR2);
   --
   
   /**
     PROCEDURE - update_contact_details
     allows the contact mechanisms to be updated, setting the old mechanism's end date
    %param p_prty_id - the ID of the party
     %param p_contact_mech_type - the type of contact mechanism being created
     %param p_contact_value - the value of the contact mechanism
     
    */     
   PROCEDURE update_contact_details(p_prty_id IN parties.id%TYPE,
                                    p_contact_mech_type IN VARCHAR2,
                                    p_contact_value IN VARCHAR2);
                                    
   /**
     allows a role to be assigned to a party
     
     %param p_role_code the role to assign to the party
     %param p_prty_id the unique party ID
   */
  PROCEDURE add_party_role(p_role_code IN VARCHAR2,
                           p_prty_id IN NUMBER);
  -- 
   
   /**
     allows a party relationship to be created e.g. associating a party with a supplier
    
    */ 
  PROCEDURE create_relationship(p_from_prty_id   IN VARCHAR2,
                                p_from_prty_role IN VARCHAR2,
                                p_to_prty_id     IN VARCHAR2,
                                p_to_prty_role   IN VARCHAR2);
  --
  
  /**
     expires any old addresses without an end date.  To be used when a new address is assigned to a party
     The address's end date is set to today as that is when the new address is valid from.  No setting
     addresses to be used in the future.
     
     %param p_prty_id the ID of the party
     %param p_addr_code the code of the new address.
     %param p_addr_type the type of address HOME/OFFICE
    */
  PROCEDURE expire_old_addresses(p_prty_id IN parties.id%TYPE,
                                 p_addr_code IN addresses.code%TYPE,
                                 p_paddr_type IN party_address_roles.rt_code%TYPE);
  --
  
  /** 
       REMOVE_PARTY
       removes the party and all related information from the system.
       
       %param p_prty_id - the ID of the party to remove.
      */
  PROCEDURE remove_party(p_prty_id IN NUMBER);
  --
END mip_parties;
/
CREATE OR REPLACE PACKAGE BODY mip_parties AS
  /*
   FUNCTION is_tripartite
   - This function searches the parties table with the supplied
   - mktp_ref and returns true if the tripartite member column is yes.
   %param p_mktp_ref - the market participant (supplier) to check.
  */
  FUNCTION is_tripartite(p_user_id IN VARCHAR2) RETURN BOOLEAN AS
    l_tripartite_status varchar2(3);
    CURSOR c_tripartite IS
      SELECT distinct tripartite_member FROM parties WHERE id = p_user_id;
  BEGIN
    OPEN c_tripartite;
    FETCH c_tripartite
      INTO l_tripartite_status;
    CLOSE c_tripartite;
    if upper(l_tripartite_status) = 'YES' then
      return TRUE;
    else
      return FALSE;
    end if;
  END is_tripartite;
  /*
   FUNCTION get_user_id
   - gets the primary key for a supplied username.  This function searches the parties
   - table for a matching username, if the name is found the users id is returned.
   %param p_username - the name of the user you want to get the id for.
  
   UPDATES
     20-Nov-2007 - MM- Upper ing the username check as APEX passes an uppered :APP_USER
  */
  FUNCTION get_user_id(p_username IN VARCHAR2) RETURN NUMBER AS
    userid NUMBER;
    CURSOR c_userid IS
      SELECT id FROM parties WHERE upper(username) = upper(p_username);
  BEGIN
    OPEN c_userid;
    FETCH c_userid
      INTO userid;
    CLOSE c_userid;
    RETURN userid;
  EXCEPTION
    WHEN OTHERS THEN
      RETURN NULL;
  END get_user_id;

  /*
   FUNCTION get_user_role
   - gets the role keycode for a supplied username.  This function searches the parties
   - table for a matching username, if the name is found the users id is returned.
   - That id is used to search the party_roles table to get the role keycode.
   %param p_username - the name of the user you want to get the id for.
  */
  FUNCTION get_user_role(p_username IN VARCHAR2) RETURN VARCHAR2 AS
    rolecode VARCHAR2(80);
    CURSOR c_userrole IS
      SELECT rt_code
        FROM party_roles
       WHERE prty_id = get_user_id(p_username);
  BEGIN
    OPEN c_userrole;
    FETCH c_userrole
      INTO rolecode;
    CLOSE c_userrole;
    RETURN rolecode;
  EXCEPTION
    WHEN OTHERS THEN
      RETURN NULL;
  END get_user_role;

  /*
   FUNCTION check_user_role
   - checks the role keycode given exists for a supplied username.
   - This function searches the parties table for a matching username and role
   - if found then true is returned.
   %param p_username - the name of the user you want to get the id for.
   %param p_role - the role you wish to check against
   
   update - 16-Jan-2008 - MM - added check that the returned role is NOT null
                               if it is return FALSE.  This was because it wasn't
                               giving a no data found error as expected.
  */
  FUNCTION check_user_role(p_username IN VARCHAR2, p_role IN VARCHAR2) RETURN BOOLEAN AS
    rolecode VARCHAR2(80);
    CURSOR c_userrole IS
      SELECT rt_code
        FROM party_roles
       WHERE prty_id = get_user_id(p_username)
         AND upper(rt_code) = upper(p_role);
  BEGIN
    OPEN c_userrole;
    FETCH c_userrole
      INTO rolecode;
    CLOSE c_userrole;
 
    IF rolecode IS NOT NULL THEN
      RETURN TRUE;
    ELSE RETURN FALSE;
    END IF;
  EXCEPTION
    WHEN OTHERS THEN
    CLOSE c_userrole;
      RETURN FALSE;
  END check_user_role;
  --

  /** get the password created date for the given username and password
  
  %return DATE the date the password was created for authenticated username and password combination
  
  */
  FUNCTION get_user_password_created(p_username IN VARCHAR2,
                                     p_password IN VARCHAR2) RETURN DATE AS
    l_password_created_on DATE;
  BEGIN
    SELECT created_on
      INTO l_password_created_on
      FROM (SELECT pwd.prty_id,
                   pwd.password_hash,
                   MAX(pwd.created_on) over(PARTITION BY pwd.prty_id) AS latest_pwd_date,
                   pwd.created_on
              FROM passwords pwd, parties prty
             WHERE upper(prty.username) = upper(p_username)
               AND pwd.prty_id = prty.id) pwd
     WHERE pwd.created_on = pwd.latest_pwd_date
       AND pwd.password_hash =
           mip_security.get_hash(p_username, p_password);
  
    RETURN l_password_created_on;
  END get_user_password_created;
  --

  PROCEDURE add_partyrole(p_role_code IN VARCHAR2, p_prty_id IN NUMBER) AS
  --
  BEGIN
  --
  
    INSERT INTO party_roles
      (id, rt_code, prty_id, start_date, end_date, description)
    VALUES
      (prty_seq.NEXTVAL,
       p_role_code,
       p_prty_id,
       SYSDATE,
       NULL,
       'added by ' || v('APP_USER'));
  
    COMMIT;
    --
  EXCEPTION
    WHEN OTHERS THEN
      ROLLBACK;
      RAISE;
  END add_partyrole;
  --

  /*
   PROCEDURE add_party
   - allows an admin user to create new parties for use in the system
   %param p_username - the name of the user to create
   %param p_password - the password of the party
   %p_role - the party's role in the system
  
  */
  --

  PROCEDURE add_party(p_role                     IN VARCHAR2,
                      p_username                 IN VARCHAR2,
                      p_created_by               IN VARCHAR2,
                      p_name                     IN VARCHAR2,
                      p_mkpt_ref                 IN VARCHAR2,
                      p_shortcode                IN VARCHAR2,
                      p_lt_7b_contract_ref       IN VARCHAR2,
                      p_gt_7b_contract_ref       IN VARCHAR2,
                      p_adversarial_contract_ref IN VARCHAR2,
                      p_manu_ref                 IN VARCHAR2,
                      p_description              IN VARCHAR2,
                      p_status                   IN VARCHAR2,
                      p_first_name               IN VARCHAR2,
                      p_last_name                IN VARCHAR2,
                      p_personal_title           IN VARCHAR2,
                      p_comments                 IN VARCHAR2,
                      p_addresscode              IN VARCHAR2,
                      p_partyrole                IN VARCHAR2,
                      p_partycontactmech         IN VARCHAR2) AS
    --
    partyid NUMBER;
  BEGIN
    --
    --not implemented yet - will allow us to check the password is valid based on rule in the functional spec
    -- check_password(p_password);
    INSERT INTO parties
      (id,
       username,
       prty_type,
       created_on,
       created_by,
       NAME,
       mktp_ref,
       shortcode,
       lt_7b_contract_ref,
       gt_7b_contract_ref,
       adversarial_contract_ref,
       manu_ref,
       description,
       status,
       first_name,
       last_name,
       personal_title,
       comments)
    VALUES
      (prty_seq.NEXTVAL,
       upper(p_username),
       --get_hash(TRIM(p_username), p_password),
       --will need to call matts get_hash routine once he's added
       --it into the new schema
       upper(p_role),
       SYSDATE,
       p_created_by,
       p_name,
       p_mkpt_ref,
       p_shortcode,
       p_lt_7b_contract_ref,
       p_gt_7b_contract_ref,
       p_adversarial_contract_ref,
       p_manu_ref,
       p_description,
       p_status,
       p_first_name,
       p_last_name,
       p_personal_title,
       p_comments)
    RETURNING parties.id INTO partyid;
    --COMMIT;
    --call the partyaddress to fill in the link between the party and the address
    IF p_addresscode IS NOT NULL THEN
      add_partyaddress(p_addresscode, partyid);
    END IF;
    -- call the party role to fill in the link between party and the role
    IF p_partyrole IS NOT NULL THEN
      add_partyrole(p_partyrole, partyid);
    END IF;
    -- call the party contact mechanism to link the party to a contact mech
    IF p_partycontactmech IS NOT NULL THEN
      add_partycontactmech(p_partycontactmech, partyid);
    END IF;
    --
  END add_party;

  /*
   PROCEDURE add_partyaddress
   - allows the association of a party to a number of addresses
   %param p_username - the name of the user to create
   %param p_password - the password of the party
   %p_role - the party's role in the system
  
  */
  --

  PROCEDURE add_partyaddress(p_addr_code IN VARCHAR2, p_prty_id IN INTEGER) AS
    --
    l_addr_code    addresses.code%TYPE;
    l_working_code addresses.code%TYPE;
    --
  BEGIN
    l_addr_code := p_addr_code || ':';
    --not implemented yet - will allow us to check the password is valid based on rule in the functional spec
    -- check_password(p_password);
    LOOP
      --
      l_working_code := substr(l_addr_code, 1, instr(l_addr_code, ':') - 1);
      --
      IF l_working_code IS NULL THEN
        EXIT;
      END IF;
      --
      INSERT INTO party_addresses
        (addr_code, prty_id, start_date, end_date, comments)
      VALUES
        (l_working_code, p_prty_id, SYSDATE, SYSDATE, 'The comments field');
      --
      l_addr_code := substr(l_addr_code, instr(l_addr_code, ':') + 1);
      --
    END LOOP;
    --
  END add_partyaddress;

  
  /*
   PROCEDURE add_partycontactmech
   - allows the association of a party to a number of addresses
   %param p_contact_mech - the name of the contact mechanism code
   %param p_prty_id - the party's id
  
  */
  --
  PROCEDURE add_partycontactmech(p_contact_mech IN VARCHAR2,
                                 p_prty_id      IN NUMBER) AS
    --
    l_contact_mech contact_mechanisms.comt_code%TYPE;
    l_working_code contact_mechanisms.comt_code%TYPE;
    comeid         contact_mechanisms.id%TYPE;
    --l_contact_mech_role  contact_mechanisms.comt_code%TYPE;
    --l_working_role contact_mechanisms.comt_code%TYPE;
    --pcmr_id party_contact_mechanism_roles.id%TYPE
  
    --
    -- get the contact mech type
    -- create a new contact mech using the type code
    -- create a new party contact mech from the partyid and the newly created contact mech
  BEGIN
    l_contact_mech := p_contact_mech || ':';
  
    LOOP
      --
      l_working_code := substr(l_contact_mech,
                               1,
                               instr(l_contact_mech, ':') - 1);
      --
      IF l_working_code IS NULL THEN
        EXIT;
      END IF;
      --
      -- Create the Contact mechanism
      INSERT INTO contact_mechanisms
      --
        (comt_code, contact_value, id)
      VALUES
        (l_working_code, 'dunno what this field is for', come_seq.NEXTVAL)
      RETURNING contact_mechanisms.id INTO comeid;
    
      /*-- Create the Party contact mechanism role
       --Think this needs to be sorted out much like the contact mechanism listed
       --above, however not sure about the model and what AH intended
      
      INSERT INTO party_contact_mechanism_roles
      --
        (pcmrt_code, pcm_prty_id, start_date, id)
      VALUES
        (l_working_role,
         p_prty_id,
         sysdate,
         MADEUPSEQPROBABLY CALLED-PCMR_seq.nextval) RETURNING party_contact_mechanism_roles.id INTO pcmr_id;*/
    
      INSERT INTO party_contact_mechanisms
      --should be the come_id i think!
        (come_id, prty_id, start_date, end_date, comments)
      VALUES
        (comeid, p_prty_id, SYSDATE, SYSDATE, 'The comments field');
      --
      l_contact_mech := substr(l_contact_mech,
                               instr(l_contact_mech, ':') + 1);
      --
    END LOOP;
    --
  END add_partycontactmech;
  --

  --
  /*
   FUNCTION has_supplier
   - Returns true if a supplier is found for a supplied username.  Searches the parties
   - table for a matching username, if the name is found the users id is returned.
   - That id is used to search the party_roles table to get the role keycode.
   %param p_username - the name of the user you want to find the supplier for.
   %param p_supplierid - the id of the supplier you want to check the user against.
  */
  FUNCTION has_supplier(p_username IN VARCHAR2, p_supplierid IN VARCHAR)
    RETURN BOOLEAN AS
    CURSOR c_userrole IS
      SELECT id
        FROM parties p, party_relationships pr
       WHERE p.id = pr.to_parl_prty_id
         AND pr.from_parl_prty_id = get_user_id(p_username)
         AND pr.to_parl_rt_code = 'SUPP'
         AND pr.from_parl_rt_code = 'AGENT';
  BEGIN
    FOR userrole_rec IN c_userrole LOOP
      IF p_supplierid = userrole_rec.id THEN
        RETURN TRUE;
      END IF;
    END LOOP;
    RETURN FALSE;
  END has_supplier;

  /** allows new parties to be created in the system.
  
  %param  p_manu_ref  the manufacturer's unique reference
  %param  p_created_by  the user that created the party
  %param  p_shortcode the mktp short code
  %param  p_name  the organization's name
  %param  p_description description of the manufacturer
  %param  p_created_on  when the party was created
  %param  p_mktp_ref  the mktp's unique reference
  %param  p_lt_7b_contract_ref  
  %param  p_gt_7b_contract_ref  
  %param  p_adversarial_contract_ref  
  %param  p_username  the username
  %param  p_status  the status of the user
  %param  p_first_name  the user's first name
  %param  p_last_name the user's last name
  %param  p_personal_title  the user's title
  %param  p_comments  any comments on the user
  %param  p_id  the unique party id
  %param  p_prty_type the prty type
  
  */
  PROCEDURE create_party(p_manu_ref                 IN VARCHAR2,
                         p_created_by               IN VARCHAR2,
                         p_shortcode                IN VARCHAR2,
                         p_name                     IN VARCHAR2,
                         p_description              IN VARCHAR2,
                         p_mktp_ref                 IN VARCHAR2,
                         p_lt_7b_contract_ref       IN VARCHAR2,
                         p_gt_7b_contract_ref       IN VARCHAR2,
                         p_adversarial_contract_ref IN VARCHAR2,
                         p_username                 IN VARCHAR2,
                         p_first_name               IN VARCHAR2,
                         p_last_name                IN VARCHAR2,
                         p_personal_title           IN VARCHAR2,
                         p_comments                 IN VARCHAR2,
                         p_id                       IN INTEGER,
                         p_prty_type                IN VARCHAR2,
                         p_tripartite_member        IN VARCHAR2,
                         p_password                 IN VARCHAR2,
                         p_expire_pword             IN NUMBER) IS
    --
  BEGIN
    --
    INSERT INTO parties
      (id,
       manu_ref,
       created_by,
       shortcode,
       NAME,
       description,
       created_on,
       mktp_ref,
       lt_7b_contract_ref,
       gt_7b_contract_ref,
       adversarial_contract_ref,
       username,
       first_name,
       last_name,
       personal_title,
       comments,
       prty_type,
       tripartite_member)
    VALUES
      (p_id,
       p_manu_ref,
       p_created_by,
       p_shortcode,
       p_name,
       p_description,
       SYSDATE,
       p_mktp_ref,
       p_lt_7b_contract_ref,
       p_gt_7b_contract_ref,
       p_adversarial_contract_ref,
       p_username,
       p_first_name,
       p_last_name,
       p_personal_title,
       p_comments,
       p_prty_type,
       p_tripartite_member);
  
    -- if we created a user they need a password creating
    IF upper(p_prty_type) = 'PERS' THEN
      mip_security.other_user_password(p_prty_id  => p_id,
                                       p_username => p_username,
                                       p_password => p_password);
    
      -- set the user's status
      IF p_expire_pword IS NOT NULL THEN
        mip_security.set_user_status(p_username => p_username,
                                     p_status   => 'EXPIRED');
      ELSE
        -- just open the user
        mip_security.set_user_status(p_username => p_username,
                                     p_status   => 'OPEN');
      END IF;
    END IF;
    --
  END create_party;
  --

  /**
      allows the creation of addresses in the system.
      
      %param p_address address%ROWTYPE - a record of the address table.
     */
  PROCEDURE add_address(p_address addresses%ROWTYPE) AS
    --
  BEGIN
    --
    -- check all columns are identical
    -- if they match do nothing, we are reusing an existing address
    -- otherwsie INSERT a new record.
    --
    MERGE INTO addresses addr
    USING (SELECT p_address.code AS code,
                  p_address.sub_building AS sub_building,
                  p_address.building AS building,
                  p_address.street AS street,
                  p_address.city AS city,
                  p_address.postcode AS postcode
             FROM dual) l_addr
    ON (addr.code = l_addr.code AND 
        addr.sub_building = l_addr.sub_building AND 
        addr.building = l_addr.building AND 
        addr.street = l_addr.street AND 
        addr.city = l_addr.city AND 
        addr.postcode = p_address.postcode)
    WHEN NOT MATCHED THEN
      INSERT
        (code, sub_building, building, street, city, postcode)
      VALUES
        (l_addr.code,
         l_addr.sub_building,
         l_addr.building,
         l_addr.street,
         l_addr.city,
         l_addr.postcode);
  
    --COMMIT;
    --
  EXCEPTION
    WHEN OTHERS THEN
      ROLLBACK;
      RAISE;
  END add_address;
  --

  /**
      associate an address with a party
      
      %param p_addr_code the code of the address
      %param p_prty_id the party's unique ID
      %param p_addr_type the type of address, either HOME or OFFICE
    */
  PROCEDURE create_party_address(p_addr_code IN addresses.code%TYPE,
                                 p_prty_id   IN parties.id%TYPE,
                                 p_addr_type IN VARCHAR2) IS
  BEGIN
    INSERT INTO party_addresses
      (addr_code, prty_id, start_date, end_date, comments)
    VALUES
      (p_addr_code,
       p_prty_id,
       SYSDATE,
       NULL,
       'Party address created by ' || v('APP_USER'));
  
    INSERT INTO party_address_roles
      (start_date,
       paddr_addr_code,
       paddr_prty_id,
       paddr_start_date,
       rt_code,
       end_date)
    VALUES
      (SYSDATE, p_addr_code, p_prty_id, SYSDATE, p_addr_type, NULL);
  
  END create_party_address;
  --
  
  /** 
     allows party contact details to be stored in the system
     
     %param p_prty_id - the ID of the party
     %param p_contact_mech_type - the type of contact mechanism being created
     %param p_contact_value - the value of the contact mechanism
     
    */
   PROCEDURE create_party_contact_details(p_prty_id           IN parties.id%TYPE,
                                          p_contact_mech_type IN VARCHAR2,
                                          p_contact_value     IN VARCHAR2) IS
     l_come_id NUMBER;
   BEGIN
     SELECT come_seq.NEXTVAL INTO l_come_id FROM dual;
     
     -- insert the contact mechanism value
     INSERT INTO contact_mechanisms(comt_code, contact_value, id)
     VALUES(p_contact_mech_type, p_contact_value, l_come_id);
     
     -- associate the contact mechanism with the party.
     INSERT INTO party_contact_mechanisms(come_id, prty_id, start_date)
     VALUES(l_come_id, p_prty_id, SYSDATE);
     
   END create_party_contact_details;
   --
   
   /**
     PROCEDURE - update_contact_details
     allows the contact mechanisms to be updated, setting the old mechanism's end date
    %param p_prty_id - the ID of the party
     %param p_contact_mech_type - the type of contact mechanism being created
     %param p_contact_value - the value of the contact mechanism
     
    */     
   PROCEDURE update_contact_details(p_prty_id IN parties.id%TYPE,
                                    p_contact_mech_type IN VARCHAR2,
                                    p_contact_value IN VARCHAR2) IS
     l_current_come_id contact_mechanisms.id%TYPE;
     l_current_come_value VARCHAR2(100);
   BEGIN
     BEGIN
     SELECT vccm.come_id, vccm.Contact_value
     INTO l_current_come_id, l_current_come_value
     FROM v_current_contact_mechanisms vccm
     WHERE vccm.PRTY_ID = p_prty_id
     AND vccm.CODE = p_contact_mech_type;
     EXCEPTION
       WHEN no_data_found THEN  --no current come exists, therefore just create one.
       create_party_contact_details(p_prty_id => p_prty_id,
                                    p_contact_mech_type => p_contact_mech_type,
                                    p_contact_value => p_contact_value);
     END;
     
     --check if it's a different value, if so, end this one and create a new one.
     IF l_current_come_value = p_contact_value THEN
       NULL;  -- no change to store
     ELSE
       UPDATE party_contact_mechanisms pcome
       SET pcome.end_date = SYSDATE
       WHERE pcome.come_id = l_current_come_id;
       
       create_party_contact_details(p_prty_id => p_prty_id,
                                    p_contact_mech_type => p_contact_mech_type,
                                    p_contact_value => p_contact_value);
     END IF;
   END update_contact_details;
   
  /**
     allows a role to be assigned to a party
     
     %param p_role_code the role to assign to the party
     %param p_prty_id the unique party ID
   */
  PROCEDURE add_party_role(p_role_code IN VARCHAR2,
                           p_prty_id IN NUMBER) AS
  --
  BEGIN
  --
  
    INSERT INTO party_roles
      (id, rt_code, prty_id, start_date, end_date, description)
    VALUES
      (prty_seq.NEXTVAL,
       p_role_code,
       p_prty_id,
       SYSDATE,
       NULL,
       'added by ' || v('APP_USER'));
  
    --
  EXCEPTION
    WHEN OTHERS THEN
      ROLLBACK;
      RAISE;
  END add_party_role;
  --
  
  /**
     allows a party relationship to be created e.g. associating a party with a supplier
    
    */ 
  PROCEDURE create_relationship(p_from_prty_id   IN VARCHAR2,
                                p_from_prty_role IN VARCHAR2,
                                p_to_prty_id     IN VARCHAR2,
                                p_to_prty_role   IN VARCHAR2) IS
    l_to_parl_id NUMBER;
    l_from_parl_id NUMBER;
  BEGIN
    -- get the role IDs
    SELECT parl.id
      INTO l_to_parl_id
      FROM party_roles parl
     WHERE parl.prty_id = p_to_prty_id
       AND parl.rt_code = p_to_prty_role;
     
    SELECT parl.id
      INTO l_from_parl_id
      FROM party_roles parl
     WHERE parl.prty_id = p_from_prty_id
       AND parl.rt_code = p_from_prty_role;
     
    -- now do the insert
    INSERT INTO party_relationships
      (from_parl_id,
       to_parl_id,
       from_parl_prty_id,
       to_parl_prty_id,
       from_parl_rt_code,
       to_parl_rt_code,
       start_date,
       from_prtp_rt_code,
       to_prtp_rt_code,
       end_date)
    VALUES
      (l_from_parl_id,
       l_to_parl_id,
       p_from_prty_id,
       p_to_prty_id,
       p_from_prty_role,
       p_to_prty_role,
       SYSDATE,
       p_from_prty_role,
       p_to_prty_role,
       NULL);
  END create_relationship;
  --
  
  /**
     expires any old addresses without an end date.  To be used when a new address is assigned to a party
     The address's end date is set to today as that is when the new address is valid from.  No setting
     addresses to be used in the future.
     
     %param p_prty_id the ID of the party
     %param p_addr_code the code of the new address.
     %param p_addr_type the type of address HOME/OFFICE
    */
  PROCEDURE expire_old_addresses(p_prty_id IN parties.id%TYPE,
                                 p_addr_code IN addresses.code%TYPE,
                                 p_paddr_type IN party_address_roles.rt_code%TYPE) IS
  BEGIN
    UPDATE party_addresses paddr
       SET paddr.end_date = SYSDATE, 
           paddr.comments = paddr.comments ||' Address end date set by '||v('APP_USER')
     WHERE paddr.prty_id = p_prty_id
       AND NOT paddr.addr_code = p_addr_code --don't want to update the new one!!!
       AND paddr.end_date IS NULL
       AND paddr.addr_code IN (SELECT paddr_roles.paddr_addr_code
                                 FROM party_address_roles paddr_roles
                                 WHERE paddr_roles.rt_code = p_paddr_type);
    
  END expire_old_addresses;
  --
  
  /** 
       REMOVE_PARTY
       removes the party and all related information from the system.
       
       %param p_prty_id - the ID of the party to remove.
      */
  PROCEDURE remove_party(p_prty_id IN NUMBER) IS
  BEGIN
    --delete all party relationships with other parties
    DELETE FROM party_relationships parl
    WHERE parl.from_parl_prty_id = p_prty_id;
    
    DELETE FROM party_relationships parl
    WHERE parl.to_parl_prty_id = p_prty_id;
    
    --delete contact mechanisms
    FOR cur_come IN (SELECT * FROM party_contact_mechanisms come WHERE come.prty_id = p_prty_id) LOOP
    
      --delete the party/contact mechanism link
      DELETE FROM party_contact_mechanisms come
        WHERE come.prty_id = p_prty_id
        AND come.come_id = cur_come.come_id;
        
      -- delete the contact mechanism
      DELETE FROM contact_mechanisms come
      WHERE come.id = cur_come.come_id;
    END LOOP;
    
    --delete addresses
    FOR cur_addr IN (SELECT * FROM party_addresses paddr WHERE paddr.prty_id = p_prty_id) LOOP
      --delete the address roles
      DELETE FROM party_address_roles paddr_role
      WHERE paddr_role.paddr_prty_id = p_prty_id;
      
      --delete the party address links
      DELETE FROM party_addresses paddr
      WHERE paddr.prty_id = p_prty_id 
      AND paddr.addr_code = cur_addr.addr_code;
      
    END LOOP;
    
    -- remove the passwords
    DELETE FROM passwords pword
    WHERE prty_id = p_prty_id;
    
    -- remove the party roles
    DELETE FROM party_roles parl
    WHERE parl.prty_id = p_prty_id;
    
    -- finally, delete the party
    DELETE FROM parties prty
    WHERE prty.id = p_prty_id;
    
    -- commit the deletes
    COMMIT;
  -- if we have any problems then rollback
  EXCEPTION
    WHEN OTHERS THEN
     ROLLBACK;
     RAISE;
  END remove_party;
END mip_parties;
/
