DROP PACKAGE ADR.ADR_UTIL;

CREATE OR REPLACE PACKAGE ADR.adr_util AUTHID CURRENT_USER
AS
  PROCEDURE ADR_PIN_SEQ;
  PROCEDURE ADR_UNPIN_SEQ;
  PROCEDURE ADR_PIN_TRIGGERS;
  PROCEDURE ADR_UNPIN_TRIGGERS;
END adr_util;
/


CREATE OR REPLACE PUBLIC SYNONYM ADR_UTIL FOR ADR.ADR_UTIL;


GRANT EXECUTE ON ADR.ADR_UTIL TO AACDBA;
DROP PACKAGE ADR.HL7_ARCHIVE_PKG;

CREATE OR REPLACE PACKAGE ADR.Hl7_Archive_Pkg -- AUTHID CURRENT_USER
AS
-- Declare externally callable procedures

  FUNCTION Hl7_Archive_Control
    (PN_status_code
      IN     NUMBER)
    RETURN VARCHAR2;

  FUNCTION Hl7_Delete_Control
    (PN_status_code
      IN     NUMBER)
    RETURN VARCHAR2;

  PROCEDURE Hl7_Process_Archives
    (PB_run_standalone
       IN     BOOLEAN DEFAULT FALSE,
     PB_allow_rpt_inst
       IN     BOOLEAN DEFAULT FALSE
    );

  PROCEDURE Hl7_Process_Deletes
    (PB_run_standalone
       IN     BOOLEAN DEFAULT FALSE,
     PB_allow_rpt_inst
       IN     BOOLEAN DEFAULT FALSE
    );

END Hl7_Archive_Pkg;
/


GRANT EXECUTE ON ADR.HL7_ARCHIVE_PKG TO EEUSR;
DROP PACKAGE ADR.IAM_UTIL_PKG;

CREATE OR REPLACE PACKAGE ADR.IAM_UTIL_PKG AUTHID CURRENT_USER
AS

  TYPE varchar2_table IS TABLE OF VARCHAR2(32767) INDEX BY BINARY_INTEGER;

  PROCEDURE USERS_IAM_INSERT_SP(
    p_USERNAME        IN USERS.USERNAME%TYPE,
    p_LAST_NAME        IN USERS.LAST_NAME%TYPE,
    p_FIRST_NAME        IN USERS.FIRST_NAME%TYPE,
    p_MIDDLE_NAME        IN USERS.MIDDLE_NAME%TYPE,
    p_STD_INSTITUTION_ID    IN STD_INSTITUTION.ID%TYPE,
    p_JOB_TITLE        IN USERS.JOB_TITLE%TYPE,
    p_PASSWORD        IN USERS.PASSWORD%TYPE,
    p_ACCOUNT_EXPIRE_DATE    IN USERS.ACCOUNT_EXPIRE_DATE%TYPE,
    p_PASSWORD_EXPIRE_DATE    IN USERS.PASSWORD_EXPIRE_DATE%TYPE,
    p_AGREEMENT_SIGNATURE_CODE IN USERS.AGREEMENT_SIGNATURE_CODE%TYPE,
    p_PASSWORD_CHANGE_DATE    IN USERS.PASSWORD_CHANGE_DATE%TYPE,
    p_PASSWORD_CREATE_DATE    IN USERS.PASSWORD_CREATE_DATE%TYPE,
    p_INACTIVE_FLAG        IN USERS.INACTIVE_FLAG%TYPE,
    p_NUMBER_OF_LOGIN_ATTEMPTS IN USERS.NUMBER_OF_LOGIN_ATTEMPTS%TYPE,
    p_SIGNATURE_VERIFIED_IND   IN USERS.SIGNATURE_VERIFIED_IND%TYPE,
    p_ROLES            IN VARCHAR2);

  PROCEDURE USERS_IAM_UPDATE_SP (
    p_USERNAME        IN USERS.USERNAME%TYPE,
    p_LAST_NAME        IN USERS.LAST_NAME%TYPE,
    p_FIRST_NAME        IN USERS.FIRST_NAME%TYPE,
    p_MIDDLE_NAME        IN USERS.MIDDLE_NAME%TYPE,
    p_STD_INSTITUTION_ID    IN STD_INSTITUTION.ID%TYPE,
    p_JOB_TITLE        IN USERS.JOB_TITLE%TYPE,
    p_PASSWORD        IN USERS.PASSWORD%TYPE,
    p_ACCOUNT_EXPIRE_DATE    IN USERS.ACCOUNT_EXPIRE_DATE%TYPE,
    p_PASSWORD_EXPIRE_DATE    IN USERS.PASSWORD_EXPIRE_DATE%TYPE,
    p_AGREEMENT_SIGNATURE_CODE IN USERS.AGREEMENT_SIGNATURE_CODE%TYPE,
    p_PASSWORD_CHANGE_DATE    IN USERS.PASSWORD_CHANGE_DATE%TYPE,
    p_PASSWORD_CREATE_DATE    IN USERS.PASSWORD_CREATE_DATE%TYPE,
    p_INACTIVE_FLAG        IN USERS.INACTIVE_FLAG%TYPE,
    p_NUMBER_OF_LOGIN_ATTEMPTS IN USERS.NUMBER_OF_LOGIN_ATTEMPTS%TYPE,
    p_SIGNATURE_VERIFIED_IND   IN USERS.SIGNATURE_VERIFIED_IND%TYPE,
    p_ROLES            IN VARCHAR2);

  PROCEDURE USERS_IAM_DELETE_SP (
    p_USERNAME    IN     USERS.USERNAME%TYPE);

  PROCEDURE delimstring_to_table
    ( p_delimstring IN VARCHAR2
    , p_table OUT varchar2_table
    , p_nfields OUT INTEGER
    , p_delim IN VARCHAR2 DEFAULT ','
    );

  PROCEDURE table_to_delimstring
    ( p_table IN varchar2_table
    , p_delimstring OUT VARCHAR2
    , p_delim IN VARCHAR2 DEFAULT ','
    );

END IAM_UTIL_PKG;
/


CREATE OR REPLACE PUBLIC SYNONYM IAM_UTIL_PKG FOR ADR.IAM_UTIL_PKG;


GRANT EXECUTE ON ADR.IAM_UTIL_PKG TO IAMUSR;
DROP PACKAGE ADR.MEC_PERIOD_PKG;

CREATE OR REPLACE PACKAGE ADR.MEC_PERIOD_PKG
AS
   /******************************************************************************
      NAME:       MEC_PERIOD_NEXT_GEN
      PURPOSE: Daily update¿MEC_PERIOD_NEXT_GEN for new changes

      REVISIONS:
      Ver        Date        Author           Description
      ---------  ----------  ---------------  ------------------------------------
      1.0        Feb/04/2016      vhaistdheera       1. Created this package.
   ******************************************************************************/
   g_pkg_name                     CONSTANT VARCHAR2 (50) := 'MEC_PERIOD_PKG';
   g_param_name_for_lower_bound   CONSTANT VARCHAR2 (60) := 'MEC_PERIOD_LOWER_BOUND';
   g_final_rec_cnt                         INTEGER := 0;
   g_job_name                              VARCHAR2 (60);
   g_current_lower_bound                   INTEGER;

   -- This is only a Public procedure that is available for execution and rest of the procedures are all private.
   PROCEDURE KICK_OFF_MEC_PERIOD;
END MEC_PERIOD_PKG;
/


CREATE OR REPLACE PUBLIC SYNONYM MEC_PERIOD_PKG FOR ADR.MEC_PERIOD_PKG;
