0005345: Chart of accounts export from live system - Openbravo Issue Tracking System

Index: src-db/database/model/functions/GET_ACCOUNT_OPERANDS.xml =================================================================== --- src-db/database/model/functions/GET_ACCOUNT_OPERANDS.xml (revision 0) +++ src-db/database/model/functions/GET_ACCOUNT_OPERANDS.xml (revision 8112) @@ -0,0 +1,35 @@ +<?xml version="1.0"?> + <database name="FUNCTION GET_ACCOUNT_OPERANDS"> + <function name="GET_ACCOUNT_OPERANDS" type="VARCHAR"> + <parameter name="p_elementvalue_id" type="NUMERIC" mode="in"> + <default/> + </parameter> + <body><![CDATA[v_List VARCHAR2(5000); + TYPE RECORD IS REF CURSOR; + operands RECORD; + +BEGIN + v_List := ''; + FOR operands IN + (select ev.value, eo.sign + from c_elementvalue_operand eo, c_elementvalue ev + where eo.c_elementvalue_id=p_elementvalue_id and eo.account_id=ev.c_elementvalue_id + ORDER BY eo.c_elementvalue_id + + ) LOOP + IF (operands.sign > 0) THEN + v_List := v_List || '+' || operands.value; + ELSE + v_List := v_List || '-' || operands.value; + END IF; + END LOOP; + + IF (SUBSTR(v_List,1,1) = '+') THEN + RETURN SUBSTR(v_List, 2); + ELSE + RETURN v_List; + END IF; + +END GET_ACCOUNT_OPERANDS]]></body> + </function> + </database> Index: src-db/database/model/views/C_ELEMENTVALUE_EXPORT_V.xml =================================================================== --- src-db/database/model/views/C_ELEMENTVALUE_EXPORT_V.xml (revision 0) +++ src-db/database/model/views/C_ELEMENTVALUE_EXPORT_V.xml (revision 8112) @@ -0,0 +1,56 @@ +<?xml version="1.0"?> + <database name="VIEW C_ELEMENTVALUE_EXPORT_V"> + <view name="C_ELEMENTVALUE_EXPORT_V"><![CDATA[select + cl.AD_CLIENT_ID as AD_CLIENT_ID, + ev.VALUE as Account_Value, + ev.NAME as Account_Name, + ev.DESCRIPTION as Account_Description, + ev.ACCOUNTTYPE as Account_Type, + ev.ACCOUNTSIGN as Account_Sign, + ev.ISDOCCONTROLLED as Account_Document, + (case ev.ISSUMMARY + when 'Y' then 'Yes' + when 'N' then 'No' + else 'Error' + END) as Account_Summary, + (case + when (select vc.ACCOUNT_ID from C_VALIDCOMBINATION vc, C_ACCTSCHEMA_DEFAULT sd, C_ACCTSCHEMA sc where sc.AD_CLIENT_ID = ev.AD_CLIENT_ID and sc.C_ACCTSCHEMA_ID = sd.C_ACCTSCHEMA_ID and sc.C_ACCTSCHEMA_ID = vc.C_ACCTSCHEMA_ID and sd.A_ACCUMDEPRECIATION_ACCT = vc.C_VALIDCOMBINATION_ID) = ev.C_ELEMENTVALUE_ID then 'A_ACCUMDEPRECIATION_ACCT' + when (select vc.ACCOUNT_ID from C_VALIDCOMBINATION vc, C_ACCTSCHEMA_DEFAULT sd, C_ACCTSCHEMA sc where sc.AD_CLIENT_ID = ev.AD_CLIENT_ID and sc.C_ACCTSCHEMA_ID = sd.C_ACCTSCHEMA_ID and sc.C_ACCTSCHEMA_ID = vc.C_ACCTSCHEMA_ID and sd.A_DEPRECIATION_ACCT = vc.C_VALIDCOMBINATION_ID) = ev.C_ELEMENTVALUE_ID then 'A_DEPRECIATION_ACCT' + when (select vc.ACCOUNT_ID from C_VALIDCOMBINATION vc, C_ACCTSCHEMA_DEFAULT sd, C_ACCTSCHEMA sc where sc.AD_CLIENT_ID = ev.AD_CLIENT_ID and sc.C_ACCTSCHEMA_ID = sd.C_ACCTSCHEMA_ID and sc.C_ACCTSCHEMA_ID = vc.C_ACCTSCHEMA_ID and sd.B_ASSET_ACCT = vc.C_VALIDCOMBINATION_ID) = ev.C_ELEMENTVALUE_ID then 'B_ASSET_ACCT' + when (select vc.ACCOUNT_ID from C_VALIDCOMBINATION vc, C_ACCTSCHEMA_DEFAULT sd, C_ACCTSCHEMA sc where sc.AD_CLIENT_ID = ev.AD_CLIENT_ID and sc.C_ACCTSCHEMA_ID = sd.C_ACCTSCHEMA_ID and sc.C_ACCTSCHEMA_ID = vc.C_ACCTSCHEMA_ID and sd.B_EXPENSE_ACCT = vc.C_VALIDCOMBINATION_ID) = ev.C_ELEMENTVALUE_ID then 'B_EXPENSE_ACCT' + when (select vc.ACCOUNT_ID from C_VALIDCOMBINATION vc, C_ACCTSCHEMA_DEFAULT sd, C_ACCTSCHEMA sc where sc.AD_CLIENT_ID = ev.AD_CLIENT_ID and sc.C_ACCTSCHEMA_ID = sd.C_ACCTSCHEMA_ID and sc.C_ACCTSCHEMA_ID = vc.C_ACCTSCHEMA_ID and sd.B_INTRANSIT_ACCT = vc.C_VALIDCOMBINATION_ID) = ev.C_ELEMENTVALUE_ID then 'B_INTRANSIT_ACCT' + when (select vc.ACCOUNT_ID from C_VALIDCOMBINATION vc, C_ACCTSCHEMA_DEFAULT sd, C_ACCTSCHEMA sc where sc.AD_CLIENT_ID = ev.AD_CLIENT_ID and sc.C_ACCTSCHEMA_ID = sd.C_ACCTSCHEMA_ID and sc.C_ACCTSCHEMA_ID = vc.C_ACCTSCHEMA_ID and sd.B_REVALUATIONGAIN_ACCT = vc.C_VALIDCOMBINATION_ID) = ev.C_ELEMENTVALUE_ID then 'B_REVALUATIONGAIN_ACCT' + when (select vc.ACCOUNT_ID from C_VALIDCOMBINATION vc, C_ACCTSCHEMA_DEFAULT sd, C_ACCTSCHEMA sc where sc.AD_CLIENT_ID = ev.AD_CLIENT_ID and sc.C_ACCTSCHEMA_ID = sd.C_ACCTSCHEMA_ID and sc.C_ACCTSCHEMA_ID = vc.C_ACCTSCHEMA_ID and sd.B_REVALUATIONLOSS_ACCT = vc.C_VALIDCOMBINATION_ID) = ev.C_ELEMENTVALUE_ID then 'B_REVALUATIONLOSS_ACCT' + when (select vc.ACCOUNT_ID from C_VALIDCOMBINATION vc, C_ACCTSCHEMA_DEFAULT sd, C_ACCTSCHEMA sc where sc.AD_CLIENT_ID = ev.AD_CLIENT_ID and sc.C_ACCTSCHEMA_ID = sd.C_ACCTSCHEMA_ID and sc.C_ACCTSCHEMA_ID = vc.C_ACCTSCHEMA_ID and sd.C_RECEIVABLE_ACCT = vc.C_VALIDCOMBINATION_ID) = ev.C_ELEMENTVALUE_ID then 'C_RECEIVABLE_ACCT' + when (select vc.ACCOUNT_ID from C_VALIDCOMBINATION vc, C_ACCTSCHEMA_DEFAULT sd, C_ACCTSCHEMA sc where sc.AD_CLIENT_ID = ev.AD_CLIENT_ID and sc.C_ACCTSCHEMA_ID = sd.C_ACCTSCHEMA_ID and sc.C_ACCTSCHEMA_ID = vc.C_ACCTSCHEMA_ID and sd.CB_ASSET_ACCT = vc.C_VALIDCOMBINATION_ID) = ev.C_ELEMENTVALUE_ID then 'CB_ASSET_ACCT' + when (select vc.ACCOUNT_ID from C_VALIDCOMBINATION vc, C_ACCTSCHEMA_DEFAULT sd, C_ACCTSCHEMA sc where sc.AD_CLIENT_ID = ev.AD_CLIENT_ID and sc.C_ACCTSCHEMA_ID = sd.C_ACCTSCHEMA_ID and sc.C_ACCTSCHEMA_ID = vc.C_ACCTSCHEMA_ID and sd.CB_CASHTRANSFER_ACCT = vc.C_VALIDCOMBINATION_ID) = ev.C_ELEMENTVALUE_ID then 'CB_CASHTRANSFER_ACCT' + when (select vc.ACCOUNT_ID from C_VALIDCOMBINATION vc, C_ACCTSCHEMA_DEFAULT sd, C_ACCTSCHEMA sc where sc.AD_CLIENT_ID = ev.AD_CLIENT_ID and sc.C_ACCTSCHEMA_ID = sd.C_ACCTSCHEMA_ID and sc.C_ACCTSCHEMA_ID = vc.C_ACCTSCHEMA_ID and sd.CB_DIFFERENCES_ACCT = vc.C_VALIDCOMBINATION_ID) = ev.C_ELEMENTVALUE_ID then 'CB_DIFFERENCES_ACCT' + when (select vc.ACCOUNT_ID from C_VALIDCOMBINATION vc, C_ACCTSCHEMA_DEFAULT sd, C_ACCTSCHEMA sc where sc.AD_CLIENT_ID = ev.AD_CLIENT_ID and sc.C_ACCTSCHEMA_ID = sd.C_ACCTSCHEMA_ID and sc.C_ACCTSCHEMA_ID = vc.C_ACCTSCHEMA_ID and sd.CB_EXPENSE_ACCT = vc.C_VALIDCOMBINATION_ID) = ev.C_ELEMENTVALUE_ID then 'CB_EXPENSE_ACCT' + when (select vc.ACCOUNT_ID from C_VALIDCOMBINATION vc, C_ACCTSCHEMA_DEFAULT sd, C_ACCTSCHEMA sc where sc.AD_CLIENT_ID = ev.AD_CLIENT_ID and sc.C_ACCTSCHEMA_ID = sd.C_ACCTSCHEMA_ID and sc.C_ACCTSCHEMA_ID = vc.C_ACCTSCHEMA_ID and sd.CB_RECEIPT_ACCT = vc.C_VALIDCOMBINATION_ID) = ev.C_ELEMENTVALUE_ID then 'CB_RECEIPT_ACCT' + when (select vc.ACCOUNT_ID from C_VALIDCOMBINATION vc, C_ACCTSCHEMA_DEFAULT sd, C_ACCTSCHEMA sc where sc.AD_CLIENT_ID = ev.AD_CLIENT_ID and sc.C_ACCTSCHEMA_ID = sd.C_ACCTSCHEMA_ID and sc.C_ACCTSCHEMA_ID = vc.C_ACCTSCHEMA_ID and sd.CH_EXPENSE_ACCT = vc.C_VALIDCOMBINATION_ID) = ev.C_ELEMENTVALUE_ID then 'CH_EXPENSE_ACCT' + when (select vc.ACCOUNT_ID from C_VALIDCOMBINATION vc, C_ACCTSCHEMA_GL gl, C_ACCTSCHEMA sc where sc.AD_CLIENT_ID = ev.AD_CLIENT_ID and sc.C_ACCTSCHEMA_ID = gl.C_ACCTSCHEMA_ID and sc.C_ACCTSCHEMA_ID = vc.C_ACCTSCHEMA_ID and gl.CURRENCYBALANCING_ACCT = vc.C_VALIDCOMBINATION_ID) = ev.C_ELEMENTVALUE_ID then 'CURRENCYBALANCING_ACCT' + when (select se.C_ELEMENTVALUE_ID from C_ACCTSCHEMA_ELEMENT se, C_ACCTSCHEMA sc where sc.AD_CLIENT_ID = ev.AD_CLIENT_ID and sc.C_ACCTSCHEMA_ID = se.C_ACCTSCHEMA_ID and se.ELEMENTTYPE='AC') = ev.C_ELEMENTVALUE_ID then 'DEFAULT_ACCT' + when (select vc.ACCOUNT_ID from C_VALIDCOMBINATION vc, C_ACCTSCHEMA_GL gl, C_ACCTSCHEMA sc where sc.AD_CLIENT_ID = ev.AD_CLIENT_ID and sc.C_ACCTSCHEMA_ID = gl.C_ACCTSCHEMA_ID and sc.C_ACCTSCHEMA_ID = vc.C_ACCTSCHEMA_ID and gl.INCOMESUMMARY_ACCT = vc.C_VALIDCOMBINATION_ID) = ev.C_ELEMENTVALUE_ID then 'INCOMESUMMARY_ACCT' + when (select vc.ACCOUNT_ID from C_VALIDCOMBINATION vc, C_ACCTSCHEMA_DEFAULT sd, C_ACCTSCHEMA sc where sc.AD_CLIENT_ID = ev.AD_CLIENT_ID and sc.C_ACCTSCHEMA_ID = sd.C_ACCTSCHEMA_ID and sc.C_ACCTSCHEMA_ID = vc.C_ACCTSCHEMA_ID and sd.NOTINVOICEDRECEIPTS_ACCT = vc.C_VALIDCOMBINATION_ID) = ev.C_ELEMENTVALUE_ID then 'NOTINVOICEDRECEIPTS_ACCT' + when (select vc.ACCOUNT_ID from C_VALIDCOMBINATION vc, C_ACCTSCHEMA_DEFAULT sd, C_ACCTSCHEMA sc where sc.AD_CLIENT_ID = ev.AD_CLIENT_ID and sc.C_ACCTSCHEMA_ID = sd.C_ACCTSCHEMA_ID and sc.C_ACCTSCHEMA_ID = vc.C_ACCTSCHEMA_ID and sd.P_ASSET_ACCT = vc.C_VALIDCOMBINATION_ID) = ev.C_ELEMENTVALUE_ID then 'P_ASSET_ACCT' + when (select vc.ACCOUNT_ID from C_VALIDCOMBINATION vc, C_ACCTSCHEMA_DEFAULT sd, C_ACCTSCHEMA sc where sc.AD_CLIENT_ID = ev.AD_CLIENT_ID and sc.C_ACCTSCHEMA_ID = sd.C_ACCTSCHEMA_ID and sc.C_ACCTSCHEMA_ID = vc.C_ACCTSCHEMA_ID and sd.P_COGS_ACCT = vc.C_VALIDCOMBINATION_ID) = ev.C_ELEMENTVALUE_ID then 'P_COGS_ACCT' + when (select vc.ACCOUNT_ID from C_VALIDCOMBINATION vc, C_ACCTSCHEMA_DEFAULT sd, C_ACCTSCHEMA sc where sc.AD_CLIENT_ID = ev.AD_CLIENT_ID and sc.C_ACCTSCHEMA_ID = sd.C_ACCTSCHEMA_ID and sc.C_ACCTSCHEMA_ID = vc.C_ACCTSCHEMA_ID and sd.P_EXPENSE_ACCT = vc.C_VALIDCOMBINATION_ID) = ev.C_ELEMENTVALUE_ID then 'P_EXPENSE_ACCT' + when (select vc.ACCOUNT_ID from C_VALIDCOMBINATION vc, C_ACCTSCHEMA_DEFAULT sd, C_ACCTSCHEMA sc where sc.AD_CLIENT_ID = ev.AD_CLIENT_ID and sc.C_ACCTSCHEMA_ID = sd.C_ACCTSCHEMA_ID and sc.C_ACCTSCHEMA_ID = vc.C_ACCTSCHEMA_ID and sd.P_REVENUE_ACCT = vc.C_VALIDCOMBINATION_ID) = ev.C_ELEMENTVALUE_ID then 'P_REVENUE_ACCT' + when (select vc.ACCOUNT_ID from C_VALIDCOMBINATION vc, C_ACCTSCHEMA_GL gl, C_ACCTSCHEMA sc where sc.AD_CLIENT_ID = ev.AD_CLIENT_ID and sc.C_ACCTSCHEMA_ID = gl.C_ACCTSCHEMA_ID and sc.C_ACCTSCHEMA_ID = vc.C_ACCTSCHEMA_ID and gl.SUSPENSEBALANCING_ACCT = vc.C_VALIDCOMBINATION_ID) = ev.C_ELEMENTVALUE_ID then 'SUSPENSEBALANCING_ACCT' + when (select vc.ACCOUNT_ID from C_VALIDCOMBINATION vc, C_ACCTSCHEMA_GL gl, C_ACCTSCHEMA sc where sc.AD_CLIENT_ID = ev.AD_CLIENT_ID and sc.C_ACCTSCHEMA_ID = gl.C_ACCTSCHEMA_ID and sc.C_ACCTSCHEMA_ID = vc.C_ACCTSCHEMA_ID and gl.SUSPENSEERROR_ACCT = vc.C_VALIDCOMBINATION_ID) = ev.C_ELEMENTVALUE_ID then 'SUSPENSEERROR_ACCT' + when (select vc.ACCOUNT_ID from C_VALIDCOMBINATION vc, C_ACCTSCHEMA_DEFAULT sd, C_ACCTSCHEMA sc where sc.AD_CLIENT_ID = ev.AD_CLIENT_ID and sc.C_ACCTSCHEMA_ID = sd.C_ACCTSCHEMA_ID and sc.C_ACCTSCHEMA_ID = vc.C_ACCTSCHEMA_ID and sd.T_CREDIT_ACCT = vc.C_VALIDCOMBINATION_ID) = ev.C_ELEMENTVALUE_ID then 'T_CREDIT_ACCT' + when (select vc.ACCOUNT_ID from C_VALIDCOMBINATION vc, C_ACCTSCHEMA_DEFAULT sd, C_ACCTSCHEMA sc where sc.AD_CLIENT_ID = ev.AD_CLIENT_ID and sc.C_ACCTSCHEMA_ID = sd.C_ACCTSCHEMA_ID and sc.C_ACCTSCHEMA_ID = vc.C_ACCTSCHEMA_ID and sd.T_DUE_ACCT = vc.C_VALIDCOMBINATION_ID) = ev.C_ELEMENTVALUE_ID then 'T_DUE_ACCT' + when (select vc.ACCOUNT_ID from C_VALIDCOMBINATION vc, C_ACCTSCHEMA_DEFAULT sd, C_ACCTSCHEMA sc where sc.AD_CLIENT_ID = ev.AD_CLIENT_ID and sc.C_ACCTSCHEMA_ID = sd.C_ACCTSCHEMA_ID and sc.C_ACCTSCHEMA_ID = vc.C_ACCTSCHEMA_ID and sd.V_LIABILITY_ACCT = vc.C_VALIDCOMBINATION_ID) = ev.C_ELEMENTVALUE_ID then 'V_LIABILITY_ACCT' + when (select vc.ACCOUNT_ID from C_VALIDCOMBINATION vc, C_ACCTSCHEMA_DEFAULT sd, C_ACCTSCHEMA sc where sc.AD_CLIENT_ID = ev.AD_CLIENT_ID and sc.C_ACCTSCHEMA_ID = sd.C_ACCTSCHEMA_ID and sc.C_ACCTSCHEMA_ID = vc.C_ACCTSCHEMA_ID and sd.W_DIFFERENCES_ACCT = vc.C_VALIDCOMBINATION_ID) = ev.C_ELEMENTVALUE_ID then 'W_DIFFERENCES_ACCT' + when (select vc.ACCOUNT_ID from C_VALIDCOMBINATION vc, C_ACCTSCHEMA_DEFAULT sd, C_ACCTSCHEMA sc where sc.AD_CLIENT_ID = ev.AD_CLIENT_ID and sc.C_ACCTSCHEMA_ID = sd.C_ACCTSCHEMA_ID and sc.C_ACCTSCHEMA_ID = vc.C_ACCTSCHEMA_ID and sd.WRITEOFF_ACCT = vc.C_VALIDCOMBINATION_ID) = ev.C_ELEMENTVALUE_ID then 'WRITEOFF_ACCT' + else '' + end) as Default_Account, + (case (select tn.PARENT_ID from AD_TREENODE tn where el.AD_TREE_ID = tn.AD_TREE_ID and tn.NODE_ID = ev.C_ELEMENTVALUE_ID) + when 0 then NULL + else (select ev2.VALUE from C_ELEMENTVALUE ev2, AD_TREENODE tn where el.AD_TREE_ID = tn.AD_TREE_ID and tn.NODE_ID = ev.C_ELEMENTVALUE_ID and ev2.C_ELEMENTVALUE_ID = tn.PARENT_ID) + END) as Account_Parent, + ev.ELEMENTLEVEL as Element_Level, + GET_ACCOUNT_OPERANDS(ev.c_elementvalue_id) as Operands + from C_ELEMENTVALUE ev, C_ELEMENT el, AD_CLIENT cl + where ev.C_ELEMENT_ID = el.C_ELEMENT_ID and el.AD_CLIENT_ID = cl.AD_CLIENT_ID]]></view> + </database>

Issue History
Date Modified	Username	Field	Change
2008-09-30 15:12	villind	New Issue
2008-09-30 15:12	villind	Assigned To	=> cromero
2008-09-30 15:12	villind	sf_bug_id	0 => 2138221
2008-09-30 15:12	villind	File Added: ChartOfAccountsExportView.diff
2008-09-30 15:12	villind	Regression testing	=> No
2008-09-30 22:33	villind	File Added: ChartOfAccountsExportView-2.diff
2008-09-30 22:33	villind	File Deleted: ChartOfAccountsExportView.diff
2008-11-10 13:09	cromero	Assigned To	cromero => pjuvara
2008-11-17 07:02	pjuvara	Tag Attached: Contribution
2008-11-17 07:02	pjuvara	Tag Attached: ReleaseCandidate
2008-11-17 07:02	pjuvara	Status	new => acknowledged
2008-11-17 07:02	pjuvara	Category	F. Localization => 09. Financial management
2008-11-22 23:39	pjuvara	Tag Attached: ModuleCandidate
2008-12-17 11:09	pjuvara	Assigned To	pjuvara => rmorley

Relationships [ Relation Graph ] [ Dependency Graph ]

Notes
There are no notes attached to this issue.