Reporting and Analytics for ERP

Get Involved. Join the Conversation.

Topic

    Sameh Soliman
    showing project reference details in OTBIAnswered
    Topic posted October 30, 2017 by Sameh SolimanBronze Medal: 1,250+ Points, last edited October 30, 2017, tagged Fusion Financial reporting, Fusion Project reporting, OTBI, Reports, Sample Reports, Tip 
    164 Views, 9 Comments
    Title:
    showing project reference details in OTBI
    Content:

    We are trying to union both
    Project Costing - Actual Costs Real Time and Payables Invoices - Transactions Real Time
    or Project Costing - Actual Costs Real Time and Costing - Receipt Accounting Real Time

    so we can show a report with actual project cost and reference details like invoice number , invoice line description , receipt number , PO number and PO line desc.

    the problem we are facing that Project Costing - Actual Costs Real Time subject area has reference IDs not actual numbers however the payables and receipt subject areas do not have IDs so we can not link both subject areas using set operation UNION available in oracle transnational business intelligence.

     

    Best Comment

    Prasanna Reka

    Yes i could see data in my POD, remove filters on Project Name. We receive cost from AP Invoices based on our configuration, if you are receiving data from receipts you can modify the mapping based on your requirement. Apply filters to eliminate non project related data an blank records.

    When you use Union query ( Union, Union All, Intersect, Minus) make sure you have equal number of columns from both subject areas:

    1. To the extent possible map same columns if they exist in both subject areas Ex: Project Name

    2. Map the reference data as per your need Ex: Receipt # or Invoice # to Transaction Number etc

    3. Make sure you have data at the same level in both subject areas, distributions being lowest level is best. Ex: PO can have multiple lines fulfilled/received/invoiced at different times you may see many expenditures pointing to one PO.

    Comment

     

    • Prasanna Reka

      Try using Union All - Update Filters on Project Name for both Subject Areas. Try below sample XML-

       

      <saw:report xmlns:saw="com.siebel.analytics.web/report/v1.1" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlVersion="201201160" xmlns:sawx="com.siebel.analytics.web/expression/v1.1">
         <saw:criteria xsi:type="saw:derivedCriteria" withinHierarchy="true">
            <saw:columns>
               <saw:column columnID="c8bd00c7072182d9d" xsi:type="saw:derivedColumn">
                  <saw:tableHeading>
                     <saw:caption>
                        <saw:text>Invoice Distribution Details - Project Information</saw:text></saw:caption></saw:tableHeading>
                  <saw:columnHeading>
                     <saw:caption>
                        <saw:text>Project Name</saw:text></saw:caption></saw:columnHeading></saw:column>
               <saw:column columnID="c65256fcf6086c62a" xsi:type="saw:derivedColumn">
                  <saw:tableHeading>
                     <saw:caption>
                        <saw:text>Invoice Distribution Details - Project Information</saw:text></saw:caption></saw:tableHeading>
                  <saw:columnHeading>
                     <saw:caption>
                        <saw:text>Project Organization</saw:text></saw:caption></saw:columnHeading></saw:column>
               <saw:column columnID="c5f41ee71bfe2a75f" xsi:type="saw:derivedColumn">
                  <saw:tableHeading>
                     <saw:caption>
                        <saw:text>Invoice Distribution Details - Project Information</saw:text></saw:caption></saw:tableHeading>
                  <saw:columnHeading>
                     <saw:caption>
                        <saw:text>Expenditure Type</saw:text></saw:caption></saw:columnHeading></saw:column>
               <saw:column columnID="ccdf1026787c40afa" xsi:type="saw:derivedColumn">
                  <saw:tableHeading>
                     <saw:caption>
                        <saw:text>Invoice Distribution Details - Project Information</saw:text></saw:caption></saw:tableHeading>
                  <saw:columnHeading>
                     <saw:caption>
                        <saw:text>Task Name</saw:text></saw:caption></saw:columnHeading></saw:column>
               <saw:column columnID="cc1a620e8f54ff288" xsi:type="saw:derivedColumn">
                  <saw:tableHeading>
                     <saw:caption>
                        <saw:text>Invoice Distribution Details - Project Information</saw:text></saw:caption></saw:tableHeading>
                  <saw:columnHeading>
                     <saw:caption>
                        <saw:text>Project Expenditure Item Date</saw:text></saw:caption></saw:columnHeading></saw:column>
               <saw:column columnID="c8d8605a25c458594" xsi:type="saw:derivedColumn">
                  <saw:tableHeading>
                     <saw:caption>
                        <saw:text>General Information</saw:text></saw:caption></saw:tableHeading>
                  <saw:columnHeading>
                     <saw:caption>
                        <saw:text>Invoice Number</saw:text></saw:caption></saw:columnHeading></saw:column>
               <saw:column columnID="c91e4081d6e94c51d" xsi:type="saw:derivedColumn">
                  <saw:tableHeading>
                     <saw:caption>
                        <saw:text>Supplier</saw:text></saw:caption></saw:tableHeading>
                  <saw:columnHeading>
                     <saw:caption>
                        <saw:text>Supplier</saw:text></saw:caption></saw:columnHeading></saw:column>
               <saw:column columnID="cc11c553bd4e11658" xsi:type="saw:derivedColumn">
                  <saw:tableHeading>
                     <saw:caption>
                        <saw:text>Supplier</saw:text></saw:caption></saw:tableHeading>
                  <saw:columnHeading>
                     <saw:caption>
                        <saw:text>Supplier Number</saw:text></saw:caption></saw:columnHeading></saw:column>
               <saw:column columnID="c252cc5b8b77a3b42" xsi:type="saw:derivedColumn">
                  <saw:tableHeading>
                     <saw:caption>
                        <saw:text>Original Source References</saw:text></saw:caption></saw:tableHeading>
                  <saw:columnHeading>
                     <saw:caption>
                        <saw:text>Source Transaction Distribution Reference</saw:text></saw:caption></saw:columnHeading></saw:column>
               <saw:column columnID="c26dc82304eeb5347" xsi:type="saw:derivedColumn">
                  <saw:tableHeading>
                     <saw:caption>
                        <saw:text>Expenditure Item and Cost Distribution Measures</saw:text></saw:caption></saw:tableHeading>
                  <saw:columnHeading>
                     <saw:caption>
                        <saw:text>Cost</saw:text></saw:caption></saw:columnHeading></saw:column>
               <saw:column columnID="cb3680446e5b20d5b" xsi:type="saw:derivedColumn">
                  <saw:tableHeading>
                     <saw:caption>
                        <saw:text>Original Source References</saw:text></saw:caption></saw:tableHeading>
                  <saw:columnHeading>
                     <saw:caption>
                        <saw:text>Original Transaction Reference</saw:text></saw:caption></saw:columnHeading></saw:column>
               <saw:column columnID="c996cf75eda8f2feb" xsi:type="saw:derivedColumn">
                  <saw:tableHeading>
                     <saw:caption>
                        <saw:text>Original Source References</saw:text></saw:caption></saw:tableHeading>
                  <saw:columnHeading>
                     <saw:caption>
                        <saw:text>Source Transaction Line Reference</saw:text></saw:caption></saw:columnHeading></saw:column></saw:columns>
            <saw:from>
               <saw:criteria xsi:type="saw:setCriteria" op="unionAll">
                  <saw:criteria xsi:type="saw:simpleCriteria" subjectArea="&quot;Project Costing - Actual Costs Real Time&quot;">
                     <saw:columns>
                        <saw:column xsi:type="saw:regularColumn" columnID="c532ed07793bd0a5e">
                           <saw:columnFormula>
                              <sawx:expr xsi:type="sawx:sqlExpression">"Project"."Project Name"</sawx:expr></saw:columnFormula></saw:column>
                        <saw:column xsi:type="saw:regularColumn" columnID="c447b1786b260af43">
                           <saw:columnFormula>
                              <sawx:expr xsi:type="sawx:sqlExpression">"- Project Organization"."Project Organization Name"</sawx:expr></saw:columnFormula></saw:column>
                        <saw:column xsi:type="saw:regularColumn" columnID="c944eaf7b8a676586">
                           <saw:columnFormula>
                              <sawx:expr xsi:type="sawx:sqlExpression">"Expenditure Type"."Resource Type Name"</sawx:expr></saw:columnFormula></saw:column>
                        <saw:column xsi:type="saw:regularColumn" columnID="c2ee266ca96e48aa1">
                           <saw:columnFormula>
                              <sawx:expr xsi:type="sawx:sqlExpression">"Task"."Task Name"</sawx:expr></saw:columnFormula></saw:column>
                        <saw:column xsi:type="saw:regularColumn" columnID="c92004f8478fd335d">
                           <saw:columnFormula>
                              <sawx:expr xsi:type="sawx:sqlExpression">"Expenditure Item and Cost Distribution Details"."Expenditure Item Date"</sawx:expr></saw:columnFormula></saw:column>
                        <saw:column xsi:type="saw:regularColumn" columnID="ceccaef8cc7e49307">
                           <saw:columnFormula>
                              <sawx:expr xsi:type="sawx:sqlExpression">"Expenditure Item"."Expenditure Item"</sawx:expr></saw:columnFormula></saw:column>
                        <saw:column xsi:type="saw:regularColumn" columnID="c4eec351f6b765c1a">
                           <saw:columnFormula>
                              <sawx:expr xsi:type="sawx:sqlExpression">"Supplier"."Supplier Name"</sawx:expr></saw:columnFormula></saw:column>
                        <saw:column xsi:type="saw:regularColumn" columnID="ce63a061a51afb76a">
                           <saw:columnFormula>
                              <sawx:expr xsi:type="sawx:sqlExpression">"Supplier"."Supplier Number"</sawx:expr></saw:columnFormula></saw:column>
                        <saw:column xsi:type="saw:regularColumn" columnID="ca161cbdf3a314df9">
                           <saw:columnFormula>
                              <sawx:expr xsi:type="sawx:sqlExpression">"Original Source References"."Expenditure Item Original Distribution Id"</sawx:expr></saw:columnFormula></saw:column>
                        <saw:column xsi:type="saw:regularColumn" columnID="cf149e4b174f53a68">
                           <saw:columnFormula>
                              <sawx:expr xsi:type="sawx:sqlExpression">"- Expenditure Item and Cost Distribution Measures"."Cost"</sawx:expr></saw:columnFormula></saw:column>
                        <saw:column xsi:type="saw:regularColumn" columnID="c4487a487fa9543b1">
                           <saw:columnFormula>
                              <sawx:expr xsi:type="sawx:sqlExpression">"Original Source References"."Expenditure Item Original Transaction Reference"</sawx:expr></saw:columnFormula></saw:column>
                        <saw:column xsi:type="saw:regularColumn" columnID="ca00597c5f016403c">
                           <saw:columnFormula>
                              <sawx:expr xsi:type="sawx:sqlExpression">"Original Source References"."Expenditure Item Original Line Number"</sawx:expr></saw:columnFormula></saw:column></saw:columns>
                     <saw:filter>
                        <sawx:expr xsi:type="sawx:comparison" op="equal">
                           <sawx:expr xsi:type="sawx:sqlExpression">"Project"."Project Name"</sawx:expr>
                           <sawx:expr xsi:type="xsd:string">Rayo Ranch - Development - Mandarin - 2015 Plantings</sawx:expr></sawx:expr></saw:filter></saw:criteria>
                  <saw:criteria xsi:type="saw:simpleCriteria" subjectArea="&quot;Payables Invoices - Transactions Real Time&quot;">
                     <saw:columns>
                        <saw:column xsi:type="saw:regularColumn" columnID="c1317ede4a7381c2e">
                           <saw:columnFormula>
                              <sawx:expr xsi:type="sawx:sqlExpression">"- Invoice Distribution Details - Project Information"."Project Name"</sawx:expr></saw:columnFormula></saw:column>
                        <saw:column xsi:type="saw:regularColumn" columnID="c15a37ea6abe72f99">
                           <saw:columnFormula>
                              <sawx:expr xsi:type="sawx:sqlExpression">"- Invoice Distribution Details - Project Information"."Project Organization Name"</sawx:expr></saw:columnFormula></saw:column>
                        <saw:column xsi:type="saw:regularColumn" columnID="c120c1fa3b77ba6df">
                           <saw:columnFormula>
                              <sawx:expr xsi:type="sawx:sqlExpression">"- Invoice Distribution Details - Project Information"."Project Expenditure Type Name"</sawx:expr></saw:columnFormula></saw:column>
                        <saw:column xsi:type="saw:regularColumn" columnID="c9d45b6375118dbe7">
                           <saw:columnFormula>
                              <sawx:expr xsi:type="sawx:sqlExpression">"- Invoice Distribution Details - Project Information"."Task Name"</sawx:expr></saw:columnFormula></saw:column>
                        <saw:column xsi:type="saw:regularColumn" columnID="cc44816b952c2b52a">
                           <saw:columnFormula>
                              <sawx:expr xsi:type="sawx:sqlExpression">"- Invoice Distribution Details - Project Information"."Project Expenditure Item Date"</sawx:expr></saw:columnFormula></saw:column>
                        <saw:column xsi:type="saw:regularColumn" columnID="ce4bd24fa0e970ee9">
                           <saw:columnFormula>
                              <sawx:expr xsi:type="sawx:sqlExpression">"- General Information"."Invoice Number"</sawx:expr></saw:columnFormula></saw:column>
                        <saw:column xsi:type="saw:regularColumn" columnID="c69332f29b6d8c40d">
                           <saw:columnFormula>
                              <sawx:expr xsi:type="sawx:sqlExpression">"Supplier"."Supplier Name"</sawx:expr></saw:columnFormula></saw:column>
                        <saw:column xsi:type="saw:regularColumn" columnID="cc4b260c9eabf6dea">
                           <saw:columnFormula>
                              <sawx:expr xsi:type="sawx:sqlExpression">"Supplier"."Supplier Number"</sawx:expr></saw:columnFormula></saw:column>
                        <saw:column xsi:type="saw:regularColumn" columnID="cf11e384277da8bdb">
                           <saw:columnFormula>
                              <sawx:expr xsi:type="sawx:sqlExpression">"- Invoice Distribution Details - General Information"."Distribution Line Number"</sawx:expr></saw:columnFormula></saw:column>
                        <saw:column xsi:type="saw:regularColumn" columnID="cf68647126e5d9aa8">
                           <saw:columnFormula>
                              <sawx:expr xsi:type="sawx:sqlExpression">"- Invoice Distributions"."Distribution Amount"</sawx:expr></saw:columnFormula></saw:column>
                        <saw:column xsi:type="saw:regularColumn" columnID="c988c6a953d30d104">
                           <saw:columnFormula>
                              <sawx:expr xsi:type="sawx:sqlExpression">"- Invoice Distribution Details - Purchase Order Information"."Purchase Order"</sawx:expr></saw:columnFormula></saw:column>
                        <saw:column xsi:type="saw:regularColumn" columnID="c59dd201481d23889">
                           <saw:columnFormula>
                              <sawx:expr xsi:type="sawx:sqlExpression">"- Invoice Distribution Details - Purchase Order Information"."Purchase Order Line"</sawx:expr></saw:columnFormula></saw:column></saw:columns>
                     <saw:filter>
                        <sawx:expr xsi:type="sawx:logical" op="and">
                           <sawx:expr xsi:type="sawx:comparison" op="notNull">
                              <sawx:expr xsi:type="sawx:sqlExpression">"- Invoice Distribution Details - Project Information"."Project Name"</sawx:expr></sawx:expr>
                           <sawx:expr xsi:type="sawx:comparison" op="equal">
                              <sawx:expr xsi:type="sawx:sqlExpression">"- Invoice Distribution Details - Project Information"."Project Name"</sawx:expr>
                              <sawx:expr xsi:type="xsd:string">Rayo Ranch - Development - Mandarin - 2015 Plantings</sawx:expr></sawx:expr></sawx:expr></saw:filter></saw:criteria></saw:criteria></saw:from></saw:criteria>
         <saw:views currentView="0">
            <saw:view xsi:type="saw:compoundView" name="compoundView!1">
               <saw:cvTable>
                  <saw:cvRow>
                     <saw:cvCell viewName="titleView!1"/></saw:cvRow>
                  <saw:cvRow>
                     <saw:cvCell viewName="tableView!1"/></saw:cvRow></saw:cvTable></saw:view>
            <saw:view xsi:type="saw:titleView" name="titleView!1"/>
            <saw:view xsi:type="saw:tableView" name="tableView!1" scrollingEnabled="false">
               <saw:edges>
                  <saw:edge axis="page" showColumnHeader="true"/>
                  <saw:edge axis="section"/>
                  <saw:edge axis="row" showColumnHeader="true">
                     <saw:edgeLayers>
                        <saw:edgeLayer type="column" columnID="c65256fcf6086c62a"/>
                        <saw:edgeLayer type="column" columnID="c8bd00c7072182d9d"/>
                        <saw:edgeLayer type="column" columnID="c5f41ee71bfe2a75f"/>
                        <saw:edgeLayer type="column" columnID="ccdf1026787c40afa"/>
                        <saw:edgeLayer type="column" columnID="cc1a620e8f54ff288"/>
                        <saw:edgeLayer type="column" columnID="c8d8605a25c458594"/>
                        <saw:edgeLayer type="column" columnID="c91e4081d6e94c51d"/>
                        <saw:edgeLayer type="column" columnID="cc11c553bd4e11658"/>
                        <saw:edgeLayer type="column" columnID="c252cc5b8b77a3b42"/>
                        <saw:edgeLayer type="column" columnID="c26dc82304eeb5347"/>
                        <saw:edgeLayer type="column" columnID="cb3680446e5b20d5b"/>
                        <saw:edgeLayer type="column" columnID="c996cf75eda8f2feb"/></saw:edgeLayers></saw:edge>
                  <saw:edge axis="column" showColumnHeader="rollover"/></saw:edges></saw:view></saw:views></saw:report>

    • Sameh Soliman

      thanks a lot Prasanna Reka

       

      did your query work with you? it did not give me any data. i think you are trying to join expenditure item number with invoice number that is why no data.

       

      I have done something similar but my challenge now is that i have few records showing as blank when the source is receipt.

      i noticed that the project actual cost subject area has more data than receipt accounting subject area when i filter both by project.

       

       

      <saw:report xmlns:saw="com.siebel.analytics.web/report/v1.1" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlVersion="201201160" xmlns:sawx="com.siebel.analytics.web/expression/v1.1">
         <saw:criteria xsi:type="saw:derivedCriteria">
            <saw:columns>
               <saw:column xsi:type="saw:derivedColumn" columnID="c11cd2ccb8cb4cd80">
                  <saw:tableHeading>
                     <saw:caption>
                        <saw:text>Original Source References</saw:text></saw:caption></saw:tableHeading>
                  <saw:columnHeading>
                     <saw:caption>
                        <saw:text>Transaction Source</saw:text></saw:caption></saw:columnHeading></saw:column>
               <saw:column xsi:type="saw:derivedColumn" columnID="c4aed226651a09d3a">
                  <saw:tableHeading>
                     <saw:caption>
                        <saw:text>Original Source References</saw:text></saw:caption></saw:tableHeading>
                  <saw:columnHeading>
                     <saw:caption>
                        <saw:text>Document</saw:text></saw:caption></saw:columnHeading></saw:column>
               <saw:column xsi:type="saw:derivedColumn" columnID="cc942b16f415367a3">
                  <saw:tableHeading>
                     <saw:caption>
                        <saw:text>Project</saw:text></saw:caption></saw:tableHeading>
                  <saw:columnHeading>
                     <saw:caption>
                        <saw:text>Project Number</saw:text></saw:caption></saw:columnHeading></saw:column>
               <saw:column xsi:type="saw:derivedColumn" columnID="c02553aa50468075f">
                  <saw:tableHeading>
                     <saw:caption>
                        <saw:text>Project</saw:text></saw:caption></saw:tableHeading>
                  <saw:columnHeading>
                     <saw:caption>
                        <saw:text>Project Manager</saw:text></saw:caption></saw:columnHeading></saw:column>
               <saw:column xsi:type="saw:derivedColumn" columnID="cb205d54a33178fef">
                  <saw:tableHeading>
                     <saw:caption>
                        <saw:text>Project Type Attributes</saw:text></saw:caption></saw:tableHeading>
                  <saw:columnHeading>
                     <saw:caption>
                        <saw:text>Project Type</saw:text></saw:caption></saw:columnHeading></saw:column>
               <saw:column xsi:type="saw:derivedColumn" columnID="c868a4974542667c0">
                  <saw:tableHeading>
                     <saw:caption>
                        <saw:text>Project Classification</saw:text></saw:caption></saw:tableHeading>
                  <saw:columnHeading>
                     <saw:caption>
                        <saw:text>Class Category Description</saw:text></saw:caption></saw:columnHeading></saw:column>
               <saw:column xsi:type="saw:derivedColumn" columnID="c44a91afb39e984c9">
                  <saw:tableHeading>
                     <saw:caption>
                        <saw:text>Project Classification</saw:text></saw:caption></saw:tableHeading>
                  <saw:columnHeading>
                     <saw:caption>
                        <saw:text>Class Code Description</saw:text></saw:caption></saw:columnHeading></saw:column>
               <saw:column xsi:type="saw:derivedColumn" columnID="cdf8b99018ea233c5">
                  <saw:displayFormat>
                     <saw:formatSpec suppress="suppress" wrapText="true"/></saw:displayFormat>
                  <saw:tableHeading>
                     <saw:caption fmt="text">
                        <saw:text>Invoice</saw:text></saw:caption></saw:tableHeading>
                  <saw:columnHeading>
                     <saw:displayFormat>
                        <saw:formatSpec/></saw:displayFormat>
                     <saw:caption fmt="text">
                        <saw:text>Invoice Number</saw:text></saw:caption></saw:columnHeading></saw:column>
               <saw:column xsi:type="saw:derivedColumn" columnID="c78f0eb401b910d9c">
                  <saw:displayFormat>
                     <saw:formatSpec suppress="suppress" wrapText="true"/></saw:displayFormat>
                  <saw:tableHeading>
                     <saw:caption fmt="text">
                        <saw:text>Invoice</saw:text></saw:caption></saw:tableHeading>
                  <saw:columnHeading>
                     <saw:displayFormat>
                        <saw:formatSpec/></saw:displayFormat>
                     <saw:caption fmt="text">
                        <saw:text>Invoice Line</saw:text></saw:caption></saw:columnHeading></saw:column>
               <saw:column xsi:type="saw:derivedColumn" columnID="cad4dfa56ad170110">
                  <saw:tableHeading>
                     <saw:caption fmt="text">
                        <saw:text>Invoice</saw:text></saw:caption></saw:tableHeading>
                  <saw:columnHeading>
                     <saw:caption fmt="text">
                        <saw:text>Invoice Line Desc</saw:text></saw:caption></saw:columnHeading></saw:column>
               <saw:column xsi:type="saw:derivedColumn" columnID="cc32503c64b72a3cf">
                  <saw:displayFormat>
                     <saw:formatSpec suppress="repeat" wrapText="true"/></saw:displayFormat>
                  <saw:tableHeading>
                     <saw:caption fmt="text">
                        <saw:text>Invoice</saw:text></saw:caption></saw:tableHeading>
                  <saw:columnHeading>
                     <saw:displayFormat>
                        <saw:formatSpec/></saw:displayFormat>
                     <saw:caption fmt="text">
                        <saw:text>Line Amount</saw:text></saw:caption></saw:columnHeading></saw:column>
               <saw:column xsi:type="saw:derivedColumn" columnID="c0a7d87f5d337f0c3">
                  <saw:tableHeading>
                     <saw:caption fmt="text">
                        <saw:text>Invoice</saw:text></saw:caption></saw:tableHeading>
                  <saw:columnHeading>
                     <saw:caption fmt="text">
                        <saw:text>Total Tax</saw:text></saw:caption></saw:columnHeading></saw:column>
               <saw:column xsi:type="saw:derivedColumn" columnID="ce54385f4b0f79c68">
                  <saw:tableHeading>
                     <saw:caption>
                        <saw:text>Supplier</saw:text></saw:caption></saw:tableHeading>
                  <saw:columnHeading>
                     <saw:caption>
                        <saw:text>Supplier</saw:text></saw:caption></saw:columnHeading></saw:column>
               <saw:column columnID="cc37df6c61ff65da9" xsi:type="saw:derivedColumn">
                  <saw:displayFormat>
                     <saw:formatSpec suppress="suppress" wrapText="true"/></saw:displayFormat>
                  <saw:tableHeading>
                     <saw:caption fmt="text">
                        <saw:text>Receipt</saw:text></saw:caption></saw:tableHeading>
                  <saw:columnHeading>
                     <saw:caption fmt="text">
                        <saw:text>Receipt</saw:text></saw:caption>
                     <saw:displayFormat>
                        <saw:formatSpec/></saw:displayFormat></saw:columnHeading></saw:column>
               <saw:column columnID="cf304ae09f7ad134f" xsi:type="saw:derivedColumn">
                  <saw:displayFormat>
                     <saw:formatSpec suppress="suppress" wrapText="true"/></saw:displayFormat>
                  <saw:tableHeading>
                     <saw:caption fmt="text">
                        <saw:text>PO</saw:text></saw:caption></saw:tableHeading>
                  <saw:columnHeading>
                     <saw:caption fmt="text">
                        <saw:text>PO Number</saw:text></saw:caption>
                     <saw:displayFormat>
                        <saw:formatSpec/></saw:displayFormat></saw:columnHeading></saw:column>
               <saw:column columnID="c57c027f2972e0124" xsi:type="saw:derivedColumn">
                  <saw:displayFormat>
                     <saw:formatSpec suppress="suppress" wrapText="true"/></saw:displayFormat>
                  <saw:tableHeading>
                     <saw:caption fmt="text">
                        <saw:text>PO</saw:text></saw:caption></saw:tableHeading>
                  <saw:columnHeading>
                     <saw:caption fmt="text">
                        <saw:text>PO Line Number</saw:text></saw:caption>
                     <saw:displayFormat>
                        <saw:formatSpec/></saw:displayFormat></saw:columnHeading></saw:column>
               <saw:column columnID="c0af18b2d2cd5f797" xsi:type="saw:derivedColumn">
                  <saw:displayFormat>
                     <saw:formatSpec suppress="suppress" wrapText="true"/></saw:displayFormat>
                  <saw:tableHeading>
                     <saw:caption fmt="text">
                        <saw:text>PO</saw:text></saw:caption></saw:tableHeading>
                  <saw:columnHeading>
                     <saw:caption fmt="text">
                        <saw:text>PO Line</saw:text></saw:caption>
                     <saw:displayFormat>
                        <saw:formatSpec/></saw:displayFormat></saw:columnHeading></saw:column>
               <saw:column columnID="c34246e18b595fdef" xsi:type="saw:derivedColumn">
                  <saw:displayFormat>
                     <saw:formatSpec suppress="suppress" wrapText="true"/></saw:displayFormat>
                  <saw:tableHeading>
                     <saw:caption fmt="text">
                        <saw:text>PO</saw:text></saw:caption></saw:tableHeading>
                  <saw:columnHeading>
                     <saw:caption fmt="text">
                        <saw:text>PO Line Amount</saw:text></saw:caption>
                     <saw:displayFormat>
                        <saw:formatSpec/></saw:displayFormat></saw:columnHeading></saw:column></saw:columns>
            <saw:from>
               <saw:criteria xsi:type="saw:setCriteria" op="union">
                  <saw:criteria xsi:type="saw:simpleCriteria" subjectArea="&quot;Project Costing - Actual Costs Real Time&quot;">
                     <saw:columns>
                        <saw:column xsi:type="saw:regularColumn" columnID="c6267de0d515e6fc2">
                           <saw:columnFormula>
                              <sawx:expr xsi:type="sawx:sqlExpression">"Original Source References"."Transaction Source Name"</sawx:expr></saw:columnFormula></saw:column>
                        <saw:column xsi:type="saw:regularColumn" columnID="cb3acc1cb067e49bd">
                           <saw:columnFormula>
                              <sawx:expr xsi:type="sawx:sqlExpression">"Original Source References"."Document"</sawx:expr></saw:columnFormula></saw:column>
                        <saw:column xsi:type="saw:regularColumn" columnID="c3999388fe6971d24">
                           <saw:columnFormula>
                              <sawx:expr xsi:type="sawx:sqlExpression">"Project"."Project Number"</sawx:expr></saw:columnFormula></saw:column>
                        <saw:column xsi:type="saw:regularColumn" columnID="c8a6c558392255d3a">
                           <saw:columnFormula>
                              <sawx:expr xsi:type="sawx:sqlExpression">"Project"."Current Project Manager"</sawx:expr></saw:columnFormula></saw:column>
                        <saw:column xsi:type="saw:regularColumn" columnID="c257d5fb0bdd703c4">
                           <saw:columnFormula>
                              <sawx:expr xsi:type="sawx:sqlExpression">"- Project Type Attributes"."Project Type Name"</sawx:expr></saw:columnFormula></saw:column>
                        <saw:column xsi:type="saw:regularColumn" columnID="c836c6939064630fa">
                           <saw:columnFormula>
                              <sawx:expr xsi:type="sawx:sqlExpression">"- Project Classification"."Project Classification Category Description"</sawx:expr></saw:columnFormula></saw:column>
                        <saw:column xsi:type="saw:regularColumn" columnID="cf6030de645b93760">
                           <saw:columnFormula>
                              <sawx:expr xsi:type="sawx:sqlExpression">"- Project Classification"."Project Classification Description"</sawx:expr></saw:columnFormula></saw:column>
                        <saw:column xsi:type="saw:regularColumn" columnID="ce9c350402aed8e51">
                           <saw:columnFormula>
                              <sawx:expr xsi:type="sawx:sqlExpression">cast ( null as char)</sawx:expr></saw:columnFormula>
                           <saw:tableHeading>
                              <saw:caption fmt="text">
                                 <saw:text>Invoice</saw:text></saw:caption></saw:tableHeading>
                           <saw:columnHeading>
                              <saw:caption fmt="text">
                                 <saw:text>Invoice Number</saw:text></saw:caption></saw:columnHeading></saw:column>
                        <saw:column xsi:type="saw:regularColumn" columnID="c6fe7837671721c75">
                           <saw:columnFormula>
                              <sawx:expr xsi:type="sawx:sqlExpression">cast ( null as int)</sawx:expr></saw:columnFormula>
                           <saw:tableHeading>
                              <saw:caption fmt="text">
                                 <saw:text>Invoice</saw:text></saw:caption></saw:tableHeading>
                           <saw:columnHeading>
                              <saw:caption fmt="text">
                                 <saw:text>Invoice Line</saw:text></saw:caption></saw:columnHeading></saw:column>
                        <saw:column xsi:type="saw:regularColumn" columnID="c355763fa35d00b5a">
                           <saw:columnFormula>
                              <sawx:expr xsi:type="sawx:sqlExpression">cast ( null as char)</sawx:expr></saw:columnFormula>
                           <saw:tableHeading>
                              <saw:caption fmt="text">
                                 <saw:text>Invoice</saw:text></saw:caption></saw:tableHeading>
                           <saw:columnHeading>
                              <saw:caption fmt="text">
                                 <saw:text>Line Desc</saw:text></saw:caption></saw:columnHeading></saw:column>
                        <saw:column xsi:type="saw:regularColumn" columnID="c6ec4e21c04870e23" aggRule="sum">
                           <saw:columnFormula>
                              <sawx:expr xsi:type="sawx:sqlExpression">cast ( null as INT)</sawx:expr></saw:columnFormula>
                           <saw:tableHeading>
                              <saw:caption fmt="text">
                                 <saw:text>Invoice</saw:text></saw:caption></saw:tableHeading>
                           <saw:columnHeading>
                              <saw:caption fmt="text">
                                 <saw:text>Line Amount</saw:text></saw:caption></saw:columnHeading></saw:column>
                        <saw:column xsi:type="saw:regularColumn" columnID="c0f61b4c237f7fa8b" aggRule="sum">
                           <saw:columnFormula>
                              <sawx:expr xsi:type="sawx:sqlExpression">Cast ( null as INT)</sawx:expr></saw:columnFormula>
                           <saw:tableHeading>
                              <saw:caption fmt="text">
                                 <saw:text>Invoice</saw:text></saw:caption></saw:tableHeading>
                           <saw:columnHeading>
                              <saw:caption fmt="text">
                                 <saw:text>Line Tax</saw:text></saw:caption></saw:columnHeading></saw:column>
                        <saw:column xsi:type="saw:regularColumn" columnID="c719b60c04c68b47f">
                           <saw:columnFormula>
                              <sawx:expr xsi:type="sawx:sqlExpression">cast(null as char)</sawx:expr></saw:columnFormula>
                           <saw:tableHeading>
                              <saw:caption fmt="text">
                                 <saw:text>Invoice</saw:text></saw:caption></saw:tableHeading>
                           <saw:columnHeading>
                              <saw:caption fmt="text">
                                 <saw:text>Supplier</saw:text></saw:caption></saw:columnHeading></saw:column>
                        <saw:column xsi:type="saw:regularColumn" columnID="ceb1f856e8b1fffde">
                           <saw:columnFormula>
                              <sawx:expr xsi:type="sawx:sqlExpression">cast ( null as char)</sawx:expr></saw:columnFormula>
                           <saw:tableHeading>
                              <saw:caption fmt="text">
                                 <saw:text>Receipt</saw:text></saw:caption></saw:tableHeading>
                           <saw:columnHeading>
                              <saw:caption fmt="text">
                                 <saw:text>Receipt Number</saw:text></saw:caption></saw:columnHeading></saw:column>
                        <saw:column xsi:type="saw:regularColumn" columnID="cfb7f2d5e6627fcd2">
                           <saw:columnFormula>
                              <sawx:expr xsi:type="sawx:sqlExpression">Cast (null as char)</sawx:expr></saw:columnFormula>
                           <saw:tableHeading>
                              <saw:caption fmt="text">
                                 <saw:text>PO</saw:text></saw:caption></saw:tableHeading>
                           <saw:columnHeading>
                              <saw:caption fmt="text">
                                 <saw:text>PO Number</saw:text></saw:caption></saw:columnHeading></saw:column>
                        <saw:column xsi:type="saw:regularColumn" columnID="caeeeb35d8e5e5464">
                           <saw:columnFormula>
                              <sawx:expr xsi:type="sawx:sqlExpression">cast ( null as char)</sawx:expr></saw:columnFormula>
                           <saw:tableHeading>
                              <saw:caption fmt="text">
                                 <saw:text>PO</saw:text></saw:caption></saw:tableHeading>
                           <saw:columnHeading>
                              <saw:caption fmt="text">
                                 <saw:text>PO Line Number</saw:text></saw:caption></saw:columnHeading></saw:column>
                        <saw:column xsi:type="saw:regularColumn" columnID="c1f59b9612548851f">
                           <saw:columnFormula>
                              <sawx:expr xsi:type="sawx:sqlExpression">cast ( null as char)</sawx:expr></saw:columnFormula>
                           <saw:tableHeading>
                              <saw:caption fmt="text">
                                 <saw:text>PO</saw:text></saw:caption></saw:tableHeading>
                           <saw:columnHeading>
                              <saw:caption fmt="text">
                                 <saw:text>PO Description</saw:text></saw:caption></saw:columnHeading></saw:column>
                        <saw:column xsi:type="saw:regularColumn" columnID="c47fd9ef51dd75500">
                           <saw:columnFormula>
                              <sawx:expr xsi:type="sawx:sqlExpression">Cast ( null as int)</sawx:expr></saw:columnFormula>
                           <saw:tableHeading>
                              <saw:caption fmt="text">
                                 <saw:text>PO</saw:text></saw:caption></saw:tableHeading>
                           <saw:columnHeading>
                              <saw:caption fmt="text">
                                 <saw:text>Line Amount</saw:text></saw:caption></saw:columnHeading></saw:column></saw:columns>
                     <saw:filter>
                        <sawx:expr xsi:type="sawx:logical" op="and">
                           <sawx:expr xsi:type="sawx:list" op="in">
                              <sawx:expr xsi:type="sawx:sqlExpression">"Original Source References"."Transaction Source Name"</sawx:expr>
                              <sawx:expr xsi:type="xsd:string">Oracle Fusion Cost Management</sawx:expr>
                              <sawx:expr xsi:type="xsd:string">Oracle Fusion Payables</sawx:expr></sawx:expr>
                           <sawx:expr xsi:type="sawx:comparison" op="notEqual">
                              <sawx:expr xsi:type="sawx:sqlExpression">"Original Source References"."Document"</sawx:expr>
                              <sawx:expr xsi:type="xsd:string">Expense Report</sawx:expr></sawx:expr></sawx:expr></saw:filter></saw:criteria>
                  <saw:criteria xsi:type="saw:simpleCriteria" subjectArea="&quot;Payables Invoices - Transactions Real Time&quot;">
                     <saw:columns>
                        <saw:column xsi:type="saw:regularColumn" columnID="ca01bd5f134ecbd27">
                           <saw:columnFormula>
                              <sawx:expr xsi:type="sawx:sqlExpression">'Oracle Fusion Payables'</sawx:expr></saw:columnFormula></saw:column>
                        <saw:column xsi:type="saw:regularColumn" columnID="c00fd4a87bd120317">
                           <saw:columnFormula>
                              <sawx:expr xsi:type="sawx:sqlExpression">'Supplier Invoice'</sawx:expr></saw:columnFormula></saw:column>
                        <saw:column xsi:type="saw:regularColumn" columnID="c092629e0453d684e">
                           <saw:columnFormula>
                              <sawx:expr xsi:type="sawx:sqlExpression">"- Invoice Line Details - Project Information"."Project Number"</sawx:expr></saw:columnFormula></saw:column>
                        <saw:column xsi:type="saw:regularColumn" columnID="ce4772a2397557b46">
                           <saw:columnFormula>
                              <sawx:expr xsi:type="sawx:sqlExpression">cast( null as char)</sawx:expr></saw:columnFormula>
                           <saw:tableHeading>
                              <saw:caption fmt="text">
                                 <saw:text>Project</saw:text></saw:caption></saw:tableHeading>
                           <saw:columnHeading>
                              <saw:caption fmt="text">
                                 <saw:text>Project Type</saw:text></saw:caption></saw:columnHeading></saw:column>
                        <saw:column xsi:type="saw:regularColumn" columnID="cbce48a040b7c05ba">
                           <saw:columnFormula>
                              <sawx:expr xsi:type="sawx:sqlExpression">cast( null as char)</sawx:expr></saw:columnFormula>
                           <saw:tableHeading>
                              <saw:caption fmt="text">
                                 <saw:text>Project</saw:text></saw:caption></saw:tableHeading>
                           <saw:columnHeading>
                              <saw:caption fmt="text">
                                 <saw:text>Project Category</saw:text></saw:caption></saw:columnHeading></saw:column>
                        <saw:column xsi:type="saw:regularColumn" columnID="cbc6f585a1eb6bcad">
                           <saw:columnFormula>
                              <sawx:expr xsi:type="sawx:sqlExpression">cast( null as char)</sawx:expr></saw:columnFormula>
                           <saw:tableHeading>
                              <saw:caption fmt="text">
                                 <saw:text>Project</saw:text></saw:caption></saw:tableHeading>
                           <saw:columnHeading>
                              <saw:caption fmt="text">
                                 <saw:text>Project Class</saw:text></saw:caption></saw:columnHeading></saw:column>
                        <saw:column xsi:type="saw:regularColumn" columnID="cc8a0b47688ce4acf">
                           <saw:columnFormula>
                              <sawx:expr xsi:type="sawx:sqlExpression">cast( null as char)</sawx:expr></saw:columnFormula>
                           <saw:tableHeading>
                              <saw:caption fmt="text">
                                 <saw:text>Project </saw:text></saw:caption></saw:tableHeading>
                           <saw:columnHeading>
                              <saw:caption fmt="text">
                                 <saw:text>Project Manager</saw:text></saw:caption></saw:columnHeading></saw:column>
                        <saw:column xsi:type="saw:regularColumn" columnID="cbf08fd229256908e">
                           <saw:columnFormula>
                              <sawx:expr xsi:type="sawx:sqlExpression">"- General Information"."Invoice Number"</sawx:expr></saw:columnFormula></saw:column>
                        <saw:column xsi:type="saw:regularColumn" columnID="c2be70a8f9e8c7d0c">
                           <saw:columnFormula>
                              <sawx:expr xsi:type="sawx:sqlExpression">"- Invoice Line Details - General Information"."Line Number"</sawx:expr></saw:columnFormula></saw:column>
                        <saw:column xsi:type="saw:regularColumn" columnID="c704c7a0502976539">
                           <saw:columnFormula>
                              <sawx:expr xsi:type="sawx:sqlExpression">"- Invoice Line Details - General Information"."Description"</sawx:expr></saw:columnFormula></saw:column>
                        <saw:column xsi:type="saw:regularColumn" columnID="cbdbfc0b3d35e0d50">
                           <saw:columnFormula>
                              <sawx:expr xsi:type="sawx:sqlExpression">"- Invoice Line Amounts"."Line Amount"</sawx:expr></saw:columnFormula></saw:column>
                        <saw:column xsi:type="saw:regularColumn" columnID="c23cf591f0890ff0e">
                           <saw:columnFormula>
                              <sawx:expr xsi:type="sawx:sqlExpression">"- Invoice Amounts"."Total Tax Amount"</sawx:expr></saw:columnFormula></saw:column>
                        <saw:column xsi:type="saw:regularColumn" columnID="c6f2003c586093f6a">
                           <saw:columnFormula>
                              <sawx:expr xsi:type="sawx:sqlExpression">"Supplier"."Supplier Name"</sawx:expr></saw:columnFormula></saw:column>

    • Prasanna Reka

      Yes i could see data in my POD, remove filters on Project Name. We receive cost from AP Invoices based on our configuration, if you are receiving data from receipts you can modify the mapping based on your requirement. Apply filters to eliminate non project related data an blank records.

      When you use Union query ( Union, Union All, Intersect, Minus) make sure you have equal number of columns from both subject areas:

      1. To the extent possible map same columns if they exist in both subject areas Ex: Project Name

      2. Map the reference data as per your need Ex: Receipt # or Invoice # to Transaction Number etc

      3. Make sure you have data at the same level in both subject areas, distributions being lowest level is best. Ex: PO can have multiple lines fulfilled/received/invoiced at different times you may see many expenditures pointing to one PO.

    • Sameh Soliman

      Thanks Prasanna

       

      Transaction number in project is not the same as invoice number in AP

      could you please share screen shot of your sample data?

      in addition to that . do you know how to get the PO line desc for receipts coming from receipt costing?

    • Prasanna Reka

      Yes they are different, having them in my report together helps us to cross reference. I need to have the same number of fields in the report so i am mapping fields together which make sense. You can try to pull all Req/PO/Receipt References all pointing to EID as we need to have same number of columns we can try to map different columns on both sides that make sense or same column from projects to different columns from AP/Receipt subject areas.

      Some Examples - it can vary based on requirement-

      • Invoice Distribution # - Expenditure Item ID
      • Receipt ID - Expenditure Item ID
      • Invoice Line # - Expenditure Item ID
      • Invoice PO # - Expenditure item ID

      Uploaded screenshot, let me know if you see any issue.

       

       

       

    • Sameh Soliman

      I think You should have a column for expenditure ID and another column for invoice .

      by doing that you will have a single row for each expenditure ID

       

      right now on the screenshot you shared the grand and subtotal of your report will be doubled

       

    • Prasanna Reka

      I agree right now amounts will double for all records transferred to projects as data is pulled from two sides

      I am only creating this report as proof of concept for union query once i get all fields required focus on presentation, we need to be very creative when using this tool this has got lot of potential and unknowns.

    • Sameh Soliman
      I agree,
      
      You need to include the tax amount too on your report if you have tax
      
      Iam working on something I will share later when I tune it up
      
      
    • Sameh Soliman

      here is what i have done so far

       

      i am still unable to finish it so it should combine project details as well as receipt and PO line description since we do not use inventory items.

      in oracle project SA there is receipt distribution ID but in receipt accounting SA there is receipt receipt number and distribution number , PO number but there is no PO number.

      where is what iam trying to achieve for receipts

       

      Project Number || Receipt Number || Receipt Distribution|| Receipt Amount|| PO Number || PO Line Desc

      <saw:report xmlns:saw="com.siebel.analytics.web/report/v1.1" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlVersion="201201160" xmlns:sawx="com.siebel.analytics.web/expression/v1.1">
         <saw:criteria xsi:type="saw:derivedCriteria">
            <saw:columns>
               <saw:column xsi:type="saw:derivedColumn" columnID="c11cd2ccb8cb4cd80">
                  <saw:tableHeading>
                     <saw:caption>
                        <saw:text>Original Source References</saw:text></saw:caption></saw:tableHeading>
                  <saw:columnHeading>
                     <saw:caption>
                        <saw:text>Transaction Source</saw:text></saw:caption></saw:columnHeading></saw:column>
               <saw:column xsi:type="saw:derivedColumn" columnID="c4aed226651a09d3a">
                  <saw:tableHeading>
                     <saw:caption>
                        <saw:text>Original Source References</saw:text></saw:caption></saw:tableHeading>
                  <saw:columnHeading>
                     <saw:caption>
                        <saw:text>Document</saw:text></saw:caption></saw:columnHeading></saw:column>
               <saw:column xsi:type="saw:derivedColumn" columnID="cc942b16f415367a3">
                  <saw:tableHeading>
                     <saw:caption>
                        <saw:text>Project</saw:text></saw:caption></saw:tableHeading>
                  <saw:columnHeading>
                     <saw:caption>
                        <saw:text>Project Number</saw:text></saw:caption></saw:columnHeading></saw:column>
               <saw:column xsi:type="saw:derivedColumn" columnID="c02553aa50468075f">
                  <saw:tableHeading>
                     <saw:caption>
                        <saw:text>Project</saw:text></saw:caption></saw:tableHeading>
                  <saw:columnHeading>
                     <saw:caption>
                        <saw:text>Project Manager</saw:text></saw:caption></saw:columnHeading></saw:column>
               <saw:column xsi:type="saw:derivedColumn" columnID="cb205d54a33178fef">
                  <saw:tableHeading>
                     <saw:caption>
                        <saw:text>Project Type Attributes</saw:text></saw:caption></saw:tableHeading>
                  <saw:columnHeading>
                     <saw:caption>
                        <saw:text>Project Type</saw:text></saw:caption></saw:columnHeading></saw:column>
               <saw:column xsi:type="saw:derivedColumn" columnID="c868a4974542667c0">
                  <saw:tableHeading>
                     <saw:caption>
                        <saw:text>Project Classification</saw:text></saw:caption></saw:tableHeading>
                  <saw:columnHeading>
                     <saw:caption>
                        <saw:text>Class Category Description</saw:text></saw:caption></saw:columnHeading></saw:column>
               <saw:column xsi:type="saw:derivedColumn" columnID="c44a91afb39e984c9">
                  <saw:tableHeading>
                     <saw:caption>
                        <saw:text>Project Classification</saw:text></saw:caption></saw:tableHeading>
                  <saw:columnHeading>
                     <saw:caption>
                        <saw:text>Class Code Description</saw:text></saw:caption></saw:columnHeading></saw:column>
               <saw:column xsi:type="saw:derivedColumn" columnID="cdf8b99018ea233c5">
                  <saw:displayFormat>
                     <saw:formatSpec suppress="suppress" wrapText="true"/></saw:displayFormat>
                  <saw:tableHeading>
                     <saw:caption fmt="text">
                        <saw:text>Invoice</saw:text></saw:caption></saw:tableHeading>
                  <saw:columnHeading>
                     <saw:displayFormat>
                        <saw:formatSpec/></saw:displayFormat>
                     <saw:caption fmt="text">
                        <saw:text>Invoice Number</saw:text></saw:caption></saw:columnHeading></saw:column>
               <saw:column xsi:type="saw:derivedColumn" columnID="c78f0eb401b910d9c">
                  <saw:displayFormat>
                     <saw:formatSpec suppress="suppress" wrapText="true"/></saw:displayFormat>
                  <saw:tableHeading>
                     <saw:caption fmt="text">
                        <saw:text>Invoice</saw:text></saw:caption></saw:tableHeading>
                  <saw:columnHeading>
                     <saw:displayFormat>
                        <saw:formatSpec/></saw:displayFormat>
                     <saw:caption fmt="text">
                        <saw:text>Invoice Line</saw:text></saw:caption></saw:columnHeading></saw:column>
               <saw:column xsi:type="saw:derivedColumn" columnID="cad4dfa56ad170110">
                  <saw:tableHeading>
                     <saw:caption fmt="text">
                        <saw:text>Invoice</saw:text></saw:caption></saw:tableHeading>
                  <saw:columnHeading>
                     <saw:caption fmt="text">
                        <saw:text>Invoice Line Desc</saw:text></saw:caption></saw:columnHeading></saw:column>
               <saw:column xsi:type="saw:derivedColumn" columnID="cc32503c64b72a3cf">
                  <saw:displayFormat>
                     <saw:formatSpec suppress="repeat" wrapText="true"/></saw:displayFormat>
                  <saw:tableHeading>
                     <saw:caption fmt="text">
                        <saw:text>Invoice</saw:text></saw:caption></saw:tableHeading>
                  <saw:columnHeading>
                     <saw:displayFormat>
                        <saw:formatSpec/></saw:displayFormat>
                     <saw:caption fmt="text">
                        <saw:text>Line Amount</saw:text></saw:caption></saw:columnHeading></saw:column>
               <saw:column xsi:type="saw:derivedColumn" columnID="c0a7d87f5d337f0c3">
                  <saw:tableHeading>
                     <saw:caption fmt="text">
                        <saw:text>Invoice</saw:text></saw:caption></saw:tableHeading>
                  <saw:columnHeading>
                     <saw:caption fmt="text">
                        <saw:text>Total Tax</saw:text></saw:caption></saw:columnHeading></saw:column>
               <saw:column xsi:type="saw:derivedColumn" columnID="ce54385f4b0f79c68">
                  <saw:tableHeading>
                     <saw:caption>
                        <saw:text>Supplier</saw:text></saw:caption></saw:tableHeading>
                  <saw:columnHeading>
                     <saw:caption>
                        <saw:text>Supplier</saw:text></saw:caption></saw:columnHeading></saw:column>
               <saw:column columnID="cc37df6c61ff65da9" xsi:type="saw:derivedColumn">
                  <saw:displayFormat>
                     <saw:formatSpec suppress="suppress" wrapText="true"/></saw:displayFormat>
                  <saw:tableHeading>
                     <saw:caption fmt="text">
                        <saw:text>Receipt</saw:text></saw:caption></saw:tableHeading>
                  <saw:columnHeading>
                     <saw:caption fmt="text">
                        <saw:text>Receipt</saw:text></saw:caption>
                     <saw:displayFormat>
                        <saw:formatSpec/></saw:displayFormat></saw:columnHeading></saw:column>
               <saw:column columnID="cf304ae09f7ad134f" xsi:type="saw:derivedColumn">
                  <saw:displayFormat>
                     <saw:formatSpec suppress="suppress" wrapText="true"/></saw:displayFormat>
                  <saw:tableHeading>
                     <saw:caption fmt="text">
                        <saw:text>Original Source References</saw:text></saw:caption></saw:tableHeading>
                  <saw:columnHeading>
                     <saw:caption fmt="text">
                        <saw:text>PO Number</saw:text></saw:caption>
                     <saw:displayFormat>
                        <saw:formatSpec/></saw:displayFormat></saw:columnHeading></saw:column>
               <saw:column columnID="c57c027f2972e0124" xsi:type="saw:derivedColumn">
                  <saw:displayFormat>
                     <saw:formatSpec suppress="suppress" wrapText="true"/></saw:displayFormat>
                  <saw:tableHeading>
                     <saw:caption fmt="text">
                        <saw:text>Original Source References</saw:text></saw:caption></saw:tableHeading>
                  <saw:columnHeading>
                     <saw:caption fmt="text">
                        <saw:text>PO Line Number</saw:text></saw:caption>
                     <saw:displayFormat>
                        <saw:formatSpec/></saw:displayFormat></saw:columnHeading></saw:column>
               <saw:column columnID="c0af18b2d2cd5f797" xsi:type="saw:derivedColumn">
                  <saw:displayFormat>
                     <saw:formatSpec suppress="suppress" wrapText="true"/></saw:displayFormat>
                  <saw:tableHeading>
                     <saw:caption fmt="text">
                        <saw:text>Original Source References</saw:text></saw:caption></saw:tableHeading>
                  <saw:columnHeading>
                     <saw:caption fmt="text">
                        <saw:text>PO Line</saw:text></saw:caption>
                     <saw:displayFormat>
                        <saw:formatSpec/></saw:displayFormat></saw:columnHeading></saw:column>
               <saw:column columnID="c34246e18b595fdef" xsi:type="saw:derivedColumn">
                  <saw:displayFormat>
                     <saw:formatSpec suppress="suppress" wrapText="true"/></saw:displayFormat>
                  <saw:tableHeading>
                     <saw:caption fmt="text">
                        <saw:text>Receipt</saw:text></saw:caption></saw:tableHeading>
                  <saw:columnHeading>
                     <saw:caption fmt="text">
                        <saw:text>Receipt Distribution Amount</saw:text></saw:caption>
                     <saw:displayFormat>
                        <saw:formatSpec/></saw:displayFormat></saw:columnHeading></saw:column>
               <saw:column columnID="c5d7fd69e582214f5" xsi:type="saw:derivedColumn">
                  <saw:tableHeading>
                     <saw:caption>
                        <saw:text>Expenditure Item</saw:text></saw:caption></saw:tableHeading>
                  <saw:columnHeading>
                     <saw:caption>
                        <saw:text>Transaction Number</saw:text></saw:caption></saw:columnHeading></saw:column>
               <saw:column columnID="cab91af53a32d1421" xsi:type="saw:derivedColumn">
                  <saw:tableHeading>
                     <saw:caption>
                        <saw:text>Original Source References</saw:text></saw:caption></saw:tableHeading>
                  <saw:columnHeading>
                     <saw:caption>
                        <saw:text>Source Transaction Distribution Reference</saw:text></saw:caption></saw:columnHeading></saw:column></saw:columns>
            <saw:from>
               <saw:criteria xsi:type="saw:setCriteria" op="union">
                  <saw:criteria xsi:type="saw:simpleCriteria" subjectArea="&quot;Project Costing - Actual Costs Real Time&quot;">
                     <saw:columns>
                        <saw:column xsi:type="saw:regularColumn" columnID="c6267de0d515e6fc2">
                           <saw:columnFormula>
                              <sawx:expr xsi:type="sawx:sqlExpression">"Original Source References"."Transaction Source Name"</sawx:expr></saw:columnFormula></saw:column>
                        <saw:column xsi:type="saw:regularColumn" columnID="cb3acc1cb067e49bd">
                           <saw:columnFormula>
                              <sawx:expr xsi:type="sawx:sqlExpression">"Original Source References"."Document"</sawx:expr></saw:columnFormula></saw:column>
                        <saw:column xsi:type="saw:regularColumn" columnID="c3999388fe6971d24">
                           <saw:columnFormula>
                              <sawx:expr xsi:type="sawx:sqlExpression">"Project"."Project Number"</sawx:expr></saw:columnFormula></saw:column>
                        <saw:column xsi:type="saw:regularColumn" columnID="c8a6c558392255d3a">
                           <saw:columnFormula>
                              <sawx:expr xsi:type="sawx:sqlExpression">"Project"."Current Project Manager"</sawx:expr></saw:columnFormula></saw:column>
                        <saw:column xsi:type="saw:regularColumn" columnID="c257d5fb0bdd703c4">
                           <saw:columnFormula>
                              <sawx:expr xsi:type="sawx:sqlExpression">"- Project Type Attributes"."Project Type Name"</sawx:expr></saw:columnFormula></saw:column>
                        <saw:column xsi:type="saw:regularColumn" columnID="c836c6939064630fa">
                           <saw:columnFormula>
                              <sawx:expr xsi:type="sawx:sqlExpression">"- Project Classification"."Project Classification Category Description"</sawx:expr></saw:columnFormula></saw:column>
                        <saw:column xsi:type="saw:regularColumn" columnID="cf6030de645b93760">
                           <saw:columnFormula>
                              <sawx:expr xsi:type="sawx:sqlExpression">"- Project Classification"."Project Classification Description"</sawx:expr></saw:columnFormula></saw:column>
                        <saw:column xsi:type="saw:regularColumn" columnID="ce9c350402aed8e51">
                           <saw:columnFormula>
                              <sawx:expr xsi:type="sawx:sqlExpression">cast ( null as char)</sawx:expr></saw:columnFormula>
                           <saw:tableHeading>
                              <saw:caption fmt="text">
                                 <saw:text>Invoice</saw:text></saw:caption></saw:tableHeading>
                           <saw:columnHeading>
                              <saw:caption fmt="text">
                                 <saw:text>Invoice Number</saw:text></saw:caption></saw:columnHeading></saw:column>
                        <saw:column xsi:type="saw:regularColumn" columnID="c6fe7837671721c75">
                           <saw:columnFormula>
                              <sawx:expr xsi:type="sawx:sqlExpression">cast ( null as int)</sawx:expr></saw:columnFormula>
                           <saw:tableHeading>
                              <saw:caption fmt="text">
                                 <saw:text>Invoice</saw:text></saw:caption></saw:tableHeading>
                           <saw:columnHeading>
                              <saw:caption fmt="text">
                                 <saw:text>Invoice Line</saw:text></saw:caption></saw:columnHeading></saw:column>
                        <saw:column xsi:type="saw:regularColumn" columnID="c355763fa35d00b5a">
                           <saw:columnFormula>
                              <sawx:expr xsi:type="sawx:sqlExpression">cast ( null as char)</sawx:expr></saw:columnFormula>
                           <saw:tableHeading>
                              <saw:caption fmt="text">
                                 <saw:text>Invoice</saw:text></saw:caption></saw:tableHeading>
                           <saw:columnHeading>
                              <saw:caption fmt="text">
                                 <saw:text>Line Desc</saw:text></saw:caption></saw:columnHeading></saw:column>
                        <saw:column xsi:type="saw:regularColumn" columnID="c6ec4e21c04870e23" aggRule="sum">
                           <saw:columnFormula>
                              <sawx:expr xsi:type="sawx:sqlExpression">case when "Original Source References"."Transaction Source Name" = 'Oracle Fusion Payables' then "- Expenditure Item and Cost Distribution Measures"."Cost" else null end</sawx:expr></saw:columnFormula>
                           <saw:tableHeading>
                              <saw:caption fmt="text">
                                 <saw:text>Invoice</saw:text></saw:caption></saw:tableHeading>
                           <saw:columnHeading>
                              <saw:caption fmt="text">
                                 <saw:text>Line Amount</saw:text></saw:caption></saw:columnHeading></saw:column>
                        <saw:column xsi:type="saw:regularColumn" columnID="c0f61b4c237f7fa8b" aggRule="sum">
                           <saw:columnFormula>
                              <sawx:expr xsi:type="sawx:sqlExpression">Cast ( null as INT)</sawx:expr></saw:columnFormula>
                           <saw:tableHeading>
                              <saw:caption fmt="text">
                                 <saw:text>Invoice</saw:text></saw:caption></saw:tableHeading>
                           <saw:columnHeading>
                              <saw:caption fmt="text">
                                 <saw:text>Line Tax</saw:text></saw:caption></saw:columnHeading></saw:column>
                        <saw:column xsi:type="saw:regularColumn" columnID="c719b60c04c68b47f">
                           <saw:columnFormula>
                              <sawx:expr xsi:type="sawx:sqlExpression">"Supplier"."Supplier Name"</sawx:expr></saw:columnFormula>
                           <saw:tableHeading>
                              <saw:caption fmt="text">
                                 <saw:text>Invoice</saw:text></saw:caption></saw:tableHeading>
                           <saw:columnHeading>
                              <saw:caption fmt="text">
                                 <saw:text>Supplier</saw:text></saw:caption></saw:columnHeading></saw:column>
                        <saw:column xsi:type="saw:regularColumn" columnID="ceb1f856e8b1fffde">
                           <saw:columnFormula>
                              <sawx:expr xsi:type="sawx:sqlExpression">cast ( null as char)</sawx:expr></saw:columnFormula>
                           <saw:tableHeading>
                              <saw:caption fmt="text">
                                 <saw:text>Receipt</saw:text></saw:caption></saw:tableHeading>
                           <saw:columnHeading>
                              <saw:caption fmt="text">
                                 <saw:text>Receipt Number</saw:text></saw:caption></saw:columnHeading></saw:column>
                        <saw:column xsi:type="saw:regularColumn" columnID="cfb7f2d5e6627fcd2">
                           <saw:columnFormula>
                              <sawx:expr xsi:type="sawx:sqlExpression">cast ( null as char)</sawx:expr></saw:columnFormula>
                           <saw:tableHeading>
                              <saw:caption fmt="text">
                                 <saw:text>PO</saw:text></saw:caption></saw:tableHeading>
                           <saw:columnHeading>
                              <saw:caption fmt="text">
                                 <saw:text>PO Number</saw:text></saw:caption></saw:columnHeading></saw:column>
                        <saw:column xsi:type="saw:regularColumn" columnID="caeeeb35d8e5e5464">
                           <saw:columnFormula>
                              <sawx:expr xsi:type="sawx:sqlExpression">"Original Source References"."Expenditure Item Original Line Number"</sawx:expr></saw:columnFormula>
                           <saw:tableHeading>
                              <saw:caption fmt="text">
                                 <saw:text>PO</saw:text></saw:caption></saw:tableHeading>
                           <saw:columnHeading>
                              <saw:caption fmt="text">
                                 <saw:text>PO Line Number</saw:text></saw:caption></saw:columnHeading></saw:column>
                        <saw:column xsi:type="saw:regularColumn" columnID="c1f59b9612548851f">
                           <saw:columnFormula>
                              <sawx:expr xsi:type="sawx:sqlExpression">cast ( null as char)</sawx:expr></saw:columnFormula>
                           <saw:tableHeading>
                              <saw:caption fmt="text">
                                 <saw:text>PO</saw:text></saw:caption></saw:tableHeading>
                           <saw:columnHeading>
                              <saw:caption fmt="text">
                                 <saw:text>PO Description</saw:text></saw:caption></saw:columnHeading></saw:column>
                        <saw:column xsi:type="saw:regularColumn" columnID="c47fd9ef51dd75500">
                           <saw:columnFormula>
                              <sawx:expr xsi:type="sawx:sqlExpression">case when "Original Source References"."Transaction Source Name" = 'Oracle Fusion Cost Management' then "- Expenditure Item and Cost Distribution Measures"."Cost" else null end</sawx:expr></saw:columnFormula>
                           <saw:tableHeading>
                              <saw:caption fmt="text">
                                 <saw:text>PO</saw:text></saw:caption></saw:tableHeading>
                           <saw:columnHeading>
                              <saw:caption fmt="text">
                                 <saw:text>Line Amount</saw:text></saw:caption></saw:columnHeading></saw:column>
                        <saw:column xsi:type="saw:regularColumn" columnID="ca57e2c3867ca3810">
                           <saw:columnFormula>
                              <sawx:expr xsi:type="sawx:sqlExpression">"Expenditure Item"."Expenditure Item"</sawx:expr></saw:columnFormula></saw:column>
                        <saw:column xsi:type="saw:regularColumn" columnID="c6aa2b8d757398004">
                           <saw:columnFormula>
                              <sawx:expr xsi:type="sawx:sqlExpression">cast ( null as char)</sawx:expr></saw:columnFormula>
                           <saw:tableHeading>
                              <saw:caption fmt="text">
                                 <saw:text>PO</saw:text></saw:caption></saw:tableHeading>
                           <saw:columnHeading>
                              <saw:caption fmt="text">
                                 <saw:text>PO Distribution number</saw:text></saw:caption></saw:columnHeading></saw:column></saw:columns>
                     <saw:filter>
                        <sawx:expr xsi:type="sawx:logical" op="and">
                           <sawx:expr xsi:type="sawx:comparison" op="notEqual">
                              <sawx:expr xsi:type="sawx:sqlExpression">"Original Source References"."Document"</sawx:expr>
                              <sawx:expr xsi:type="xsd:string">Expense Report</sawx:expr></sawx:expr>
                           <sawx:expr xsi:type="sawx:list" op="in">
                              <sawx:expr xsi:type="sawx:sqlExpression">"Original Source References"."Transaction Source Name"</sawx:expr>
                              <sawx:expr xsi:type="xsd:string">Oracle Fusion Cost Management</sawx:expr>
                              <sawx:expr xsi:type="xsd:string">Oracle Fusion Payables</sawx:expr></sawx:expr></sawx:expr></saw:filter></saw:criteria>
                  <saw:criteria xsi:type="saw:simpleCriteria" subjectArea="&quot;Payables Invoices - Transactions Real Time&quot;">
                     <saw:columns>
                        <saw:column xsi:type="saw:regularColumn" columnID="ca01bd5f134ecbd27">
                           <saw:columnFormula>
                              <sawx:expr xsi:type="sawx:sqlExpression">'Oracle Fusion Payables'</sawx:expr></saw:columnFormula></saw:column>
                        <saw:column xsi:type="saw:regularColumn" columnID="c00fd4a87bd120317">
                           <saw:columnFormula>
                              <sawx:expr xsi:type="sawx:sqlExpression">'Supplier Invoice'</sawx:expr></saw:columnFormula></saw:column>
                        <saw:column xsi:type="saw:regularColumn" columnID="c092629e0453d684e">
                           <saw:columnFormula>
                              <sawx:expr xsi:type="sawx:sqlExpression">"- Invoice Line Details - Project Information"."Project Number"</sawx:expr></saw:columnFormula></saw:column>
                        <saw:column xsi:type="saw:regularColumn" columnID="ce4772a2397557b46">
                           <saw:columnFormula>
                              <sawx:expr xsi:type="sawx:sqlExpression">cast( null as char)</sawx:expr></saw:columnFormula>
                           <saw:tableHeading>
                              <saw:caption fmt="text">
                                 <saw:text>Project</saw:text></saw:caption></saw:tableHeading>
                           <saw:columnHeading>
                              <saw:caption fmt="text">
                                 <saw:text>Project Type</saw:text></saw:caption></saw:columnHeading></saw:column>
                        <saw:column xsi:type="saw:regularColumn" columnID="cbce48a040b7c05ba">
                           <saw:columnFormula>
                              <sawx:expr xsi:type="sawx:sqlExpression">cast( null as char)</sawx:expr></saw:columnFormula>
                           <saw:tableHeading>
                              <saw:caption fmt="text">
                                 <saw:text>Project</saw:text></saw:caption></saw:tableHeading>
                           <saw:columnHeading>
                              <saw:caption fmt="text">
                                 <saw:text>Project Category</saw:text></saw:caption></saw:columnHeading></saw:column>
                        <saw:column xsi:type="saw:regularColumn" columnID="cbc6f585a1eb6bcad">
                           <saw:columnFormula>
                              <sawx:expr xsi:type="sawx:sqlExpression">cast( null as char)</sawx:expr></saw:columnFormula>
                           <saw:tableHeading>
                              <saw:caption fmt="text">
                                 <saw:text>Project</saw:text></saw:caption></saw:tableHeading>
                           <saw:columnHeading>
                              <saw:caption fmt="text">
                                 <saw:text>Project Class</saw:text></saw:caption></saw:columnHeading></saw:column>
                        <saw:column xsi:type="saw:regularColumn" columnID="cc8a0b47688ce4acf">
                           <saw:columnFormula>
                              <sawx:expr xsi:type="sawx:sqlExpression">cast( null as char)</sawx:expr></saw:columnFormula>
                           <saw:tableHeading>