PROMPT CREATE OR REPLACE FUNCTION yvr_get_std

CREATE OR REPLACE FUNCTION yvr_get_std (INSTR VARCHAR2) RETURN DATE IS

/*****************************************************************************
*
* Filename		- $Workfile$
*
* Project Name		- UltraTrak Database
*
* Source Code Identifier	- 0/3/300/8010/112
*
* Description		- Database function created to extract the correct date and time
*                 for std.
*
* Language		- Oracle PL*SQL
*
* File History		- For the development history run the Show History report in PVCS
*
* (c) Ultra Electronics Limited, 2001
*
*****************************************************************************/

/*****************************************************************************
*
* Database Function - yvr_get_std
*
* Description	- Determines the correct std date/time incoming to the FIDs
*               interface
*
* CR History:
* Date	Name		CR Number	CR Description
*
*****************************************************************************/

    sch_dt CHAR (7);
    sch_tm CHAR (4);
    est_dt CHAR  (7);
    est_tm CHAR  (4);
    act_dt CHAR  (7);
    act_tm CHAR  (4);

    tmpVar VARCHAR2 (256);
    retVar DATE;
BEGIN

    retVar := NULL;
    tmpVar := INSTR;

    sch_dt := SUBSTR (tmpVar, 1, 7);
    sch_tm := SUBSTR (tmpVar, 9, 4);

    est_dt := SUBSTR (tmpVar, 14, 7);
    est_tm := SUBSTR (tmpVar, 22, 4);

    act_dt := SUBSTR (tmpVar, 27, 7);
    act_tm := SUBSTR (tmpVar, 35, 4);

    BEGIN
        tmpVar := act_dt || act_tm;
        retVar := TO_DATE (tmpVar, 'DDMONRRHH24MI');

        EXCEPTION
        WHEN OTHERS THEN
            NULL;
    END;

    BEGIN
        IF retVar IS NULL THEN
            tmpVar := est_dt || est_tm;
            retVar := TO_DATE (tmpVar, 'DDMONRRHH24MI');
        END IF;

        EXCEPTION
        WHEN OTHERS THEN
            NULL;
    END;

    BEGIN
        IF retVar IS NULL THEN
            tmpVar := sch_dt || sch_tm;
            retVar := TO_DATE (tmpVar, 'DDMONRRHH24MI');
        END IF;

        EXCEPTION
        WHEN OTHERS THEN
            NULL;
    END;

    RETURN retVar;

    EXCEPTION
    WHEN OTHERS THEN
        RAISE;
END yvr_get_std;

/


