/*****************************************************************************
*
* Filename         - $Workfile:   valid_address_stamp.sql  $
*
*                     Name              Date      Version     CR Number
* Author           - See revision history below.
* Last Update      - See revision history below.
*
* Project Name     - TAGTRAK(Oracle Database)
*
* Source Code
* Identifier       - 0/3/300/8010/112
*
* Design Spec
* Section          - 
*
* Contents         - Live Schema Stored Procedure
*
* Language         - Oracle SQL*Plus script
*
* Classification   - Commercial In Confidence
*
*          (c) Ultra Electronics Limited, 2003
*
*****************************************************************************/
/****************************************************************************
* @(#)$Workfile:   valid_address_stamp.sql  $  $Revision:   1.0  $
*
* Revision History
*
* Note that user tagtrak is many users. For these users the user
* name is entered as the last line in the change description.
*
* $Log:   /shared/pvcs/tagtarch/db_ora/db/source/live/procedures/valid_address_stamp.sqv  $
* 
****************************************************************************/
PROMPT CREATE OR REPLACE FUNCTION valid_address_stamp

CREATE OR REPLACE
FUNCTION valid_address_stamp
 (p_subunit_name IN system_cfg.subunit_name%TYPE)
 RETURN system_cfg.subunit_name%TYPE IS
 v_subunit_name system_cfg.subunit_name%TYPE DEFAULT 'SYM';
 BEGIN
   SELECT 'SYM'
   INTO v_subunit_name
   FROM dual
   WHERE NOT EXISTS
   (SELECT 1 FROM system_cfg WHERE subunit_name = p_subunit_name
   AND process_name = 'DCS')
   AND EXISTS (SELECT 1 FROM system_cfg WHERE bpm_status = 'Y');

   RETURN v_subunit_name;
 EXCEPTION
   WHEN NO_DATA_FOUND THEN
     RETURN p_subunit_name;
 END valid_address_stamp;
/
