Projects:EnhancedMulti-organizationSupport/Upgrade Script

Contents 1 Enhanced Multi-organization Support - Upgrade Script 1.1 Accounting 1.1.1 1.1 1.1.2 1.2 1.1.3 1.4 1.1.4 1.7 1.1.5 1.9 1.2 Period control 1.2.1 2.1 1.2.2 2.2 1.2.3 2.4 2 Upgrade Script - Oracle version 3 Upgrade Script - PostgreSQL version

Enhanced Multi-organization Support - Upgrade Script

Accounting

1.1

Define three types of organizations:

• Organization. Default organization, which can't have transactions allowed.
• Legal. A legal entity. It has transactions allowed.
• Generic. It has transactions allowed.

Already defined into the sourcedata

1.2

The * organization will be an Organization. Every immediate child will be a mark as Legal, and the rest of organizations will be Generic.

-- * is an Organization
UPDATE AD_ORG
SET AD_OrgType_ID='0'
WHERE AD_Org_ID='0'

-- children are Legal
UPDATE AD_ORG
SET AD_OrgType_ID='1'
WHERE AD_IsOrgIncluded(AD_ORG.AD_ORG_ID, '0', AD_ORG.AD_CLIENT_ID)=2

-- other are Generic 
UPDATE AD_ORG
SET AD_OrgType_ID='2'
WHERE AD_IsOrgIncluded(AD_ORG.AD_ORG_ID, '0', AD_ORG.AD_CLIENT_ID)>=3

1.4

The General Accounting reports will have the column IsOrgIncluded='Y'. The way we will use to detect if a report is a General Accounting report will be to check if an element value of the report's node has the accounttype='A'.

/* 1.4: General Accounting report (When a node's element value has an accounttype=A): IsOrgIncluded='Y' */
UPDATE c_acct_rpt
set IsOrgBalanced='Y'
where exists (select 1
              from c_acct_rpt p, c_acct_rpt_group g, c_acct_rpt_node n, c_elementvalue e
              where p.c_acct_rpt_id = c_acct_rpt.c_acct_rpt_id
              and p.c_acct_rpt_id=g.c_acct_rpt_id
              and g.c_acct_rpt_group_id=n.c_acct_rpt_group_id
              and n.c_elementvalue_id= e.c_elementvalue_id
              and e.accounttype='A'
              )

1.7

We must transfer the client's schema to the legal entities defined into the 1.2 chapter.

/* 1.7: Transfer the accounting schema to the Legal Organization */
CURSOR cur_schema IS
  SELECT AD_Client_ID, C_AcctSchema1_ID, C_AcctSchema2_ID, C_AcctSchema3_ID 
  FROM AD_ClientInfo

CURSOR cur_le IS
  SELECT AD_Org_ID
  FROM AD_Org
  WHERE AD_OrgType_ID='1'
  AND AD_Client_ID=cur_schema.AD_Client_ID
FOR v_cur_schema IN cur_schema LOOP
  FOR v_cur_le IN cur_le LOOP
    IF (v_cur_schema.C_AcctSchema1_ID IS NOT NULL) THEN    
      INSERT INTO AD_ORG_ACCTSCHEMA (AD_Org_AcctSchema_ID, AD_Client_ID, AD_Org_ID, 
      IsActive, Created, CreatedBy, Updated, UpdatedBy, C_AcctSchema_ID)
      VALUES (get_uuid(), v_cur_schema.ad_client_id, v_cur_le.ad_org_id,
      'Y', to_date(now()), '0', to_date(now()), '0', 
      v_cur_schema.c_acctschema1_id);
    END IF;
   
    IF (v_cur_schema.C_AcctSchema2_ID IS NOT NULL) THEN    
      INSERT INTO AD_ORG_ACCTSCHEMA (AD_Org_AcctSchema_ID, AD_Client_ID, AD_Org_ID, 
      IsActive, Created, CreatedBy, Updated, UpdatedBy, C_AcctSchema_ID)
      VALUES (get_uuid(), v_cur_schema.ad_client_id, v_cur_le.ad_org_id,
      'Y', to_date(now()), '0', to_date(now()), '0', 
      v_cur_schema.c_acctschema2_id);
    END IF;
   
    IF (v_cur_schema.C_AcctSchema3_ID IS NOT NULL) THEN    
      INSERT INTO AD_ORG_ACCTSCHEMA (AD_Org_AcctSchema_ID, AD_Client_ID, AD_Org_ID, 
      IsActive, Created, CreatedBy, Updated, UpdatedBy, C_AcctSchema_ID)
      VALUES (get_uuid(), v_cur_schema.ad_client_id, v_cur_le.ad_org_id,
      'Y', to_date(now()), '0', to_date(now()), '0', 
      v_cur_schema.c_acctschema3_id);
    END IF;

  END LOOP;
END LOOP;

1.9

We must transfer the client's calendar to the legal entities defined into the 1.2 chapter and set every organization as ready.

FOR cur_calendar IN
(
 SELECT AD_Client_ID, C_Calendar_ID 
 FROM AD_ClientInfo 
 WHERE C_Calendar_ID IS NOT NULL
)
LOOP
 UPDATE AD_Org
 SET c_calendar_id=cur_calendar.c_calendar_id
 WHERE ad_orgtype_id='1' 
 AND ad_client_id=cur_calendar.ad_client_id;
END LOOP;

UPDATE AD_ORG
SET IsReady='Y';

Period control

2.1

The defined Legal Organization will be the one which can open or close period.

/* 2.1: The Legal Organization can open/close period */ 
UPDATE AD_Org
SET isperiodcontrolallowed='Y'
WHERE ad_orgtype_id='1';

2.2

The rows inside the C_PeriodControl table must be updated to be related with the previously defined legal entities.

/* 2.2: Update the c_periodcontrol rows to be owned by the legal entities */
FOR cur_client_le IN
(
 SELECT AD_Org_ID, AD_Client_ID
 FROM AD_Org
 WHERE AD_OrgType_ID='1'
 ORDER BY AD_Client_ID
)
LOOP
 FOR cur_periodcontrol IN 
 (
  SELECT * 
  FROM C_PERIODCONTROL 
  WHERE AD_Client_ID=cur_client_le.AD_Client_ID
  AND AD_Org_ID='0'
 )
 LOOP
  INSERT INTO C_PERIODCONTROL (C_PERIODCONTROL_ID, AD_CLIENT_ID, AD_ORG_ID, 
  ISACTIVE, CREATED, CREATEDBY, UPDATED, UPDATEDBY, C_PERIOD_ID, DOCBASETYPE, 
  PERIODSTATUS, PERIODACTION, PROCESSING) 
  VALUES (get_uuid(), cur_periodcontrol.AD_CLIENT_ID, cur_client_le.AD_ORG_ID, 
  cur_periodcontrol.ISACTIVE, cur_periodcontrol.CREATED,  cur_periodcontrol.CREATEDBY, 
  cur_periodcontrol.UPDATED, cur_periodcontrol.UPDATEDBY, cur_periodcontrol.C_PERIOD_ID, 
  cur_periodcontrol.DOCBASETYPE, cur_periodcontrol.PERIODSTATUS, cur_periodcontrol.PERIODACTION, 
  cur_periodcontrol.PROCESSING);
 END LOOP;
END LOOP;

DELETE FROM C_PERIODCONTROL WHERE AD_ORG_ID='0';

2.4

For every organization that is period control allowed, copy the Period info to the new AD_Org_Closing table

FOR cur_orgperiodcontrol IN
(
 SELECT AD_ORG.AD_Org_ID, AD_ORG.AD_Client_ID, AD_ORG_ACCTSCHEMA_ID
 FROM AD_Org, AD_ORG_ACCTSCHEMA
 WHERE IsPeriodControlAllowed='Y'
 AND ad_org.ad_org_id=AD_ORG_ACCTSCHEMA.AD_Org_ID
 ORDER BY AD_Client_ID
)
LOOP
 FOR cur_period IN
 (
  SELECT DISTINCT(c_year_id), open_fact_acct_group_id, close_fact_acct_group_id, reg_fact_acct_group_id, divideup_fact_acct_group_id 
  FROM C_PERIOD 
  WHERE AD_Client_ID=cur_orgperiodcontrol.AD_Client_ID
  AND (open_fact_acct_group_id IS NOT NULL 
      OR close_fact_acct_group_id IS NOT NULL 
      OR reg_fact_acct_group_id IS NOT NULL 
      OR divideup_fact_acct_group_id IS NOT NULL)
 )
 LOOP
   INSERT INTO AD_Org_Closing (AD_Org_Closing_ID, AD_CLIENT_ID, AD_ORG_ID, 
   ISACTIVE, CREATED, CREATEDBY, UPDATED, UPDATEDBY, C_Year_ID, AD_ORG_ACCTSCHEMA_ID, 
   REG_FACT_ACCT_GROUP_ID, CLOSE_FACT_ACCT_GROUP_ID, DIVIDEUP_FACT_ACCT_GROUP_ID, OPEN_FACT_ACCT_GROUP_ID)
   VALUES (get_uuid(), cur_period.AD_Client_ID, cur_orgperiodcontrol.AD_ORG_ID,
   cur_period.ISACTIVE, cur_period.CREATED, cur_period.CREATEDBY, 
   cur_period.UPDATED, cur_period.UPDATEDBY, cur_period.C_Year_ID, cur_orgperiodcontrol.AD_ORG_ACCTSCHEMA_ID,
   cur_period.REG_FACT_ACCT_GROUP_ID, cur_period.CLOSE_FACT_ACCT_GROUP_ID, cur_period.DIVIDEUP_FACT_ACCT_GROUP_ID, cur_period.OPEN_FACT_ACCT_GROUP_ID);
 END LOOP;
END LOOP;

Upgrade Script - Oracle version

CREATE OR REPLACE PROCEDURE MULTIORG_UPDATE 
AS
BEGIN
BEGIN

 /* 1.2 */
EXECUTE IMMEDIATE 'ALTER TRIGGER AD_ORG_STLE_TRG DISABLE';
 -- * is an Organization
UPDATE AD_ORG
SET AD_OrgType_ID='0'
WHERE AD_Org_ID='0';

-- children are Legal
UPDATE AD_ORG
SET AD_OrgType_ID='1'
WHERE AD_IsOrgIncluded(AD_ORG.AD_ORG_ID, '0', AD_ORG.AD_CLIENT_ID)=2;

-- other are Generic 
UPDATE AD_ORG
SET AD_OrgType_ID='2'
WHERE AD_IsOrgIncluded(AD_ORG.AD_ORG_ID, '0', AD_ORG.AD_CLIENT_ID)>=3;

EXECUTE IMMEDIATE 'ALTER TRIGGER AD_ORG_STLE_TRG ENABLE';


/* 1.4: General Accounting report (When a node's element value has an accounttype=A): IsOrgIncluded='Y' */
UPDATE c_acct_rpt
set IsOrgBalanced='Y'
where exists (select 1
              from c_acct_rpt p, c_acct_rpt_group g, c_acct_rpt_node n, c_elementvalue e
              where p.c_acct_rpt_id = c_acct_rpt.c_acct_rpt_id
	      and p.c_acct_rpt_id=g.c_acct_rpt_id
              and g.c_acct_rpt_group_id=n.c_acct_rpt_group_id
              and n.c_elementvalue_id= e.c_elementvalue_id
              and e.accounttype='A'
              );


/* 1.7: Transfer the accounting schema to the Legal Organization */
FOR cur_schema IN
(
 SELECT AD_Client_ID, C_AcctSchema1_ID, C_AcctSchema2_ID, C_AcctSchema3_ID 
 FROM AD_ClientInfo
)
LOOP
  FOR cur_le IN
  (
  SELECT AD_Org_ID
  FROM AD_Org
  WHERE AD_OrgType_ID='1'
  AND AD_Client_ID=cur_schema.AD_Client_ID
  )
  LOOP
    IF (cur_schema.C_AcctSchema1_ID IS NOT NULL) THEN    
      INSERT INTO AD_ORG_ACCTSCHEMA (AD_Org_AcctSchema_ID, AD_Client_ID, AD_Org_ID, 
      IsActive, Created, CreatedBy, Updated, UpdatedBy, C_AcctSchema_ID)
      VALUES (get_uuid(), cur_schema.ad_client_id, cur_le.ad_org_id,
      'Y', to_date(now()), '0', to_date(now()), '0', 
      cur_schema.c_acctschema1_id);
    END IF;
   
    IF (cur_schema.C_AcctSchema2_ID IS NOT NULL) THEN    
      INSERT INTO AD_ORG_ACCTSCHEMA (AD_Org_AcctSchema_ID, AD_Client_ID, AD_Org_ID, 
      IsActive, Created, CreatedBy, Updated, UpdatedBy, C_AcctSchema_ID)
      VALUES (get_uuid(), cur_schema.ad_client_id, cur_le.ad_org_id,
      'Y', to_date(now()), '0', to_date(now()), '0', 
      cur_schema.c_acctschema2_id);
    END IF;
   
    IF (cur_schema.C_AcctSchema3_ID IS NOT NULL) THEN    
      INSERT INTO AD_ORG_ACCTSCHEMA (AD_Org_AcctSchema_ID, AD_Client_ID, AD_Org_ID, 
      IsActive, Created, CreatedBy, Updated, UpdatedBy, C_AcctSchema_ID)
      VALUES (get_uuid(), cur_schema.ad_client_id, cur_le.ad_org_id,
      'Y', to_date(now()), '0', to_date(now()), '0', 
      cur_schema.c_acctschema3_id);
    END IF;
  END LOOP;
END LOOP;



/* 1.9: Transfer the calendar to the Legal Organization */
EXECUTE IMMEDIATE 'ALTER TRIGGER AD_ORG_TRG DISABLE';
EXECUTE IMMEDIATE 'ALTER TRIGGER AD_ORG_STLE_TRG DISABLE';
FOR cur_calendar IN
(
 SELECT AD_Client_ID, C_Calendar_ID 
 FROM AD_ClientInfo 
 WHERE C_Calendar_ID IS NOT NULL
)
LOOP
  UPDATE AD_Org
  SET c_calendar_id=cur_calendar.c_calendar_id
  WHERE ad_orgtype_id='1' 
  AND ad_client_id=cur_calendar.ad_client_id;
END LOOP;


-- set organizations as ready
UPDATE AD_ORG
SET IsReady='Y';
EXECUTE IMMEDIATE 'ALTER TRIGGER AD_ORG_STLE_TRG ENABLE';
EXECUTE IMMEDIATE 'ALTER TRIGGER AD_ORG_TRG ENABLE';

/* 2.1: The Legal Organization can open/close period */ 
UPDATE AD_Org
SET isperiodcontrolallowed='Y'
WHERE ad_orgtype_id='1';


/* 2.2: Update the c_periodcontrol rows to be owned by the legal entities */
FOR cur_client_le IN
(
 SELECT AD_Org_ID, AD_Client_ID
 FROM AD_Org
 WHERE AD_OrgType_ID='1'
 ORDER BY AD_Client_ID
)
LOOP
 FOR cur_periodcontrol IN 
 (
  SELECT * 
  FROM C_PERIODCONTROL 
  WHERE AD_Client_ID=cur_client_le.AD_Client_ID
  AND AD_Org_ID='0'
 )
 LOOP
  INSERT INTO C_PERIODCONTROL (C_PERIODCONTROL_ID, AD_CLIENT_ID, AD_ORG_ID, 
  ISACTIVE, CREATED, CREATEDBY, UPDATED, UPDATEDBY, C_PERIOD_ID, DOCBASETYPE, 
  PERIODSTATUS, PERIODACTION, PROCESSING) 
  VALUES (get_uuid(), cur_periodcontrol.AD_CLIENT_ID, cur_client_le.AD_ORG_ID, 
  cur_periodcontrol.ISACTIVE, cur_periodcontrol.CREATED, cur_periodcontrol.CREATEDBY, 
  cur_periodcontrol.UPDATED, cur_periodcontrol.UPDATEDBY, cur_periodcontrol.C_PERIOD_ID, 
  cur_periodcontrol.DOCBASETYPE, cur_periodcontrol.PERIODSTATUS, cur_periodcontrol.PERIODACTION, 
  cur_periodcontrol.PROCESSING);
 END LOOP;
END LOOP;
 
DELETE FROM C_PERIODCONTROL
WHERE AD_ORG_ID='0';


/* 2.4: For every organization that isperiodcontrolallowed, copy the Period info to the new AD_Org_Closing table. One year and one organization */
FOR cur_orgperiodcontrol IN
(
 SELECT AD_ORG.AD_Org_ID, AD_ORG.AD_Client_ID, AD_ORG_ACCTSCHEMA_ID
 FROM AD_Org, AD_ORG_ACCTSCHEMA
 WHERE IsPeriodControlAllowed='Y'
 AND ad_org.ad_org_id=AD_ORG_ACCTSCHEMA.AD_Org_ID
 ORDER BY AD_Client_ID
)
LOOP
 FOR cur_period IN
 (
  SELECT DISTINCT(c_year_id), open_fact_acct_group_id, close_fact_acct_group_id, reg_fact_acct_group_id, divideup_fact_acct_group_id 
  FROM C_PERIOD 
  WHERE AD_Client_ID=cur_orgperiodcontrol.AD_Client_ID
  AND (open_fact_acct_group_id IS NOT NULL 
      OR close_fact_acct_group_id IS NOT NULL 
      OR reg_fact_acct_group_id IS NOT NULL 
      OR divideup_fact_acct_group_id IS NOT NULL)
 )
 LOOP
   INSERT INTO AD_Org_Closing (AD_Org_Closing_ID, AD_CLIENT_ID, AD_ORG_ID, 
   ISACTIVE, CREATED, CREATEDBY, UPDATED, UPDATEDBY, C_Year_ID, AD_ORG_ACCTSCHEMA_ID, 
   REG_FACT_ACCT_GROUP_ID, CLOSE_FACT_ACCT_GROUP_ID, DIVIDEUP_FACT_ACCT_GROUP_ID, OPEN_FACT_ACCT_GROUP_ID)
   VALUES (get_uuid(), cur_orgperiodcontrol.AD_Client_ID, cur_orgperiodcontrol.AD_ORG_ID,
   'Y', to_date(now()), '0', 
   to_date(now()), '0', cur_period.C_Year_ID, cur_orgperiodcontrol.AD_ORG_ACCTSCHEMA_ID,
   cur_period.REG_FACT_ACCT_GROUP_ID, cur_period.CLOSE_FACT_ACCT_GROUP_ID, cur_period.DIVIDEUP_FACT_ACCT_GROUP_ID, cur_period.OPEN_FACT_ACCT_GROUP_ID);
 END LOOP;
END LOOP;


COMMIT;


EXCEPTION
WHEN OTHERS THEN
  ROLLBACK;
  DBMS_OUTPUT.put_line('. ERROR - ' || SQLERRM);
END;
END MULTIORG_UPDATE; 

Upgrade Script - PostgreSQL version

 
CREATE OR REPLACE FUNCTION MULTIORG_UPDATE()
RETURNS void
AS
$BODY$ DECLARE 
 v_ResultStr VARCHAR(2000):='';
 cur_schema RECORD;
 cur_le	RECORD;
 cur_calendar RECORD;
 cur_client_le RECORD;
 cur_periodcontrol RECORD;
 cur_orgperiodcontrol RECORD;
 cur_period RECORD;
 
BEGIN
 
 /* 1.2 */
 -- * is an Organization
ALTER TABLE AD_ORG DISABLE TRIGGER AD_ORG_STLE_TRG;
UPDATE AD_ORG
SET AD_OrgType_ID='0'
WHERE AD_Org_ID='0';
 
-- children are Legal
UPDATE AD_ORG
SET AD_OrgType_ID='1'
WHERE AD_IsOrgIncluded(AD_ORG.AD_ORG_ID, '0', AD_ORG.AD_CLIENT_ID)=2;
 
-- other are Generic 
UPDATE AD_ORG
SET AD_OrgType_ID='2'
WHERE AD_IsOrgIncluded(AD_ORG.AD_ORG_ID, '0', AD_ORG.AD_CLIENT_ID)>=3;
 
ALTER TABLE AD_ORG ENABLE TRIGGER AD_ORG_STLE_TRG;
 
/* 1.4: General Accounting report (When a node's element value has an accounttype=A): IsOrgIncluded='Y' */
UPDATE c_acct_rpt
SET IsOrgBalanced='Y'
WHERE EXISTS (SELECT 1
              FROM c_acct_rpt p, c_acct_rpt_group g, c_acct_rpt_node n, c_elementvalue e
              WHERE p.c_acct_rpt_id = c_acct_rpt.c_acct_rpt_id
	      AND p.c_acct_rpt_id=g.c_acct_rpt_id
              AND g.c_acct_rpt_group_id=n.c_acct_rpt_group_id
              AND n.c_elementvalue_id= e.c_elementvalue_id
              AND e.accounttype='A'
              );
 
 
/* 1.7: Transfer the accounting schema to the Legal Organization */
FOR cur_schema IN
(
 SELECT AD_Client_ID, C_AcctSchema1_ID, C_AcctSchema2_ID, C_AcctSchema3_ID 
 FROM AD_ClientInfo
)
LOOP
  FOR cur_le IN
  (
  SELECT AD_Org_ID
  FROM AD_Org
  WHERE AD_OrgType_ID='1'
  AND AD_Client_ID=cur_schema.AD_Client_ID
  )
  LOOP
    IF (cur_schema.C_AcctSchema1_ID IS NOT NULL) THEN    
      INSERT INTO AD_ORG_ACCTSCHEMA (AD_Org_AcctSchema_ID, AD_Client_ID, AD_Org_ID, 
      IsActive, Created, CreatedBy, Updated, UpdatedBy, C_AcctSchema_ID)
      VALUES (get_uuid(), cur_schema.ad_client_id, cur_le.ad_org_id,
      'Y', to_date(now()), '0', to_date(now()), '0', 
      cur_schema.c_acctschema1_id);
    END IF;
   
    IF (cur_schema.C_AcctSchema2_ID IS NOT NULL) THEN    
      INSERT INTO AD_ORG_ACCTSCHEMA (AD_Org_AcctSchema_ID, AD_Client_ID, AD_Org_ID, 
      IsActive, Created, CreatedBy, Updated, UpdatedBy, C_AcctSchema_ID)
      VALUES (get_uuid(), cur_schema.ad_client_id, cur_le.ad_org_id,
      'Y', to_date(now()), '0', to_date(now()), '0', 
      cur_schema.c_acctschema2_id);
    END IF;
   
    IF (cur_schema.C_AcctSchema3_ID IS NOT NULL) THEN    
      INSERT INTO AD_ORG_ACCTSCHEMA (AD_Org_AcctSchema_ID, AD_Client_ID, AD_Org_ID, 
      IsActive, Created, CreatedBy, Updated, UpdatedBy, C_AcctSchema_ID)
      VALUES (get_uuid(), cur_schema.ad_client_id, cur_le.ad_org_id,
      'Y', to_date(now()), '0', to_date(now()), '0', 
      cur_schema.c_acctschema3_id);
    END IF;
  END LOOP;
END LOOP;
 
 
 
/* 1.9: Transfer the calendar to the Legal Organization */
ALTER TABLE AD_ORG DISABLE TRIGGER AD_ORG_TRG;
ALTER TABLE AD_ORG DISABLE TRIGGER AD_ORG_STLE_TRG;
FOR cur_calendar IN
(
 SELECT AD_Client_ID, C_Calendar_ID 
 FROM AD_ClientInfo 
 WHERE C_Calendar_ID IS NOT NULL
)
LOOP
  UPDATE AD_Org
  SET c_calendar_id=cur_calendar.c_calendar_id
  WHERE ad_orgtype_id='1' 
  AND ad_client_id=cur_calendar.ad_client_id;
END LOOP;
 
-- set organizations as ready
UPDATE AD_ORG
SET IsReady='Y';
ALTER TABLE AD_ORG ENABLE TRIGGER AD_ORG_STLE_TRG;
ALTER TABLE AD_ORG ENABLE TRIGGER AD_ORG_TRG;
 
/* 2.1: The Legal Organization can open/close period */ 
UPDATE AD_Org
SET isperiodcontrolallowed='Y'
WHERE ad_orgtype_id='1';
 
 
/* 2.2: Update the c_periodcontrol rows to be owned by the legal entities */
FOR cur_client_le IN
(
 SELECT AD_Org_ID, AD_Client_ID
 FROM AD_Org
 WHERE AD_OrgType_ID='1'
 ORDER BY AD_Client_ID
)
LOOP
 FOR cur_periodcontrol IN 
 (
  SELECT * 
  FROM C_PERIODCONTROL 
  WHERE AD_Client_ID=cur_client_le.AD_Client_ID
  AND AD_Org_ID='0'
 )
 LOOP
  INSERT INTO C_PERIODCONTROL (C_PERIODCONTROL_ID, AD_CLIENT_ID, AD_ORG_ID, 
  ISACTIVE, CREATED, CREATEDBY, UPDATED, UPDATEDBY, C_PERIOD_ID, DOCBASETYPE, 
  PERIODSTATUS, PERIODACTION, PROCESSING) 
  VALUES (get_uuid(), cur_periodcontrol.AD_CLIENT_ID, cur_client_le.AD_ORG_ID, 
  cur_periodcontrol.ISACTIVE, cur_periodcontrol.CREATED, cur_periodcontrol.CREATEDBY, 
  cur_periodcontrol.UPDATED, cur_periodcontrol.UPDATEDBY, cur_periodcontrol.C_PERIOD_ID, 
  cur_periodcontrol.DOCBASETYPE, cur_periodcontrol.PERIODSTATUS, cur_periodcontrol.PERIODACTION, 
  cur_periodcontrol.PROCESSING);
 END LOOP;
END LOOP;
 
DELETE FROM C_PERIODCONTROL
WHERE AD_ORG_ID='0';
 
 
/* 2.4: For every organization that isperiodcontrolallowed, copy the Period info to the new AD_Org_Closing table */
FOR cur_orgperiodcontrol IN
(
 SELECT AD_ORG.AD_Org_ID, AD_ORG.AD_Client_ID, AD_ORG_ACCTSCHEMA_ID
 FROM AD_Org, AD_ORG_ACCTSCHEMA
 WHERE IsPeriodControlAllowed='Y'
 AND ad_org.ad_org_id=AD_ORG_ACCTSCHEMA.AD_Org_ID
 ORDER BY AD_Client_ID
)
LOOP
 FOR cur_period IN
 (
  SELECT DISTINCT(c_year_id), open_fact_acct_group_id, close_fact_acct_group_id, reg_fact_acct_group_id, divideup_fact_acct_group_id 
  FROM C_PERIOD 
  WHERE AD_Client_ID=cur_orgperiodcontrol.AD_Client_ID
  AND (open_fact_acct_group_id IS NOT NULL 
      OR close_fact_acct_group_id IS NOT NULL 
      OR reg_fact_acct_group_id IS NOT NULL 
      OR divideup_fact_acct_group_id IS NOT NULL)
 )
 LOOP
   INSERT INTO AD_Org_Closing (AD_Org_Closing_ID, AD_CLIENT_ID, AD_ORG_ID, 
   ISACTIVE, CREATED, CREATEDBY, UPDATED, UPDATEDBY, C_Year_ID, AD_ORG_ACCTSCHEMA_ID, 
   REG_FACT_ACCT_GROUP_ID, CLOSE_FACT_ACCT_GROUP_ID, DIVIDEUP_FACT_ACCT_GROUP_ID, OPEN_FACT_ACCT_GROUP_ID)
   VALUES (get_uuid(), cur_orgperiodcontrol.AD_Client_ID, cur_orgperiodcontrol.AD_ORG_ID,
   'Y', to_date(now()), '0', 
   to_date(now()), '0', cur_period.C_Year_ID, cur_orgperiodcontrol.AD_ORG_ACCTSCHEMA_ID,
   cur_period.REG_FACT_ACCT_GROUP_ID, cur_period.CLOSE_FACT_ACCT_GROUP_ID, cur_period.DIVIDEUP_FACT_ACCT_GROUP_ID, cur_period.OPEN_FACT_ACCT_GROUP_ID);
 END LOOP;
END LOOP;
 
 
 
 
 
 
EXCEPTION
WHEN OTHERS THEN
  RAISE NOTICE '%',v_ResultStr ;
     v_ResultStr:= '@ERROR=' || SQLERRM;
     RAISE NOTICE '%',v_ResultStr ;
 
     RAISE EXCEPTION '%', SQLERRM;
 
 
END ; $BODY$
LANGUAGE 'plpgsql' VOLATILE
COST 100;