SET VERIFY OFF
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');
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;

  IF l_flow_id IS NOT NULL THEN
  
    BEGIN
      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;
    
      INSERT INTO flows_030100.wwv_flow_company_schemas
        (security_group_id
        ,SCHEMA)
      VALUES
        (l_sgid
        ,l_schema);
    EXCEPTION
      WHEN dup_val_on_index THEN
        NULL;
    END;
  
    UPDATE flows_030100.wwv_flows w
       SET w.owner = l_schema
     WHERE security_group_id = l_sgid and id = l_flow_id;
  

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

END;
/