Projects:EnhancedMulti-organizationSupport/Upgrade Script
Contents |
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;