Oracle BI Publisher Blog

Finally I got a JDE related question yesterday, so I have some content for you JDE junkies out there. Its actually a pretty generic question so if your not a JDE-phile read on its an interesting problem.

Shafeek Abooubaker from a well know consulting firm contacted me with an issue, he had built a PO template for his client but was stuck right at the last hurdle, getting the tax and final total for the purchase order onto the template. I thought he was 'pulling my chain' at first - sorry an English expression. He had managed the tough stuff with the PO lines and I thought the totals were a cinch. Looking at the JDE XML revealed his problem.
Heres a snippet:

<R43500_UTIL0003>
 <Properties>
 </Properties>
 <PageHeaders>
 </PageHeaders>
 <Grand_Total_S50>
  <Variable_000001_ID1/>
  <Payment_Terms_Desc_ID5>NET 7</Payment_Terms_Desc_ID5>
  <Order_Total_Tax_Rate_ID7/>
  <Tax_Order_Amount_ID9>.00</Tax_Order_Amount_ID9>
  <Order_Total_ID10>1300.00</Order_Total_ID10>
 </Grand_Total_S50>
 <Grand_Total_S50>
 </Grand_Total_S50>
 <Grand_Total_S50>
 </Grand_Total_S50>
 <Grand_Total_S50>
 </Grand_Total_S50>
 <Grand_Total_S50>
 </Grand_Total_S50>
 <Grand_Total_S50>
 </Grand_Total_S50>
 <Grand_Total_S50>
 </Grand_Total_S50>
 <Purchase_Order_Print_S42>
  <On_Ship_To_S45>
   <On_Ship_To_S48>
    <On_Line_Number_S47>
    </On_Line_Number_S47>
    </On_Line_Number_S47>
    <On_Line_Number_S47>
    </On_Line_Number_S47>
    <Order_Extended_Price_ID13>1300.00</Order_Extended_Price_ID13>
   </On_Ship_To_S48>

I have cut out alot of the data I just wanted to get across the structure. Notice there a series of Grand_Total_S50 nodes, one for each PO in the batch. The main loops for the PO are around On_Ship_To_S45 (header - red rectangle) and On_Line_Number_S47 (lines - green rectangle)

The SUBTOTAL value is in the Order_Extended_Price_ID13 element in the header loop, but the other two requied values are in the Grand_Total_S50 node. The problem is that there is not apparent connection between the On_Ship_To_S45 and the Grand_Total_S50 nodes. The grand totals do not store the relevant PO ID. The only way I could see to link the two was in the record numbers; there are 7 grand total nodes and that matches the number of purchase orders. So we can link on that using some publisher features and some XPATH.

To get to the Tax_Order_Amount_ID9 and Order_Total_ID10 values from the On_Ship_To_S45 (header) loop we use:

../../Grand_Total_S50/Tax_Order_Amount_ID9 or Order_Total_ID10

now we need to introduce the record component i.e. we grab the tax/total value in the relevant Grand_Total_S50 node based on the current iteration of the On_Ship_To_S45 (header) loop.

We can use XPATH's function position() for this:

<?../../Grand_Total_S50[x]/Order_Total_ID10?>

where x is the record position we want i.e. PO #1->x=1, PO#2->x=2 and so on. To get that PO record number we need to use a publisher updatable variable to keep track of the number.

 <?xdoxslt:set_variable($_XDOCTX, 'x', position())?>

we assign the record number to a variable 'x'.
Now in our reference to the tax and total values we need to fetch the current value of 'x'.

<?../../Grand_Total_S50[xdoxslt:get_variable($_XDOCTX, 'x')]/Order_Total_ID10?>

So we can now fetch what looks like an unrelated value into our template, modified template and XML here.

Im not sure if the disconnect between totals and POs was by design in the data extract or something that JDE does for you when you ask for a batch of POs. Ideally you want totals with headers, failing that, totals with references to headers and failing that - what we have just gone through i.e. position based references.