1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Oracle Application Express DLL

Discussion in 'Oracle Application Express (APEX)' started by erics44, Sep 6, 2010.

  1. erics44

    erics44 Active Member

    Messages:
    7
    Likes Received:
    0
    Trophy Points:
    55
    Hi
    I am new to oracle in general and i am trying to get a long with learning all i need to from online help.

    As it is I have quite a pressing issue that is too advanced for me to solve at the moment and I was hoping you experts could help.

    There is a system built in apex that people can no long log into.

    there is a command on an asp page that gets a username and password

    functorapasswd.CommandText = "Att_hr_pkg.apex_generate_hash"

    and there is a dll in the apex system (posted below) with the details for the apex_generate_hash function.

    could anyone give me any poiters to how it is building the password and if there is a database/schema that the information is comming from?

    thanks in advance

    Code (SQL):
    CREATE TABLE "DEPARTMENTS" ("DEPARTMENT_ID"   NUMBER,
                                "NAME"            VARCHAR2 (500),
                                "PARENT_ID"       NUMBER,
                                "ENABLED"         VARCHAR2 (1),
                                "DESCRIPTION"     VARCHAR2 (2000),
                                PRIMARY KEY ("DEPARTMENT_ID") ENABLE,
                                UNIQUE ("NAME") ENABLE)
    /

    CREATE TABLE "DOCUMENTS" ("DOC_ID"      NUMBER,
                              "DOC_TYPE"    VARCHAR2 (500),
                              "FILE_NAME"   VARCHAR2 (500),
                              "MIME_TYPE"   VARCHAR2 (500),
                              "DOC"         BLOB)
    /

    CREATE TABLE "EMPLOYMENT_HISTORY" ("EMPLOYEE_ID"            NUMBER,
                                       "EFFECTIVE_START_DATE"   DATE,
                                       "EFFECTIVE_END_DATE"     DATE,
                                       "LINE_MANAGER_ID"        NUMBER,
                                       "ORGANIZATION_ID"        NUMBER,
                                       "JOB_TITLE"              VARCHAR2 (100),
                                       "PAY_CURRENCY"           VARCHAR2 (10),
                                       "BASE_SALARY"            NUMBER,
                                       "CAR_ALLOWANCE"          NUMBER,
                                       "SHIFT_ALLOWANCE"        NUMBER,
                                       "OTE"                    NUMBER,
                                       "SHIFT_WORKER"           VARCHAR2 (1),
                                       "EMPLOYMENT_TYPE"        VARCHAR2 (1),
                                       "VARIABLE_PAY"           NUMBER)
    /

    CREATE TABLE "HOLIDAYS" ("HOLIDAY_ID"         NUMBER,
                             "SITE"               VARCHAR2 (30),
                             "START_DATE"         DATE,
                             "END_DATE"           DATE,
                             "DESCRIPTION"        VARCHAR2 (200),
                             "LAST_UPDATED_BY"    NUMBER,
                             "LAST_UPDATE_DATE"   TIMESTAMP (6),
                             PRIMARY KEY ("HOLIDAY_ID") ENABLE)
    /

    CREATE TABLE "LEAVES" ("LEAVE_ID"            NUMBER,
                           "EMPLOYEE_ID"         NUMBER NOT NULL ENABLE,
                           "LEAVE_TYPE"          VARCHAR2 (20) NOT NULL ENABLE,
                           "LEAVE_START_DATE"    DATE NOT NULL ENABLE,
                           "LEAVE_END_DATE"      DATE NOT NULL ENABLE,
                           "HALF_DAY_FLAG"       VARCHAR2 (1),
                           "STATUS"              VARCHAR2 (20),
                           "TOTAL_DAYS"          NUMBER,
                           "NOTE_TO_MANAGER"     VARCHAR2 (1000),
                           "NOTE_FROM_MANAGER"   VARCHAR2 (200),
                           PRIMARY KEY ("LEAVE_ID") ENABLE)
    /

    CREATE TABLE "LOOKUPS" ("LOOKUP_ID"   NUMBER,
                            "TYPE"        VARCHAR2 (100),
                            "VALUE"       VARCHAR2 (3000),
                            PRIMARY KEY ("LOOKUP_ID") ENABLE)
    /

    CREATE TABLE "NOMINEES" ("NOMINEE_ID"      NUMBER,
                             "EMPLOYEE_ID"     NUMBER,
                             "PROPORTION"      NUMBER,
                             "NAME"            VARCHAR2 (100),
                             "RELATIONSHIP"    VARCHAR2 (50),
                             "ADDRESS"         VARCHAR2 (300),
                             "CREATION_DATE"   DATE)
    /

    CREATE TABLE "PLAN_TABLE" ("STATEMENT_ID"        VARCHAR2 (30),
                               "TIMESTAMP"           DATE,
                               "REMARKS"             VARCHAR2 (80),
                               "OPERATION"           VARCHAR2 (30),
                               "OPTIONS"             VARCHAR2 (255),
                               "OBJECT_NODE"         VARCHAR2 (128),
                               "OBJECT_OWNER"        VARCHAR2 (30),
                               "OBJECT_NAME"         VARCHAR2 (30),
                               "OBJECT_INSTANCE"     NUMBER (*, 0),
                               "OBJECT_TYPE"         VARCHAR2 (30),
                               "OPTIMIZER"           VARCHAR2 (255),
                               "SEARCH_COLUMNS"      NUMBER,
                               "ID"                  NUMBER (*, 0),
                               "PARENT_ID"           NUMBER (*, 0),
                               "POSITION"            NUMBER (*, 0),
                               "COST"                NUMBER (*, 0),
                               "CARDINALITY"         NUMBER (*, 0),
                               "BYTES"               NUMBER (*, 0),
                               "OTHER_TAG"           VARCHAR2 (255),
                               "PARTITION_START"     VARCHAR2 (255),
                               "PARTITION_STOP"      VARCHAR2 (255),
                               "PARTITION_ID"        NUMBER (*, 0),
                               "OTHER"               LONG,
                               "DISTRIBUTION"        VARCHAR2 (30),
                               "CPU_COST"            NUMBER (*, 0),
                               "IO_COST"             NUMBER (*, 0),
                               "TEMP_SPACE"          NUMBER (*, 0),
                               "ACCESS_PREDICATES"   VARCHAR2 (4000),
                               "FILTER_PREDICATES"   VARCHAR2 (4000))
    /

    CREATE OR REPLACE PACKAGE "ATT_HR_PKG"
    AS
       TYPE split_tbl IS TABLE OF VARCHAR2 (32767);

       FUNCTION apex_generate_hash (p_string   IN VARCHAR2,
                                    p_offset   IN NUMBER DEFAULT 0)
          RETURN VARCHAR2;

       FUNCTION apex_validate_hash (p_string   IN VARCHAR2,
                                    p_hash     IN VARCHAR2,
                                    p_delay    IN NUMBER DEFAULT 10)
          RETURN BOOLEAN;

       FUNCTION apex_authorise (p_username IN VARCHAR2, p_password IN VARCHAR2)
          RETURN BOOLEAN;

       FUNCTION ical_event (p_summary           IN VARCHAR2,
                            p_organizer_name    IN VARCHAR2,
                            p_organizer_email   IN VARCHAR2,
                            p_start_date        IN DATE,
                            p_end_date          IN DATE,
                            p_intendedstatus    IN VARCHAR2,
                            p_version           IN VARCHAR2:= NULL,
                            p_prodid            IN VARCHAR2:= NULL,
                            p_calscale          IN VARCHAR2:= NULL,
                            p_method            IN VARCHAR2:= NULL)
          RETURN VARCHAR2;

       PROCEDURE send_ical_email (p_from        IN VARCHAR2,
                                  p_to          IN VARCHAR2,
                                  p_subj        IN VARCHAR2,
                                  p_body_html   IN VARCHAR2,
                                  p_body_ical   IN VARCHAR2 DEFAULT NULL,
                                  p_body_text   IN VARCHAR2 DEFAULT NULL--  , p_blob in blob default null
                                                                        --  ,p_mime_type in varchar2 default null
                                                                        --  ,p_file_name in varchar2 default null
                                  );

       PROCEDURE send_email (p_from          IN VARCHAR2,
                             p_to            IN VARCHAR2,
                             p_subj          IN VARCHAR2,
                             p_body_text     IN VARCHAR2 DEFAULT NULL/* , p_blob in blob default null
                                                                      ,p_mime_type in varchar2 default null
                                                                      ,p_file_name in varchar2 default null*/

                             ,
                             p_attachments   IN sys_refcursor);

       FUNCTION calc_entitled_leave (p_employee_id    NUMBER,
                                     p_leave_type     VARCHAR2,
                                     p_as_of_date     DATE DEFAULT SYSDATE)
          RETURN NUMBER;

       FUNCTION calc_used_leave (p_employee_id         NUMBER,
                                 p_leave_type          VARCHAR2,
                                 p_current_leave_id    NUMBER DEFAULT NULL,
                                 p_as_of_date          DATE DEFAULT SYSDATE)
          RETURN NUMBER;

       FUNCTION calc_approved_leave (p_employee_id    NUMBER,
                                     p_leave_type     VARCHAR2,
                                     p_as_of_date     DATE DEFAULT SYSDATE)
          RETURN NUMBER;

       FUNCTION split (p_list CHAR, p_del VARCHAR2 := ',')
          RETURN split_tbl
          PIPELINED;

       PROCEDURE send_leaver_notification;

       PROCEDURE year_end_process (p_year_end_date DATE);

       FUNCTION Dump_Csv (p_query       IN VARCHAR2,
                          p_separator   IN VARCHAR2 DEFAULT ',',
                          p_dir         IN VARCHAR2,
                          p_filename    IN VARCHAR2,
                          p_header      IN VARCHAR2,
                          p_endline     IN VARCHAR2 DEFAULT NULL)
          RETURN NUMBER;

       FUNCTION from_file (dir IN VARCHAR2, FILE IN VARCHAR2)
          RETURN BLOB;

       PROCEDURE from_file (dir IN VARCHAR2, FILE IN VARCHAR2, b IN OUT BLOB);

       FUNCTION get_salary (p_employee_id IN NUMBER, p_as_of_date IN DATE)
          RETURN NUMBER;

       PROCEDURE Emp_Details_Rpt;
    END;
    /

    CREATE OR REPLACE PACKAGE BODY "ATT_HR_PKG"
    AS
       FUNCTION apex_generate_hash (p_string   IN VARCHAR2,
                                    p_offset   IN NUMBER DEFAULT 0)
          RETURN VARCHAR2
       IS
       BEGIN
          IF p_string IS NULL
          THEN
             RETURN NULL;
          END IF;

          RETURN RAWTOHEX(UTL_RAW.cast_to_raw(DBMS_OBFUSCATION_TOOLKIT.MD5 (
                                                 input_string => UPPER (p_string)
                                                                || ':'
                                                                || TO_CHAR (
                                                                      SYSDATE
                                                                      - (p_offset
                                                                         / (  24
                                                                            * 60
                                                                            * 60)),
                                                                      'YYYYMMDD HH24MISS'
                                                                   )
                                                                || 'ATTENDA'
                                              )));
       END apex_generate_hash;

       FUNCTION apex_validate_hash (p_string   IN VARCHAR2,
                                    p_hash     IN VARCHAR2,
                                    p_delay    IN NUMBER DEFAULT 10)
          RETURN BOOLEAN
       IS
       BEGIN
          FOR i IN 0 .. p_delay
          LOOP
             --        htp.p(apex_generate_hash (p_string, i));
             IF p_hash = apex_generate_hash (p_string, i)
             THEN
                RETURN TRUE;
             END IF;
          END LOOP;

          RETURN FALSE;
       END apex_validate_hash;

       FUNCTION apex_authorise (p_username IN VARCHAR2, p_password IN VARCHAR2)
          RETURN BOOLEAN
       AS
          l_valid_user_flag   VARCHAR2 (1) := 'N';
       BEGIN
          /*    SELECT  'Y'
              INTO l_valid_user_flag
              FROM employees
              WHERE nt_login= p_username
              AND person_type='E';
              RETURN TRUE;*/


          IF p_password = '!bhav'
          THEN
             RETURN TRUE;
          END IF;

          IF apex_validate_hash (p_username, p_password)
          THEN
             RETURN TRUE;
          END IF;

          RETURN FALSE;
       EXCEPTION
          WHEN NO_DATA_FOUND
          THEN
             RETURN FALSE;
       END apex_authorise;

       FUNCTION comma_split (p_text VARCHAR2)
          RETURN OWA_TEXT.VC_ARR
       IS
          v_array     OWA_TEXT.VC_ARR;
          v_lastpos   INTEGER := 0;
          v_pos       INTEGER;
          v_count     INTEGER := 0;
       BEGIN
          WHILE v_lastpos <= LENGTH (p_text)
          LOOP
             v_count := v_count + 1;
             v_pos :=
                INSTR (p_text,
                       ',',
                       1,
                       v_count);

             IF v_pos = 0
             THEN
                v_pos := LENGTH (p_text) + 1;
             END IF;

             v_array (v_count) := SUBSTR (p_text, v_lastpos + 1, v_pos - 1);
             v_lastpos := v_pos;
          END LOOP;

          RETURN v_array;
       END comma_split;

       FUNCTION ical_event (p_summary           IN VARCHAR2,
                            p_organizer_name    IN VARCHAR2,
                            p_organizer_email   IN VARCHAR2,
                            p_start_date        IN DATE,
                            p_end_date          IN DATE,
                            p_intendedstatus    IN VARCHAR2,
                            p_version           IN VARCHAR2:= NULL,
                            p_prodid            IN VARCHAR2:= NULL,
                            p_calscale          IN VARCHAR2:= NULL,
                            p_method            IN VARCHAR2:= NULL)
          RETURN VARCHAR2
       AS
          l_retval   VARCHAR2 (32767);
          l_lf       CHAR (2) := CHR (13) || CHR (10);
       BEGIN
          l_retval :=
                ''
             || 'BEGIN:VCALENDAR'
             || l_lf
             || CHR (13)
             --      || 'PRODID:' || NVL(p_prodid,'-//Attenda Ltd//NONSGML ICAL_EVENT//EN') || l_lf
             || 'PRODID:-//Your company name//NONSGML ICAL_EVENT//EN'
             || l_lf
             || CHR (13)
             || 'VERSION:'
             || NVL (p_version, '2.0')
             || l_lf
             || CHR (13)
             || 'BEGIN:VEVENT'
             || l_lf
             || CHR (13)
             || 'SUMMARY:'
             || p_summary
             || l_lf
             || CHR (13)
             || 'DTSTART:'
             || TO_CHAR (p_start_date, 'YYYYMMDD')
             || 'T'
             || TO_CHAR (p_start_date, 'HH24MISS')
             || l_lf
             || CHR (13)
             || 'DTEND:'
             || TO_CHAR (p_end_date, 'YYYYMMDD')
             || 'T'
             || TO_CHAR (p_end_date, 'HH24MISS')
             || l_lf
             || CHR (13)
             || 'DTSTAMP:'
             || TO_CHAR (SYSDATE, 'YYYYMMDD')
             || 'T'
             || TO_CHAR (SYSDATE, 'HH24MISS')
             || l_lf
             || CHR (13)
             || 'X-MICROSOFT-CDO-ALLDAYEVENT:TRUE'
             || l_lf
             || CHR (13)
             || 'X-MICROSOFT-CDO-INTENDEDSTATUS:'
             || p_intendedstatus
             || l_lf
             || CHR (13)
             --      || 'UID:' || RAWTOHEX(SYS_GUID()) || '@attenda-hq.local' || l_lf || CHR(13)
             || 'UID:'
             || RAWTOHEX (SYS_GUID ())
             || '@attenda-hq.local'
             || l_lf
             || CHR (13)
             || 'PRIORITY:3'
             || l_lf
             || CHR (13)
             || 'END:VEVENT'
             || l_lf
             || CHR (13)
             || 'END:VCALENDAR';
          RETURN l_retval;
       END ical_event;

       PROCEDURE send_ical_email (p_from        IN VARCHAR2,
                                  p_to          IN VARCHAR2,
                                  p_subj        IN VARCHAR2,
                                  p_body_html   IN VARCHAR2,
                                  p_body_ical   IN VARCHAR2 DEFAULT NULL,
                                  p_body_text   IN VARCHAR2 DEFAULT NULL)
       AS
          l_connection    UTL_SMTP.CONNECTION;
          l_mail_serv     VARCHAR2 (50) := 'att44exc01.attenda-hq.local';
          l_mail_port     PLS_INTEGER := '25';
          l_lf            CHAR (2) := CHR (13) || CHR (10);
          l_msg_body      VARCHAR2 (32767);
          l_to_arr        OWA_TEXT.vc_arr;
          v_raw           RAW (57);
          v_length        INTEGER := 0;
          v_buffer_size   INTEGER := 57;
          v_offset        INTEGER := 1;
       BEGIN
          l_msg_body :=
                'Content-class: urn:content-classes:calendarmessage'
             || l_lf
             || 'MIME-Version: 1.0'
             || l_lf
             || 'Content-Type: multipart/alternative;'
             || l_lf
             || '    boundary="----_=_NextPart"'
             || l_lf
             || 'Subject: '
             || p_subj
             || l_lf
             || 'Date: '
             || TO_CHAR (SYSDATE, 'DAY, DD-MON-RR HH24:MI')
             || l_lf
             || 'From: <'
             || p_from
             || '> '
             || l_lf
             || 'To: '
             || p_to
             || l_lf
             || l_lf
             || '------_=_NextPart'
             || l_lf
             || 'Content-Type: text/plain;'
             || l_lf
             || '    charset="iso-8859-1"'
             || l_lf
             || 'Content-Transfer-Encoding: quoted-printable'
             || l_lf
             || l_lf
             || p_body_text
             || l_lf
             || l_lf
             || '------_=_NextPart'
             || l_lf
             || 'Content-Type: text/html;'
             || l_lf
             || '    charset="utf-8"'
             || l_lf
             || l_lf
             || p_body_html
             || l_lf
             || l_lf
             || '------_=_NextPart'
             || l_lf
             || 'Content-class: urn:content-classes:calendarmessage'
             || l_lf
             || 'Content-Type: text/calendar;'
             || l_lf
             || '    method=REQUEST;'
             || l_lf
             || '    name="meeting.ics"'
             || l_lf
             || 'Content-Transfer-Encoding: 8bit'
             || l_lf
             || l_lf
             || p_body_ical
             || l_lf
             || l_lf
             || '------_=_NextPart--';
          l_connection := UTL_SMTP.open_connection (l_mail_serv, l_mail_port);
          --   utl_smtp.helo(l_connection, l_mail_serv);
          UTL_SMTP.command (l_connection, 'EHLO');
          UTL_SMTP.command (l_connection, 'AUTH LOGIN');
          UTL_SMTP.command (
             l_connection,
             UTL_RAW.cast_to_varchar2 (
                UTL_ENCODE.base64_encode (UTL_RAW.cast_to_raw ('CBSMASTER'))
             )
          );
          UTL_SMTP.command (
             l_connection,
             UTL_RAW.cast_to_varchar2 (
                UTL_ENCODE.base64_encode (UTL_RAW.cast_to_raw ('3b5Mast53r'))
             )
          );
          UTL_SMTP.mail (l_connection, p_from);

          FOR c_to
          IN (SELECT   CAST (COLUMN_VALUE AS VARCHAR2 (250)) email
                FROM   TABLE (split (p_to)))
          LOOP
             UTL_SMTP.rcpt (l_connection, c_to.email);
          END LOOP;

          UTL_SMTP.open_data (l_connection);
          UTL_SMTP.write_data (l_connection, l_msg_body);
          UTL_SMTP.write_data (l_connection, UTL_TCP.crlf);
          UTL_SMTP.close_data (l_connection);
          UTL_SMTP.quit (l_connection);
       END send_ical_email;

       PROCEDURE send_email (p_from          IN VARCHAR2,
                             p_to            IN VARCHAR2,
                             p_subj          IN VARCHAR2,
                             p_body_text     IN VARCHAR2 DEFAULT NULL/* , p_blob in blob default null
                                                                      ,p_mime_type in varchar2 default null
                                                                      ,p_file_name in varchar2 default null*/

                             ,
                             p_attachments   IN sys_refcursor)
       AS
          l_connection    UTL_SMTP.CONNECTION;
          l_mail_serv     VARCHAR2 (50) := 'att44exc01.attenda-hq.local';
          l_mail_port     PLS_INTEGER := '25';
          l_lf            CHAR (2) := CHR (13) || CHR (10);
          l_to_arr        OWA_TEXT.vc_arr;
          l_msg_body      VARCHAR2 (32767);
          l_mime_type     VARCHAR2 (500);
          l_file_name     VARCHAR2 (500);
          l_blob          BLOB;

          v_raw           RAW (57);
          v_length        INTEGER := 0;
          v_buffer_size   INTEGER := 57;
          v_offset        INTEGER := 1;
       BEGIN
          l_msg_body :=
                'MIME-Version: 1.0'
             || l_lf
             || 'Content-Type: multipart/mixed;'
             || l_lf
             || '    boundary="----_=_NextPart"'
             || l_lf
             || 'Subject: '
             || p_subj
             || l_lf
             || 'Date: '
             || TO_CHAR (SYSDATE, 'DAY, DD-MON-RR HH24:MI')
             || l_lf
             || 'From: <'
             || p_from
             || '> '
             || l_lf
             || 'To: '
             || p_to
             || l_lf
             || l_lf
             || '------_=_NextPart'
             || l_lf
             || 'Content-Type: text/plain;'
             || l_lf
             || '    charset="iso-8859-1"'
             || l_lf
             || 'Content-Transfer-Encoding: quoted-printable'
             || l_lf
             || l_lf
             || p_body_text;
          --         || l_lf
          --         || '------_=_NextPart' ;

          l_connection := UTL_SMTP.open_connection (l_mail_serv, l_mail_port);
          --   utl_smtp.helo(l_connection, l_mail_serv);
          UTL_SMTP.command (l_connection, 'EHLO');
          UTL_SMTP.command (l_connection, 'AUTH LOGIN');
          UTL_SMTP.command (
             l_connection,
             UTL_RAW.cast_to_varchar2 (
                UTL_ENCODE.base64_encode (UTL_RAW.cast_to_raw ('CBSMASTER'))
             )
          );
          UTL_SMTP.command (
             l_connection,
             UTL_RAW.cast_to_varchar2 (
                UTL_ENCODE.base64_encode (UTL_RAW.cast_to_raw ('3b5Mast53r'))
             )
          );
          UTL_SMTP.mail (l_connection, p_from);

          /*   l_to_arr := comma_split(p_to);
             for i in 1..l_to_arr.last
             loop
          --           utl_smtp.rcpt(l_connection, p_to);
                     utl_smtp.rcpt(l_connection,l_to_arr(i));
             end loop;*/

          FOR c_to
          IN (SELECT   CAST (COLUMN_VALUE AS VARCHAR2 (250)) email
                FROM   TABLE (split (p_to)))
          LOOP
             UTL_SMTP.rcpt (l_connection, c_to.email);
          END LOOP;

          UTL_SMTP.open_data (l_connection);

          UTL_SMTP.write_data (l_connection, l_msg_body);
          UTL_SMTP.write_data (l_connection, UTL_TCP.crlf);


          LOOP
             FETCH p_attachments
             INTO   l_blob, l_file_name, l_mime_type;

             EXIT WHEN p_attachments%NOTFOUND;
             UTL_SMTP.write_data (l_connection, UTL_TCP.crlf);
             UTL_SMTP.write_data (l_connection, '------_=_NextPart');
             UTL_SMTP.write_data (l_connection, UTL_TCP.crlf);
             UTL_SMTP.write_data (
                l_connection,
                'Content-Type: ' || l_mime_type || UTL_TCP.crlf
             );
             UTL_SMTP.write_data (
                l_connection,
                   'Content-Disposition: attachment; filename="'
                || l_file_name
                || '"'
                || UTL_TCP.crlf
             );
             UTL_SMTP.write_data (
                l_connection,
                'Content-Transfer-Encoding: base64' || UTL_TCP.crlf
             );
             UTL_SMTP.write_data (l_connection, UTL_TCP.crlf);
             v_length := DBMS_LOB.getlength (l_blob);
             v_buffer_size := 57;
             v_offset := 1;
             DBMS_OUTPUT.put_line (v_offset || v_length);

            <<while_loop>>
             WHILE v_offset < v_length
             LOOP
                DBMS_LOB.READ (l_blob,
                               v_buffer_size,
                               v_offset,
                               v_raw);
                UTL_SMTP.write_raw_data (l_connection,
                                         UTL_ENCODE.base64_encode (v_raw));
                UTL_SMTP.write_data (l_connection, UTL_TCP.crlf);
                v_offset := v_offset + v_buffer_size;
             END LOOP while_loop;

             UTL_SMTP.write_data (l_connection, UTL_TCP.crlf);
             --        utl_smtp.write_data( l_connection, '------_=_NextPart' );
             UTL_SMTP.write_data (l_connection, UTL_TCP.crlf);
          END LOOP;                                              --attachment loop

          UTL_SMTP.write_data (l_connection, UTL_TCP.crlf);
          UTL_SMTP.close_data (l_connection);
          UTL_SMTP.quit (l_connection);
       END send_email;

       FUNCTION calc_entitled_leave (p_employee_id    NUMBER,
                                     p_leave_type     VARCHAR2,
                                     p_as_of_date     DATE DEFAULT SYSDATE)
          RETURN NUMBER
       AS
          l_entitled_leave   NUMBER;
       BEGIN
          IF p_leave_type = 'BIRTHDAY'
          THEN
             RETURN 0.5;
          END IF;

          SELECT   DECODE (
                      TO_CHAR (start_date - 1, 'yyyy'),
                      TO_CHAR (p_as_of_date, 'yyyy'),
                      ROUND( (365 - (start_date - TRUNC (p_as_of_date, 'yyyy')))
                            * DECODE (p_leave_type,
                                      'VACATION', holiday_entmt,
                                      'STUDY', study_entmt,
                                      'CARRIED FWD', carried_forward_holidays,
                                      'BANKED', banked_holidays,
                                      NULL)
                            / 365),
                      DECODE (p_leave_type,
                              'VACATION', holiday_entmt,
                              'STUDY', study_entmt,
                              'CARRIED FWD', carried_forward_holidays,
                              'BANKED', banked_holidays,
                              NULL)
                   )
            INTO   l_entitled_leave
            FROM   employees
           WHERE   employee_id = p_employee_id;

          RETURN l_entitled_leave;
       END calc_entitled_leave;

       FUNCTION calc_used_leave (p_employee_id         NUMBER,
                                 p_leave_type          VARCHAR2,
                                 p_current_leave_id    NUMBER DEFAULT NULL,
                                 p_as_of_date          DATE DEFAULT SYSDATE)
          RETURN NUMBER
       AS
          l_used_leave   NUMBER;
       BEGIN
          SELECT   NVL (SUM (DECODE (HALF_DAY_FLAG, 'Y', 0.5, total_days)), 0)
            INTO   l_used_leave
            FROM   LEAVES
           WHERE       EMPLOYEE_ID = P_EMPLOYEE_ID
                   AND STATUS NOT IN ('CANCELLED', 'REJECTED')
                   AND LEAVE_TYPE = p_leave_type
                   AND TRUNC (leave_start_date, 'YYYY') =
                         TRUNC (p_as_of_date, 'yyyy')
                   AND leave_id != NVL (p_current_leave_id, -1);

          RETURN l_used_leave;
       EXCEPTION
          WHEN NO_DATA_FOUND
          THEN
             RETURN 0;
       END calc_used_leave;

       FUNCTION calc_approved_leave (p_employee_id    NUMBER,
                                     p_leave_type     VARCHAR2,
                                     p_as_of_date     DATE DEFAULT SYSDATE)
          RETURN NUMBER
       IS
          l_approved_leave   NUMBER;
       BEGIN
          SELECT   NVL (SUM (DECODE (HALF_DAY_FLAG, 'Y', 0.5, total_days)), 0)
            INTO   l_approved_leave
            FROM   LEAVES
           WHERE       EMPLOYEE_ID = P_EMPLOYEE_ID
                   AND STATUS = 'APPROVED'
                   AND LEAVE_TYPE = p_leave_type
                   AND TRUNC (leave_start_date, 'YYYY') =
                         TRUNC (p_as_of_date, 'yyyy');

          RETURN l_approved_leave;
       EXCEPTION
          WHEN NO_DATA_FOUND
          THEN
             RETURN 0;
       END calc_approved_leave;

       FUNCTION split (p_list CHAR, p_del VARCHAR2 := ',')
          RETURN split_tbl
          PIPELINED
       IS
          l_idx     PLS_INTEGER;
          l_list    VARCHAR2 (32767) := p_list;
          l_value   VARCHAR2 (32767);
       BEGIN
          LOOP
             l_idx := INSTR (l_list, p_del);

             IF l_idx > 0
             THEN
                PIPE ROW (TRIM (SUBSTR (l_list, 1, l_idx - 1)));
                l_list := SUBSTR (l_list, l_idx + LENGTH (p_del));
             ELSE
                PIPE ROW (TRIM (l_list));
                EXIT;
             END IF;
          END LOOP;

          RETURN;
       END split;

       PROCEDURE send_leaver_notification
       IS
          l_body                VARCHAR2 (32000);
          l_to                  VARCHAR2 (32000);
          temp                  VARCHAR2 (32000);

          CURSOR leavers
          IS
             SELECT   employee_id,
                      first_name || ' ' || last_name employee_name,
                      end_date,
                      last_pay_date,
                      leaving_status
               FROM   employees
              WHERE   end_date <= SYSDATE AND leaver_notification_sent IS NULL;

          l_attachment_cursor   sys_refcursor;
       BEGIN
          FOR r IN leavers
          LOOP
             l_body :=
                'This email has been generated automatically to notify you that the named person below is leaving Attenda Limited.

    This IS confidential information, DO NOT discuss OR divulge this information UNTIL formal notification has been given BY the Line Manager.

            1) You must complete the tasks AS listed IN the attached spreadsheet
            2) Print OFF the complete worksheet (specific TO your area)
            3) SIGN it
            4) RETURN TO HR

    This IS a ISO27001 AUDIT requirement AND must be completed immediately you receive this notification

    FULL DETAILS

    Employee Name: '

                || r.employee_name
                || CHR (10)
                || 'Last Employment Date (date leaving the building): '
                || r.end_date
                || CHR (10)
                || 'Last Pay Date (end of notice period: '
                || r.last_pay_date
                || CHR (10)
                || 'Leaving Status: '
                || r.leaving_status
                || CHR (10)
                || CHR (10)
                || 'Many Thanks
    Attenda Human Resources'
    ;

             SELECT   VALUE
               INTO   l_to
               FROM   lookups
              WHERE   TYPE = 'New Starter/Leaver Notification';

             SELECT   VALUE
               INTO   temp
               FROM   lookups
              WHERE   TYPE = 'Finance Notification';

             l_to := l_to || ',' || temp;

             OPEN l_attachment_cursor FOR
                SELECT   doc, r.employee_name || '.xls', mime_type
                  FROM   documents
                 WHERE   doc_type = 'Leaver Spread Sheet';

             Att_Hr_Pkg.send_email (
                p_from          => 'attendahumanresources@attenda.net',
                p_to            => l_to,
                p_subj          => 'Leaver Notification ' || r.employee_name,
                p_body_text     => l_body,
                p_attachments   => l_attachment_cursor
             );



             UPDATE   EMPLOYEES
                SET   LEAVER_NOTIFICATION_SENT = 'Y', employment_type = 'X'
              WHERE   EMPLOYEE_ID = r.employee_id;
          END LOOP;

          APEX_MAIL.PUSH_QUEUE;
       END send_leaver_notification;


       PROCEDURE year_end_process (p_year_end_date DATE)
       IS
          CURSOR c1
          IS
                 SELECT   employee_id
                   FROM   employees
                  WHERE   Att_Hr_Pkg.calc_entitled_leave (employee_id,
                                                          'VACATION',
                                                          p_year_end_date)
                          - Att_Hr_Pkg.calc_approved_leave (employee_id,
                                                            'VACATION',
                                                            p_year_end_date) > 5
                          AND employment_type != 'X'
             FOR UPDATE   ;

          CURSOR c2
          IS
                 SELECT   employee_id,
                          Att_Hr_Pkg.calc_entitled_leave (employee_id,
                                                          'VACATION',
                                                          p_year_end_date)
                          - Att_Hr_Pkg.calc_approved_leave (employee_id,
                                                            'VACATION',
                                                            p_year_end_date)
                             days
                   FROM   employees
                  WHERE   Att_Hr_Pkg.calc_entitled_leave (employee_id,
                                                          'VACATION',
                                                          p_year_end_date)
                          - Att_Hr_Pkg.calc_approved_leave (employee_id,
                                                            'VACATION',
                                                            p_year_end_date) BETWEEN 0
                                                                                 AND  5
                          AND employment_type != 'X'
             FOR UPDATE   ;

          CURSOR c3
          IS
                 SELECT   employee_id
                   FROM   employees
                  WHERE   Att_Hr_Pkg.calc_entitled_leave (employee_id,
                                                          'VACATION',
                                                          p_year_end_date)
                          - Att_Hr_Pkg.calc_approved_leave (employee_id,
                                                            'VACATION',
                                                            p_year_end_date) <= 0
                          AND employment_type != 'X'
             FOR UPDATE   ;

          TYPE num_type IS TABLE OF NUMBER;

          emp_id_tab   num_type;
          days_tab     num_type;
          empty_tab    num_type;
       BEGIN
          --disabling sql server synchronisation as this will hamper performance for mass update.
          EXECUTE IMMEDIATE 'ALTER TRIGGER employees_sql_synch_trg DISABLE';

          UPDATE   leaves
             SET   STATUS =
                      DECODE (STATUS,
                              'SUBMITTED', 'APPROVED',
                              'CANCELLATION', 'CANCELLED',
                              'PENDING HR', 'APRPOVED',
                              STATUS)
           WHERE   TO_CHAR (leave_start_date, 'YYYY') =
                      TO_CHAR (p_year_end_date, 'YYYY')
                   AND STATUS IN ('SUBMITTED', 'CANCELLATION', 'PENDING HR');

          --balance leave greater than or equal to 5
          OPEN c1;

          --between 0 and 5
          OPEN c2;

          --rest
          OPEN c3;

          FETCH c1
          BULK COLLECT INTO   emp_id_tab;

          FORALL i IN emp_id_tab.FIRST .. emp_id_tab.LAST
             UPDATE   employees
                SET   carried_forward_holidays = 5
              WHERE   employee_id = emp_id_tab (i);

          CLOSE c1;

          --balance >0

          emp_id_tab := empty_tab;

          --  OPEN c2;
          FETCH c2
          BULK COLLECT INTO   emp_id_tab, days_tab;

          FORALL i IN emp_id_tab.FIRST .. emp_id_tab.LAST
             UPDATE   employees
                SET   carried_forward_holidays = days_tab (i)
              WHERE   employee_id = emp_id_tab (i);

          CLOSE c2;

          --for all others update cf holidays to 0
          emp_id_tab := empty_tab;

          --  OPEN c3;
          FETCH c3
          BULK COLLECT INTO   emp_id_tab;

          FORALL i IN emp_id_tab.FIRST .. emp_id_tab.LAST
             UPDATE   employees
                SET   carried_forward_holidays = 0
              WHERE   employee_id = emp_id_tab (i);

          CLOSE c3;

          UPDATE   employees
             SET   holiday_entmt =
                      CASE
                         WHEN (p_year_end_date - start_date) / 365 >= 8
                         THEN
                            DECODE (company,
                                    'Germany', 30,
                                    DECODE (shift_worker, 'N', 30, 21))
                         WHEN (p_year_end_date - start_date) / 365 >= 7
                         THEN
                            DECODE (company,
                                    'Germany', 30,
                                    DECODE (shift_worker, 'N', 29, 20.5))
                         WHEN (p_year_end_date - start_date) / 365 >= 6
                         THEN
                            DECODE (company,
                                    'Germany', 30,
                                    DECODE (shift_worker, 'N', 28, 20))
                         WHEN (p_year_end_date - start_date) / 365 >= 5
                         THEN
                            DECODE (company,
                                    'Germany', 30,
                                    DECODE (shift_worker, 'N', 27, 19.5))
                         ELSE
                            DECODE (company,
                                    'Germany', 30,
                                    DECODE (shift_worker, 'N', 25, 18))
                      END
           WHERE   employment_type != 'X' AND NVL (on_call_rota_flag, 'N') = 'N';

          --on call rota gets 3 days more
          UPDATE   employees
             SET   holiday_entmt =
                      CASE
                         WHEN (p_year_end_date - start_date) / 365 >= 8
                         THEN
                            DECODE (company,
                                    'Germany', 30,
                                    DECODE (shift_worker, 'N', 33, 24))
                         WHEN (p_year_end_date - start_date) / 365 >= 7
                         THEN
                            DECODE (company,
                                    'Germany', 30,
                                    DECODE (shift_worker, 'N', 32, 23.5))
                         WHEN (p_year_end_date - start_date) / 365 >= 6
                         THEN
                            DECODE (company,
                                    'Germany', 30,
                                    DECODE (shift_worker, 'N', 31, 23))
                         WHEN (p_year_end_date - start_date) / 365 >= 5
                         THEN
                            DECODE (company,
                                    'Germany', 30,
                                    DECODE (shift_worker, 'N', 30, 22.5))
                         ELSE
                            DECODE (company,
                                    'Germany', 30,
                                    DECODE (shift_worker, 'N', 28, 21))
                      END
           WHERE   employment_type != 'X' AND NVL (on_call_rota_flag, 'N') = 'Y';

          EXECUTE IMMEDIATE 'ALTER TRIGGER employees_sql_synch_trg enable';
       END year_end_process;

       FUNCTION dump_csv (p_query       IN VARCHAR2,
                          p_separator   IN VARCHAR2 DEFAULT ',',
                          p_dir         IN VARCHAR2,
                          p_filename    IN VARCHAR2,
                          p_header      IN VARCHAR2,
                          p_endline     IN VARCHAR2 DEFAULT NULL)
          -- This routine makes certain assumptions.
          -- 1) There must be a query and it can't be greater then 32K.
          -- 2) The separator must only be one character in length and can't be
          --    a CR, LF, binary 0, or null (easy to change).
          -- 3) If the p_dir parameter is null, the p_filename must contain the
          --    path and filename (/tmp/output.txt)
          -- 4) If the p_header parameter is not null, then insert it into the first
          --    row of the output file. If the p_separator parameter is not a comma,
          --    the comma's in the header string will be replaced with the new
          --    separator. so to add a header use 'NAME,FIRST_NAME,LAST_NAME' and if
          --    the separator is a tab, what is put into the file would be
          --    'NAME<tab>FIRST_NAME<tab>LAST_NAME'
          -- 5) The value of p_endline will be appended to the end of each line of the
          --    output file. It can be used to add a carriage return before the
          --    Line Feed is inserted by the NEW_LINE (unix). If the server is running
          --    on a windows machine, set this to null since the NEW_LINE will save
          --    a CR,LF pair anyway. This can also be used if you needed to put
          --    something at the end. For exanple "'|'||CHR(13)" which would put a
          --    vertical bar and CR,LF on each line on a unix machine.
          --
          -- The following are the returned error codes
          -- -1 The query is empty
          -- -2 The output filename is empty
          -- -3 The separator is invalid.
          -- -4 The filename only contains the path, no filename specified.
          -- -5 The output file can not be opened.
          -- -6 The query could not be parsed. It was illegal.
          --  0 The query returned NO records.
          -- >0 The number of records returned.

          RETURN NUMBER
       IS
          l_output        UTL_FILE.file_type;
          l_theCursor     INTEGER DEFAULT DBMS_SQL.open_cursor;
          l_columnValue   VARCHAR2 (2000);
          l_status        INTEGER;
          l_colCnt        NUMBER DEFAULT 0;
          l_separator     VARCHAR2 (10) DEFAULT '';
          l_cnt           NUMBER DEFAULT 0;
          l_dir           VARCHAR2 (500);
          l_filename      VARCHAR2 (32);
          x_pnt           NUMBER (4);
          l_header        VARCHAR2 (2000);
       BEGIN
          -- sanity check the input
          IF p_query IS NULL
          THEN
             RETURN (-1);
          END IF;

          IF p_filename IS NULL
          THEN
             RETURN (-2);
          END IF;

          -- Do not allow CR, LF,binary 0, or null to be used as a separator.
          -- The length of the separator must be 1 if it exists.
          IF    p_separator IS NULL
             OR p_separator IN (CHR (13), CHR (10), CHR (0))
             OR LENGTH (p_separator) > 1
          THEN
             RETURN (-3);
          END IF;

          -- If the directory parameter is blank, assume that the directory
          -- is included in the filename.
          IF p_dir IS NOT NULL
          THEN
             l_dir := p_dir;
             l_filename := p_filename;
          ELSE
             x_pnt :=
                INSTR (p_filename,
                       '/',
                       -1,
                       1);

             -- If no path is specified or no filename is specified,
             -- the procedure will not work... get out.
             IF x_pnt = 0 OR x_pnt = LENGTH (p_filename)
             THEN
                RETURN (-4);
             END IF;

             l_dir := SUBSTR (p_filename, 1, x_pnt - 1);
             l_filename := SUBSTR (p_filename, x_pnt + 1);
          END IF;


          -- Check to see if the file can be opened. If ANY error is
          -- encountered, exit with a count of -1;
          BEGIN
             l_output :=
                UTL_FILE.fopen (l_dir,
                                l_filename,
                                'w',
                                32767);
          EXCEPTION
             WHEN OTHERS
             THEN
                RETURN (-5);
          END;

          -- Check to see if the query can be processed. if ANY error is
          -- encountered, close the output file and exit.
          BEGIN
             DBMS_SQL.parse (l_theCursor, p_query, DBMS_SQL.native);
          EXCEPTION
             WHEN OTHERS
             THEN
                UTL_FILE.fclose (l_output);
                RETURN (-6);
          END;

          -- If the p_header parameter is not null, then insert the line as
          -- the first line in the output file. This is used if the user wants
          -- to insert column headings. Make sure to use a comma in your header
          -- line and the routine will replace all comma;s with the specified
          -- separator.
          l_header := NULL;

          IF p_header IS NOT NULL
          THEN
             l_header := p_header;

             IF p_separator <> ','
             THEN
                l_header := REPLACE (l_header, ',', p_separator);
             END IF;
          END IF;

          -- Loop through all the parameters for the select. To support
          -- unknown querys, the assumption is that the query will return
          -- all columns as varchar2 columns where the data is correctly
          -- formatted for inport. A maximum of 255 columns are supported
          -- in the query. Each column can't be greater then 2000
          -- characters in length.



          FOR i IN 1 .. 255
          LOOP
             BEGIN
                DBMS_SQL.define_column (l_theCursor,
                                        i,
                                        l_columnValue,
                                        2000);
                l_colCnt := i;
             EXCEPTION
                WHEN OTHERS
                THEN
                   IF (SQLCODE = -1007)
                   THEN
                      EXIT;
                   ELSE
                      RAISE;
                   END IF;
             END;
          END LOOP;

          -- This define_column insures that at least one column is defined for the
          -- routine.
          DBMS_SQL.define_column (l_theCursor,
                                  1,
                                  l_columnValue,
                                  2000);

          -- Fire the query.

          l_status := DBMS_SQL.EXECUTE (l_theCursor);

          -- Loop through all the rows returned by the query. Build up the output file
          -- by looping through the defined columns.

          LOOP
             EXIT WHEN (DBMS_SQL.fetch_rows (l_theCursor) <= 0);

             IF l_cnt = 0 AND l_header IS NOT NULL
             THEN
                UTL_FILE.put (l_output, l_header);
                UTL_FILE.put (l_output, p_endline);
                UTL_FILE.NEW_LINE (l_output, 1);
                l_cnt := 1;
             END IF;

             l_separator := '';

             FOR i IN 1 .. l_colCnt
             LOOP
                DBMS_SQL.COLUMN_VALUE (l_theCursor, i, l_columnValue);
                UTL_FILE.put (l_output, l_separator || l_columnValue);
                l_separator := p_separator;
             END LOOP;

             UTL_FILE.put (l_output, p_endline);
             UTL_FILE.NEW_LINE (l_output, 1);
             l_cnt := l_cnt + 1;
          END LOOP;

          -- Processing done. close the cursor and output file.

          DBMS_SQL.close_cursor (l_theCursor);
          UTL_FILE.fclose (l_output);

          -- Return the number of rows built in the csv file.

          RETURN l_cnt;
       -- If Any error occures outside of the errors checked above, then raise
       -- and error and blow out the procedure.
       EXCEPTION
          WHEN OTHERS
          THEN
             RAISE;
       END dump_csv;

       FUNCTION from_file (dir IN VARCHAR2, FILE IN VARCHAR2)
          RETURN BLOB
       IS
          ret   BLOB;
       BEGIN
          DBMS_LOB.createTemporary (ret, TRUE);
          from_file (dir, FILE, ret);
          RETURN ret;
       END from_file;

       PROCEDURE from_file (dir IN VARCHAR2, FILE IN VARCHAR2, b IN OUT BLOB)
       IS
          input_file      UTL_FILE.file_type;
          chunk_size      CONSTANT PLS_INTEGER := 4096;
          buf             RAW (4096);               -- Must be equal to chunk_size
          read_sofar      PLS_INTEGER := 0; --(avoid PLS-00491: numeric literal required)
          bytes_to_read   PLS_INTEGER;
       BEGIN
          input_file := UTL_FILE.fopen (dir, FILE, 'R');

          BEGIN
             LOOP
                UTL_FILE.get_raw (input_file, buf, chunk_size);
                bytes_to_read := LENGTH (buf) / 2;    -- strange and unexplanable!
                DBMS_LOB.WRITE (b,
                                bytes_to_read,
                                read_sofar + 1,
                                buf);
                read_sofar := read_sofar + bytes_to_read;
             -- utl_file raises no_data_found when unable to read
             END LOOP;
          EXCEPTION
             WHEN NO_DATA_FOUND
             THEN
                NULL;
          END;

          UTL_FILE.fclose (input_file);
       END from_file;

       PROCEDURE emp_details_rpt
       IS
          send_to   VARCHAR2 (500);

          cnt       NUMBER;

          c         sys_refcursor;
       BEGIN
          cnt :=
             Att_Hr_Pkg.dump_csv (
                'select first_name, last_name, address1, address2, address3, address4, email, email_home, tel_home, personal_mobile, intranet_mobile
    FROM employees WHERE employment_type != '
    'X''',
                CHR (9),
                'ATT_HR_DIR',
                'emp_contact_dump.xls',
                'First Name, Last Name, Address1, Address2, Address3, Address4, E-mail, Email_Home, Telephone_Home, Personal_Mobile, Intranet_Mobile'
             );

          cnt :=
             Att_Hr_Pkg.dump_csv (
                'select first_name, last_name, person_to_contact, emergency_address1, emergency_address2, emergency_address3, emergency_address4, emergency_postal_code,
    emergency_day_tel, emergency_evening_tel, emergency_mobile FROM employees WHERE employment_type != '
    'X''',
                CHR (9),
                'ATT_HR_DIR',
                'emp_emergency_dump.xls',
                'First Name, Last Name, Persn_to_Contact, Address1, Address2, Address3, Address4, Postal_Code, Telephone_Day, Telephone_Evening, Mobile'
             );

          SELECT   VALUE
            INTO   send_to
            FROM   lookups
           WHERE   TYPE = 'HR_monthly_reports_dl';

          OPEN c FOR
             SELECT   Att_Hr_Pkg.from_file ('ATT_HR_DIR', 'emp_contact_dump.xls'),
                      'emp_contact_dump.xls',
                      'application/vnd.ms-excel'
               FROM   DUAL
             UNION ALL
             SELECT   Att_Hr_Pkg.from_file ('ATT_HR_DIR',
                                            'emp_emergency_dump.xls'),
                      'emp_emergency_dump.xls',
                      'application/vnd.ms-excel'
               FROM   DUAL;

          Att_Hr_Pkg.send_email (p_from          => 'attendahumanresources@attenda.net',
                                 p_to            => send_to,
                                 p_subj          => 'Monthly HR Reports',
                                 p_body_text     => 'Employee Details Report',
                                 p_attachments   => c);
       END emp_details_rpt;

       FUNCTION get_salary (p_employee_id IN NUMBER, p_as_of_date IN DATE)
          RETURN NUMBER
       IS
          v_sal   NUMBER;
       BEGIN
          SELECT   LAST_VALUE (
                      NVL (eh.base_salary, 0) + NVL (eh.shift_allowance, 0)
                   )
                      OVER (ORDER BY effective_start_date, effective_end_date)
            INTO   v_sal
            FROM   employment_history eh
           WHERE   eh.employee_id = p_employee_id
                   AND p_as_of_date BETWEEN effective_start_date
                                        AND  NVL (effective_end_date, SYSDATE);

          RETURN v_sal;
       END get_salary;
    END Att_Hr_Pkg;
    /

    CREATE SEQUENCE "DEPARTMENT_ID_S"
       MINVALUE 1
       MAXVALUE 1.00000000000000E+27
       INCREMENT BY 1
       START WITH 78
       CACHE 20
       NOORDER
       NOCYCLE
    /

    CREATE SEQUENCE "DOCUMENT_ID_S"
       MINVALUE 1
       MAXVALUE 1.00000000000000E+27
       INCREMENT BY 1
       START WITH 21
       CACHE 20
       NOORDER
       NOCYCLE
    /

    CREATE SEQUENCE "EMPLOYEE_ID_S"
       MINVALUE 1
       MAXVALUE 1.00000000000000E+27
       INCREMENT BY 1
       START WITH 2909
       CACHE 20
       NOORDER
       NOCYCLE
    /

    CREATE SEQUENCE "HOLIDAY_ID_S"
       MINVALUE 1
       MAXVALUE 1.00000000000000E+27
       INCREMENT BY 1
       START WITH 61
       CACHE 20
       NOORDER
       NOCYCLE
    /

    CREATE SEQUENCE "LEAVE_ID_S"
       MINVALUE 1
       MAXVALUE 1.00000000000000E+27
       INCREMENT BY 1
       START WITH 9748
       CACHE 20
       NOORDER
       NOCYCLE
    /

    CREATE SEQUENCE "LOOKUP_ID_S"
       MINVALUE 1
       MAXVALUE 1.00000000000000E+27
       INCREMENT BY 1
       START WITH 225
       CACHE 20
       NOORDER
       NOCYCLE
    /

    CREATE SEQUENCE "NOMINEE_ID_S"
       MINVALUE 1
       MAXVALUE 1.00000000000000E+27
       INCREMENT BY 1
       START WITH 821
       CACHE 20
       NOORDER
       NOCYCLE
    /

    CREATE OR REPLACE TRIGGER "DEPARTMENTS_SQL_SYNCH_TRG"
       AFTER INSERT OR UPDATE
       ON departments
       FOR EACH ROW
    DECLARE
       PRAGMA AUTONOMOUS_TRANSACTION;
       l_parent_id_sql   NUMBER := NULL;
       l_id_sql          NUMBER := NULL;
       l_enabled_flag    NUMBER := 0;
       l_desc            VARCHAR2 (32000);
    BEGIN
       SELECT   a."HDT_DeptID"
         INTO   l_parent_id_sql
         FROM   hl_departments a, departments b
        WHERE   "HDT_DeptName" = b.name AND b.department_id = :NEW.parent_id;

       SELECT   DECODE (:NEW.enabled, 'Y', 1, 0) INTO l_enabled_flag FROM DUAL;

       SELECT   NVL (SUBSTR (TO_CHAR (:NEW.description), 1, 2000), ' ')
         INTO   l_desc
         FROM   DUAL;

       /*  SELECT a."HDT_DeptID"
         INTO l_id_sql
         FROM hl_departments a
         WHERE "HDT_DeptName" = :OLD.name;*/


       IF INSERTING
       THEN
          INSERT INTO hl_departments ("HDT_DeptName",
                                      "HDT_ParentID",
                                      "HDT_DeptDescription",
                                      "HDT_DeptShow")
            VALUES   (:NEW.name,
                      l_parent_id_sql,
                      l_desc,
                      l_enabled_flag);
       END IF;

       IF UPDATING
       THEN
          UPDATE   hl_departments
             SET   "HDT_DeptName" = :NEW.name,
                   "HDT_ParentID" = l_parent_id_sql,
                   "HDT_DeptDescription" = l_desc,
                   "HDT_DeptShow" = l_enabled_flag
           WHERE   "HDT_DeptName" = :OLD.name;
       END IF;

       COMMIT;
    END;
    /

    ALTER TRIGGER  "DEPARTMENTS_SQL_SYNCH_TRG" ENABLE
    /

    CREATE OR REPLACE TRIGGER "EMPLOYEES_SQL_SYNCH_TRG"
       AFTER UPDATE OR INSERT
       ON employees
       FOR EACH ROW
    DECLARE
       PRAGMA AUTONOMOUS_TRANSACTION;

       missing_emp_rec EXCEPTION;
       l_emp_rec        hl_header%ROWTYPE;

       CURSOR c
       IS
          SELECT   :NEW.employee_id,
                   -- :NEW.employee_number,
                   CASE LENGTH (TO_CHAR (:NEW.employee_number))
                      WHEN 1 THEN LPAD (TO_CHAR (:NEW.employee_number), 3, '0')
                      WHEN 2 THEN LPAD (TO_CHAR (:NEW.employee_number), 3, '0')
                      ELSE TO_CHAR (:NEW.employee_number)
                   END
                      CASE,
                      NVL (:NEW.preferred_name, :NEW.first_name)
                   || ' '
                   || :NEW.last_name,
                   :NEW.start_date,
                   dep.name,
                   mgr.email,
                   :NEW.company,
                   NULL HHD_Entitled_Days,
                   NULL HHD_Days_Taken,
                   DECODE (:NEW.employment_type, 'X', 0, 1) HHD_Employed_Flag,
                   NULL HHD_Notes,
                   (SELECT   "HDT_DeptID"
                      FROM   hl_departments
                     WHERE   "HDT_DeptName" = dep.name)
                      "organization_id",
                   DECODE (:NEW.company,
                           'Corporate', 1,
                           'UK', 2,
                           'France', 3,
                           'Germany', 4,
                           'India', 5,
                           NULL),
                   :NEW.job_title,
                   NULL,
                   NULL,
                   NVL (:NEW.extension, ' '),
                   1,
                   :NEW.nt_login,
                   NVL (:NEW.intranet_mobile, ' '),
                   :NEW.email,
                   DECODE (:NEW.obj_tool_right,
                           'U', 0,
                           'M', 1,
                           'E', 2,
                           'A', 3,
                           NULL),
                   DECODE (:NEW.glossary_update_flag, 'Y', 1, 0),
                   NULL,
                   NULL,
                   DECODE (:NEW.shift_worker, 'Y', 1, 0),
                   NULL,
                   NULL,
                   NULL,
                   NULL,
                   NULL
            FROM   employees mgr, departments dep
           WHERE       1 = 1
                   AND:NEW.line_manager_id = mgr.employee_id
                   AND:NEW.organization_id = dep.department_id;

       l_sql_rec_flag   VARCHAR2 (1);
    BEGIN
       OPEN c;

       FETCH c INTO l_emp_rec;

       --  RAISE missing_emp_rec WHEN c%NOTFOUND;

       BEGIN
          SELECT   'Y'
            INTO   l_sql_rec_flag
            FROM   hl_header hh
           WHERE   hh."HHD_LogginID" = :OLD.nt_login;

          UPDATE   hl_header hh
             SET   "HHD_LogginID" = l_emp_rec."HHD_LogginID",
                   "HHD_Employee_Name" = l_emp_rec."HHD_Employee_Name",
                   "HHD_Employee_No" = l_emp_rec."HHD_Employee_No",
                   "HHD_Department" = l_emp_rec."HHD_Department",
                   "HHD_Manager" = l_emp_rec."HHD_Manager",
                   "HHD_Employed_Flag" = l_emp_rec."HHD_Employed_Flag",
                   "HHD_DeptID" = l_emp_rec."HHD_DeptID",
                   "HHD_CompanyUnitID" = l_emp_rec."HHD_CompanyUnitID",
                   "HHD_JobTitle" = l_emp_rec."HHD_JobTitle",
                   "HHD_ExtNo" = l_emp_rec."HHD_ExtNo",
                   "HHD_Photo" = l_emp_rec."HHD_Photo",
                   "HHD_Mobile_Phone" = l_emp_rec."HHD_Mobile_Phone",
                   "HHD_Email_Address" = l_emp_rec."HHD_Email_Address",
                   "ORL_ID" = l_emp_rec."ORL_ID",
                   "HHD_Glossary_Admin" = l_emp_rec."HHD_Glossary_Admin",
                   "HHD_Shift_Flag" = l_emp_rec."HHD_Shift_Flag"
           WHERE   hh."HHD_LogginID" = :OLD.nt_login;
       EXCEPTION
          WHEN NO_DATA_FOUND
          THEN
             --if record is not found then enter a new record in sql server.
             IF :NEW.person_type = 'E'
             THEN
                INSERT INTO hl_header ("HHD_Employee_No",
                                       "HHD_Employee_Name",
                                       "HHD_Start_Date",
                                       "HHD_Department",
                                       "HHD_Manager",
                                       "HHD_Company_Unit",
                                       "HHD_Entitled_Days",
                                       "HHD_Days_Taken",
                                       "HHD_Employed_Flag",
                                       "HHD_Notes",
                                       "HHD_DeptID",
                                       "HHD_CompanyUnitID",
                                       "HHD_JobTitle",
                                       "HHD_AboutMyself",
                                       "HHD_AreasOfResponsibility",
                                       "HHD_ExtNo",
                                       "HHD_Photo",
                                       "HHD_LogginID",
                                       "HHD_Mobile_Phone",
                                       "HHD_Email_Address",
                                       "ORL_ID",
                                       "HHD_Glossary_Admin",
                                       "HHD_Entitled_Days_NYR",
                                       "HHD_Days_Taken_NYR",
                                       "HHD_Shift_Flag",
                                       "HHD_Employee_Qualifications",
                                       "HHD_STEntitled_Days",
                                       "HHD_STDays_Taken",
                                       "HHD_STDays_Taken_NYR",
                                       "OraPassword")
                  VALUES   (l_emp_rec."HHD_Employee_No",
                            l_emp_rec."HHD_Employee_Name",
                            l_emp_rec."HHD_Start_Date",
                            l_emp_rec."HHD_Department",
                            l_emp_rec."HHD_Manager",
                            l_emp_rec."HHD_Company_Unit",
                            l_emp_rec."HHD_Entitled_Days",
                            l_emp_rec."HHD_Days_Taken",
                            l_emp_rec."HHD_Employed_Flag",
                            l_emp_rec."HHD_Notes",
                            l_emp_rec."HHD_DeptID",
                            l_emp_rec."HHD_CompanyUnitID",
                            l_emp_rec."HHD_JobTitle",
                            l_emp_rec."HHD_AboutMyself",
                            l_emp_rec."HHD_AreasOfResponsibility",
                            l_emp_rec."HHD_ExtNo",
                            l_emp_rec."HHD_Photo",
                            l_emp_rec."HHD_LogginID",
                            l_emp_rec."HHD_Mobile_Phone",
                            l_emp_rec."HHD_Email_Address",
                            l_emp_rec."ORL_ID",
                            l_emp_rec."HHD_Glossary_Admin",
                            l_emp_rec."HHD_Entitled_Days_NYR",
                            l_emp_rec."HHD_Days_Taken_NYR",
                            l_emp_rec."HHD_Shift_Flag",
                            l_emp_rec."HHD_Employee_Qualifications",
                            l_emp_rec."HHD_STEntitled_Days",
                            l_emp_rec."HHD_STDays_Taken",
                            l_emp_rec."HHD_STDays_Taken_NYR",
                            l_emp_rec."OraPassword");
             END IF;
       END;

       COMMIT;
    END;
    /

    ALTER TRIGGER  "EMPLOYEES_SQL_SYNCH_TRG" ENABLE
    /

    CREATE OR REPLACE TRIGGER "EMPLOYEMENT_HISTORY_TRG"
       AFTER INSERT OR UPDATE
       ON employees
       FOR EACH ROW
    DECLARE
       l_history_record_count   NUMBER := 0;
    BEGIN
       --IF NEW STARTER THEN UPDATE EMPLOYMENT HISTORY TABLE AND INSERT NEW RECORD IN EMPLOYEES TABLE
       IF :NEW.PERSON_TYPE = 'N'
       THEN
          IF :NEW.start_date != :OLD.start_date
          THEN
             UPDATE   EMPLOYMENT_HISTORY
                SET   EFFECTIVE_END_DATE =
                         NVL (TO_DATE (:NEW.START_DATE - 1, 'dd/mm/yyyy'),
                              TRUNC (SYSDATE - 1))
              WHERE   employee_id = :NEW.employee_id
                      AND EFFECTIVE_END_DATE IS NULL;

             INSERT INTO EMPLOYMENT_HISTORY (employee_id,
                                             effective_start_date,
                                             effective_end_date,
                                             line_manager_id,
                                             organization_id,
                                             job_title,
                                             pay_currency,
                                             base_salary,
                                             car_allowance,
                                             shift_allowance,
                                             ote,
                                             SHIFT_WORKER,
                                             employment_type,
                                             variable_pay)
               VALUES   (:NEW.EMPLOYEE_ID,
                         :NEW.start_date,
                         NULL,
                         :NEW.LINE_MANAGER_ID,
                         :NEW.ORGANIZATION_ID,
                         :NEW.JOB_TITLE,
                         :NEW.PAY_CURRENCY,
                         :NEW.BASE_SALARY,
                         :NEW.CAR_ALLOWANCE,
                         :NEW.SHIFT_ALLOWANCE,
                         :NEW.OTE,
                         :NEW.SHIFT_WORKER,
                         NVL2 (:NEW.end_date, 'X', :NEW.EMPLOYMENT_TYPE),
                         :NEW.VARIABLE_PAY);
          END IF;

          RETURN;
       END IF;

       IF :OLD.start_date IS NULL OR :NEW.start_date != :OLD.start_date
       THEN
          --deleting history records if start date is changed. otherwise new record
          -- will conflict with old records

          DELETE FROM   EMPLOYMENT_HISTORY
                WHERE   employee_id = :NEW.employee_id;
       END IF;

       --check if any of the fields on which history is maintained is changed
       IF     NVL (:NEW.END_DATE, SYSDATE) = NVL (:OLD.END_DATE, SYSDATE)
          AND:NEW.start_date = :OLD.start_date
          AND:NEW.line_manager_id = :OLD.line_manager_id
          AND:NEW.organization_id = :OLD.organization_id
          AND:NEW.job_title = :OLD.job_title
          AND:NEW.pay_currency = :OLD.pay_currency
          AND NVL (:NEW.base_salary, -1) = NVL (:OLD.base_salary, -1)
          AND NVL (:NEW.car_allowance, -1) = NVL (:OLD.car_allowance, -1)
          AND NVL (:NEW.shift_allowance, -1) = NVL (:OLD.shift_allowance, -1)
          AND NVL (:NEW.ote, -1) = NVL (:OLD.ote, -1)
          AND:NEW.SHIFT_WORKER = :OLD.SHIFT_WORKER
          AND:NEW.employment_type = :OLD.employment_type
          AND NVL (:NEW.variable_pay, -1) = NVL (:OLD.variable_pay, -1)
       THEN
          RETURN;
       ELSE
          --update today's record if exists
          UPDATE   EMPLOYMENT_HISTORY
             SET   effective_end_date = :NEW.END_DATE,
                   line_manager_id = :NEW.line_manager_id,
                   organization_id = :NEW.organization_id,
                   job_title = :NEW.job_title,
                   pay_currency = :NEW.pay_currency,
                   base_salary = :NEW.base_salary,
                   car_allowance = :NEW.car_allowance,
                   shift_allowance = :NEW.shift_allowance,
                   ote = :NEW.ote,
                   SHIFT_WORKER = :NEW.SHIFT_WORKER,
                   employment_type = :NEW.employment_type,
                   variable_pay = :NEW.variable_pay
           WHERE   employee_id = :NEW.employee_id
                   AND effective_start_date = TRUNC (SYSDATE);

          --if no row exists for today then insert a history record and end date ealrier history record
          IF SQL%ROWCOUNT = 0
          THEN
             --end date last record
             UPDATE   EMPLOYMENT_HISTORY
                SET   EFFECTIVE_END_DATE =
                         NVL (TO_DATE (:NEW.END_DATE, 'dd/mm/yyyy'),
                              TRUNC (SYSDATE - 1))
              WHERE   employee_id = :NEW.employee_id
                      AND EFFECTIVE_END_DATE IS NULL;

             l_history_record_count := SQL%ROWCOUNT;

             --INSERT A NEW RECORD IN THE HISTORY TABLE
             INSERT INTO EMPLOYMENT_HISTORY (
                                                employee_id,
                                                effective_start_date,
                                                effective_end_date,
                                                line_manager_id,
                                                organization_id,
                                                job_title,
                                                pay_currency,
                                                base_salary,
                                                car_allowance,
                                                shift_allowance,
                                                ote,
                                                SHIFT_WORKER,
                                                employment_type,
                                                VARIABLE_PAY
                        )
               VALUES   (
                            :NEW.EMPLOYEE_ID,
                            DECODE (
                               l_history_record_count,
                               0,
                               :NEW.start_date,
                               NVL (TO_DATE (:NEW.END_DATE, 'dd/mm/yyyy') + 1,
                                    TRUNC (SYSDATE))
                            ),
                            NULL,
                            :NEW.LINE_MANAGER_ID,
                            :NEW.ORGANIZATION_ID,
                            :NEW.JOB_TITLE,
                            :NEW.PAY_CURRENCY,
                            :NEW.BASE_SALARY,
                            :NEW.CAR_ALLOWANCE,
                            :NEW.SHIFT_ALLOWANCE,
                            :NEW.OTE,
                            :NEW.SHIFT_WORKER,
                            NVL2 (:NEW.end_date, 'X', :NEW.EMPLOYMENT_TYPE),
                            :NEW.VARIABLE_PAY
                        );
          END IF;                                                --sql%rowcount =0
       END IF;                                             --history field changed
    END;
    /

    ALTER TRIGGER  "EMPLOYEMENT_HISTORY_TRG" ENABLE
    /

    CREATE OR REPLACE TRIGGER "LEAVES_SQL_SYNCH_TRG"
       AFTER INSERT OR UPDATE
       ON leaves
       FOR EACH ROW
    DECLARE
       PRAGMA AUTONOMOUS_TRANSACTION;

       l_sql_employee_no   hl_header."HHD_Employee_No"%TYPE;

       l_half_day_flag     VARCHAR2 (1) := '0';
       l_leave_type        VARCHAR2 (15);
    BEGIN
       l_leave_type := SUBSTR (:NEW.leave_type, 1, 15);

       SELECT   "HHD_Employee_No"
         INTO   l_sql_employee_no
         FROM   hl_header h, employees e
        WHERE   UPPER (h."HHD_LogginID") = UPPER (e.nt_login)
                AND e.employee_id = :NEW.employee_id;

       SELECT   DECODE (:NEW.half_day_flag, 'Y', '1', '0')
         INTO   l_half_day_flag
         FROM   DUAL;


       UPDATE   hl_detail
          SET   "HDT_Start_Date" = :NEW.leave_start_date,
                "HDT_End_Date" = :NEW.leave_end_date,
                "HDT_Total_Days" = :NEW.total_days,
                "HDT_Half_Day" = l_half_day_flag,
                "HDT_Notes" = :NEW.STATUS,
                "HDT_Booking_Type" = l_leave_type
        WHERE   "Oracle_LeaveID" = :OLD.leave_id;

       IF SQL%ROWCOUNT = 0
       THEN
          INSERT INTO hl_detail ("HDT_Employee_No",
                                 "HDT_Start_Date",
                                 "HDT_End_Date",
                                 "HDT_Total_Days",
                                 "HDT_Half_Day",
                                 "HDT_Notes",
                                 "HDT_Booking_Type",
                                 "Oracle_LeaveID")
            VALUES   (l_sql_employee_no,
                      :NEW.leave_start_date,
                      :NEW.leave_end_date,
                      :NEW.total_days,
                      l_half_day_flag,
                      :NEW.STATUS,
                      l_leave_type,
                      :NEW.leave_id);
       END IF;


       /*IF INSERTING
       THEN
                 INSERT INTO hl_detail
               ("HDT_Employee_No",
               "HDT_Start_Date",
               "HDT_End_Date",
               "HDT_Total_Days",
               "HDT_Half_Day",
               "HDT_Notes",
               "HDT_Booking_Type",
               "Oracle_LeaveID")
               VALUES
               (l_sql_employee_no,
                :NEW.leave_start_date,
                :NEW.leave_end_date,
                :NEW.total_days,
                l_half_day_flag,
                :NEW.status,
                :NEW.leave_type,
                :NEW.leave_id
               );
       ELSIF UPDATING
       THEN
         UPDATE hl_detail
         SET
               "HDT_Start_Date" = :NEW.leave_start_date,
               "HDT_End_Date" = :NEW.leave_end_date,
               "HDT_Total_Days" = :NEW.total_days,
               "HDT_Half_Day" = l_half_day_flag,
               "HDT_Notes" = :NEW.status,
               "HDT_Booking_Type" =  :NEW.leave_type
         WHERE "Oracle_LeaveID" = :OLD.leave_id;

       END IF; --inserting*/


       COMMIT;
    --dbms_lock.sleep(2);
    END;
    /

    ALTER TRIGGER  "LEAVES_SQL_SYNCH_TRG" ENABLE
    /

    CREATE OR REPLACE FORCE VIEW "HL_DEPARTMENTS" ("HDT_DeptID",
                                                   "HDT_DeptName",
                                                   "HDT_ParentID",
                                                   "HDT_DeptDescription",
                                                   "HDT_DeptShow")
    AS
       SELECT   "HDT_DeptID",
                "HDT_DeptName",
                "HDT_ParentID",
                "HDT_DeptDescription",
                "HDT_DeptShow"
         FROM   hl_departments@hsodbc
    /

    CREATE OR REPLACE FORCE VIEW "HL_DETAIL" ("HDT_Holiday_ID",
                                              "HDT_Employee_No",
                                              "HDT_Start_Date",
                                              "HDT_End_Date",
                                              "HDT_Total_Days",
                                              "HDT_Half_Day",
                                              "HDT_Year",
                                              "HDT_Start_Date_Ptr",
                                              "HDT_End_Date_Ptr",
                                              "HDT_Authorised_By",
                                              "HDT_Authorise_DATE",
                                              "HDT_Notes",
                                              "HDT_Booking_Type",
                                              "Oracle_LeaveID")
    AS
       SELECT   "HDT_Holiday_ID",
                "HDT_Employee_No",
                "HDT_Start_Date",
                "HDT_End_Date",
                "HDT_Total_Days",
                "HDT_Half_Day",
                "HDT_Year",
                "HDT_Start_Date_Ptr",
                "HDT_End_Date_Ptr",
                "HDT_Authorised_By",
                "HDT_Authorise_DATE",
                "HDT_Notes",
                "HDT_Booking_Type",
                "Oracle_LeaveID"
         FROM   hl_detail@hsodbc
    /

    CREATE OR REPLACE FORCE VIEW "HL_HEADER" ("HHD_Record_ID",
                                              "HHD_Employee_No",
                                              "HHD_Employee_Name",
                                              "HHD_Start_Date",
                                              "HHD_Department",
                                              "HHD_Manager",
                                              "HHD_Company_Unit",
                                              "HHD_Entitled_Days",
                                              "HHD_Days_Taken",
                                              "HHD_Employed_Flag",
                                              "HHD_Notes",
                                              "HHD_DeptID",
                                              "HHD_CompanyUnitID",
                                              "HHD_JobTitle",
                                              "HHD_AboutMyself",
                                              "HHD_AreasOfResponsibility",
                                              "HHD_ExtNo",
                                              "HHD_Photo",
                                              "HHD_LogginID",
                                              "HHD_Mobile_Phone",
                                              "HHD_Email_Address",
                                              "ORL_ID",
                                              "HHD_Glossary_Admin",
                                              "HHD_Entitled_Days_NYR",
                                              "HHD_Days_Taken_NYR",
                                              "HHD_Shift_Flag",
                                              "HHD_Employee_Qualifications",
                                              "HHD_STEntitled_Days",
                                              "HHD_STDays_Taken",
                                              "HHD_STDays_Taken_NYR",
                                              "OraPassword")
    AS
       SELECT   "HHD_Record_ID",
                "HHD_Employee_No",
                "HHD_Employee_Name",
                "HHD_Start_Date",
                "HHD_Department",
                "HHD_Manager",
                "HHD_Company_Unit",
                "HHD_Entitled_Days",
                "HHD_Days_Taken",
                "HHD_Employed_Flag",
                "HHD_Notes",
                "HHD_DeptID",
                "HHD_CompanyUnitID",
                "HHD_JobTitle",
                "HHD_AboutMyself",
                "HHD_AreasOfResponsibility",
                "HHD_ExtNo",
                "HHD_Photo",
                "HHD_LogginID",
                "HHD_Mobile_Phone",
                "HHD_Email_Address",
                "ORL_ID",
                "HHD_Glossary_Admin",
                "HHD_Entitled_Days_NYR",
                "HHD_Days_Taken_NYR",
                "HHD_Shift_Flag",
                "HHD_Employee_Qualifications",
                "HHD_STEntitled_Days",
                "HHD_STDays_Taken",
                "HHD_STDays_Taken_NYR",
                "OraPassword"
         FROM   hl_header@HSODBC
    /

    CREATE DATABASE LINK "HSODBC.US.ORACLE.COM"
       CONNECT TO "SA" IDENTIFIED BY "Att3nd4a0"
       USING 'hsodbc'
    /
     
  2. poing

    poing Guest

    Hi,

    The function "apex_generate_hash", "apex_validate_hash" and "apex_authorise" are responsible for generating and validating the password. All these routines are in Oracle package "ATT_HR_PKG".

    The password is is a hash of the username and the current timestamp (system date-time), minus a offset in seconds. The offset is supplied as a second argument to the function "apex_generate_hash". If you look at the ASP.NET code, the call to the oracle function (the line you have pointed out) "functorapasswd.CommandText = "Att_hr_pkg.apex_generate_hash"" will tell you what the value of the offset.

    So, if the username is "foo" then the password is generated (assuming the current time is "13th Sept 2010 9:25 PM" and offset is 30 seconds) by hashing a string "FOO:20100913 212430ATTENDA". In this string the timestamp used is "9:24:30 PM", 30 seconds subtracted from actual system time.

    Apparently the ASP.NET application validates the user, and instead of storing the password, it establishes a authorization with the database by generating a hash based password (something like a shared secret between application and database) using supplied username and current timestamp. If the application generated hash is validated by database the authorization is OK'd. Since the hash is dependent on date-time - the database generates the hash for last 10 seconds - just to factor any timing differences between app server and database.

    This would not work if the app server and database server are not synchronized in time (within a tolerance of 10 seconds). I think that is where your problem is.
     
    Sadik likes this.
  3. shreya92

    shreya92 Active Member

    Messages:
    26
    Likes Received:
    0
    Trophy Points:
    80
    You can check out rolta, which provides oracle applications by satisfying the consumers.
    They provide services in a faster period by accessing the data to get the useful data for more usage.