At last a JDE post!

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)


PO:


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.

Comments:

Is it honourable matching ? Already saw the unvarying info on this plot Watch

Posted by advedopomyday on April 01, 2009 at 12:59 PM MDT #

Android is flooded through a lot of carriers and open to all software for free. My thoughts are the iPhone have less potential the day Android is on all carriers it will be on top of the smartphones.

Posted by Rodger Shibley on September 16, 2010 at 09:20 PM MDT #

Post a Comment:
  • HTML Syntax: NOT allowed
About

Follow bipublisher on Twitter Find Us on Facebook BI Publisher Youtube ChannelDiscussion Forum

Join our BI Publisher community to get the most and keep updated with the latest news, How-to, Solutions! Share your feedback and let us hear your voice @bipublisher on Twitter, on our official Facebook page, and Youtube!

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today