AnsweredAssumed Answered

Cost Plan XOG object query problem

Question asked by mphelan on Jun 15, 2012
Latest reply on Jun 21, 2012 by Owen_R
Hi,

We are trying to build a cost-plan xml file and having difficulty building the multiple segments under ‘cost’ and ‘units’ tags with a single sql statement

The sql we built retrieves two grouping attributes and their start, finish, amount (either cost or units) and a rec_type identifier for ‘COST’ or ‘UNITS’ in one row as follows:

GA_1, GA_2, finish, start, amount, rec_type(with value of ‘COST’ or ‘UNITS’)

With the above record information how can we loop through to build the following structure?

thanks,

Mike Phelan
            <Details>
                <Detail>
                    <Cost>
                        <segment finish="2011-01-31T00:00:00" start="2011-01-01T00:00:00" value="1000.0"/>
                        <segment finish="2011-02-28T00:00:00" start="2011-02-01T00:00:00" value="1600.0"/>
                        <segment finish="2011-03-31T00:00:00" start="2011-03-01T00:00:00" value="800.0"/>
                        <segment finish="2011-04-30T00:00:00" start="2011-04-01T00:00:00" value="100.0"/>
                    </Cost>
                    <Units>
                        <segment finish="2011-01-31T00:00:00"  start="2011-01-01T00:00:00" value="10.0"/>
                        <segment finish="2011-02-28T00:00:00"  start="2011-02-01T00:00:00" value="15.0"/>
                       <segment finish="2011-03-31T00:00:00"  start="2011-03-01T00:00:00" value="18.0"/>
                      <segment finish="2011-04-30T00:00:00"  start="2011-04-01T00:00:00" value="12.0"/>
                    </Units>
                    <GroupingAttributes>
                        <GroupingAttribute code="GA_1" value="captial"/>
                        <GroupingAttribute code="GA_2" value="clarity"/>
                    </GroupingAttributes>
                    <CustomInformation>
                        <ColumnValue name="partition_code">NIKU.ROOT</ColumnValue>
                    </CustomInformation>
                </Detail>
                <Detail>
                    <Cost>
                        <segment finish="2011-01-31T00:00:00"  start="2011-01-01T00:00:00" value="1100.0"/>
                        <segment finish="2011-02-28T00:00:00" start="2011-02-01T00:00:00" value="300.0"/>
                        <segment finish="2011-03-31T00:00:00" start="2011-03-01T00:00:00" value="200.0"/>
                        <segment finish="2011-04-30T00:00:00" start="2011-04-01T00:00:00" value="600.0"/>
                    </Cost>
                    <Units>
                        <segment finish="2011-01-31T00:00:00" start="2011-01-01T00:00:00" value="11.0"/>
                        <segment finish="2011-02-28T00:00:00"  start="2011-02-01T00:00:00" value="5.0"/>
                       <segment finish="2011-03-31T00:00:00"  start="2011-03-01T00:00:00" value="8.0"/>
                      <segment finish="2011-04-30T00:00:00"  start="2011-04-01T00:00:00" value="9.0"/>
                    </Units>
                    <GroupingAttributes>
                        <GroupingAttribute code="GA_1" value="ODC"/>
                        <GroupingAttribute code="GA_2 value="COM"/>
                    </GroupingAttributes>
                    <CustomInformation>
                        <ColumnValue name="partition_code">NIKU.ROOT</ColumnValue>
                    </CustomInformation>
                </Detail>
            </Details>

Outcomes