set verify off
set serveroutput on
DECLARE

  l_test_load  BOOLEAN := upper('&&2') = 'TEST';
  l_fat_load   BOOLEAN := upper('&&2') = 'FAT';
  l_uat_load   BOOLEAN := upper('&&2') = 'UAT';
  l_live_load  BOOLEAN := upper('&&2') = 'LIVE';
  l_train_load BOOLEAN := upper('&&2') = 'TRAIN';
  l_dev_load   BOOLEAN := upper(nvl('&&2' ,'DEV')) = 'DEV';
  l_sgid       NUMBER;
  l_flow_id    NUMBER;
  l_schema     VARCHAR2(80) DEFAULT upper('&&1');
  l_substitution varchar2(80);
BEGIN

  IF l_test_load THEN
    l_flow_id := 102;
  ELSIF l_fat_load THEN
    l_flow_id := 200;
  ELSIF l_dev_load THEN
    l_flow_id := 155;
  ELSIF l_uat_load THEN
    l_flow_id := 400;
  ELSIF l_train_load THEN
    l_flow_id := 800;
  END IF;

  dbms_output.put_line('Flow:'||l_flow_id);

  IF l_flow_id IS NOT NULL THEN
  
      SELECT wwv_flow.get_current_flow_sgid(l_flow_id)
        INTO l_sgid
        FROM dual;
    
      wwv_flow_api.set_security_group_id(l_sgid);
      apex_application.g_flow_id := l_flow_id;

    dbms_output.put_line('Security Group:'||l_sgid);
    
    l_substitution := '('||upper('&&2')||'-'||to_char(sysdate,'DD-MON-YYYY-HH24MI')||')';

    UPDATE flows_030100.wwv_flows w
       SET w.substitution_value_01 = l_substitution
     WHERE security_group_id = l_sgid and id = l_flow_id;

    dbms_output.put_line('SetSubstitution:'||l_substitution);
  
  ELSE
    dbms_output.put_line('Run SetSubstitution: Unexpected parameter value of &&2 received');
  END IF;

END;
/
commit
/