CREATE OR REPLACE FUNCTION ad_create_organizations(p_clientid character varying)
  RETURNS NUMERIC AS
$BODY$ DECLARE 
v_count NUMERIC:=1;
v_parentid AD_TREENODe.PARENT_ID%TYPE;
v_orgid AD_ORG.AD_ORG_ID%TYPE;
cur_treenode RECORD;
cur_childs RECORD;
cur_orgs RECORD;
v_seqno AD_TREENODE.SEQNO%TYPE;
v_treeID AD_TREENODE.AD_TREE_ID%TYPE;
v_name AD_ORG.NAME%TYPE;
v_value AD_ORG.VALUE%TYPE;
BEGIN
--v_parentid:='0';
FOR cur_orgs IN (select ad_org_id from ad_org where ad_client_id = p_clientid and isready='Y') 
LOOP
  SELECT value, name INTO v_value, v_name
     FROM ad_org
     WHERE ad_org_id = cur_orgs.AD_ORG_ID;
  v_count:=1;
  WHILE v_count <= 10 
  LOOP
     v_orgid:=get_uuid();
     v_seqno:=0;
     
     INSERT INTO ad_org(
            ad_org_id, ad_client_id, isactive, created, createdby, updated, 
            updatedby, value, name, description, issummary, ad_orgtype_id, 
            isperiodcontrolallowed, c_calendar_id, isready, social_name, 
            c_currency_id, c_acctschema_id)
    VALUES (v_orgid,p_clientid,'Y',now(),'100',now(),
    '100', v_value||'-'||v_count,v_name||'-'||v_count,'','N','2',
    'N',NULL,'N','',NULL,NULL);
    
    SELECT MAX(AD_TREE_ORG_ID)
    INTO v_treeID
    FROM AD_CLIENTINFO
    WHERE AD_CLIENT_ID=p_clientid;

    SELECT PARENT_ID INTO v_parentid 
        FROM AD_TREENODE 
	WHERE NODE_ID = cur_orgs.AD_ORG_ID
	AND AD_CLIENT_ID = p_clientid
	AND AD_TREE_ID = v_treeID;

    FOR cur_childs IN
    (
	SELECT * FROM AD_TREENODE 
	WHERE PARENT_ID = v_parentid
	AND AD_TREE_ID = v_treeID
    )
    LOOP
      IF(cur_childs.seqno > v_seqno) THEN
        v_seqno:=cur_childs.seqno;
      END IF;
    END LOOP;

    FOR cur_treenode IN
    (
	SELECT * FROM AD_TREENODE 
	WHERE NODE_ID = v_orgid
	AND AD_CLIENT_ID = p_clientid
	AND AD_TREE_ID = v_treeID
    )
    LOOP
	UPDATE AD_TREENODE SET PARENT_ID = v_parentid, seqno=(v_seqno + 10) WHERE AD_TREENODE_ID = cur_treenode.AD_TREENODE_ID;
	EXIT;
    END LOOP;    
    v_count:= v_count + 1;
  END LOOP;  
END LOOP;
RETURN v_Count;
END ; $BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION ad_create_organizations(character varying)
  OWNER TO tad;

SELECT * FROM ad_create_organizations('23C59575B9CF467C9620760EB255B389');

DROP FUNCTION ad_create_organizations(character varying);