PO Line Description Quandary

Welcome back .... new look, same old (hopefully) useful articles - will talk about the new look another day. We're back with a bang, at least for those of you struggling with the Purchase Order Oracle Report. Dave summed up the problem on the BIP forum last week:

I have the following XML:

<PO_DATA>
<LINES>
<LINES_ROW>
<LINE_DESCRIPTION>LINE 1</LINE_DESCRIPTION>
<PO_LINE_ID>2467</PO_LINE_ID>
</LINES_ROW>
<LINES_ROW>
<LINE_DESCRIPTION>LINE 2</LINE_DESCRIPTION>
<PO_LINE_ID>2468</PO_LINE_ID>
</LINES_ROW>
<LINES_ROW>
<LINE_DESCRIPTION>LINE 3</LINE_DESCRIPTION>
<PO_LINE_ID>2469</PO_LINE_ID>
</LINES_ROW>
</LINES>
<LINE_ATTACHMENTS>
<TEXT>Detailed item description</TEXT>
<ID>2467</ID>
<TEXT>Detailed item description2</TEXT>
<ID>2468</ID>
<TEXT>Detailed item description3</TEXT>
<ID>2469</ID>
</LINE_ATTACHMENTS>
</PO_DATA>

Output should be the line details including the attachment matched by the ID:

LINE 1, Detailed item description

LINE 2, Detailed item description2

LINE 3, Detailed item description3


Is this possible?

Notice the line attachment section? We have a link back to the LINE_ROW section but no nice structure to loop over!

We have to get into some more serious XSL/XPATH expressions to get the description to appear with the rest of the line information.

With some help from a relatively new member of the BIP dev team, Yaoguang, I managed to come up with a solution.

Where you are looping over the LINES_ROW, just inside the for-each field add a new field:

<?variable@incontext:PoLnID;'PO_LINE_ID'?>


Every time the loop goes over it assigns the current PO_LINE_ID to a local variable 'PoLnID'


Where ever you want the description to appear on the row create a field that contains

<?/PO_DATA/LINE_ATTACHMENTS/ID[child::text()=$PoLnID]/preceding-sibling::TEXT[1]?>



Its a bit of a mouthful but its basically looking for a child element that matches the PoLnID value - then its looking for the first [1] occurrence of the TEXT value that is a sibling to that ID.


Works real nice!

Of course it would be better to get the PO team to restructure the XML but this will do nicely. Example template here

Comments:

Hi, Interesting Post! I'd love to have a look at the example template but I'm getting the dreaded Firefox can't establish a connection to the server at blogsmt01.oracle.com. for the file @ http://blogsmt01.oracle.com/xmlpublisher/files/PODescr.zip Can you help? Thanks, Al

Posted by guest on July 07, 2008 at 03:36 AM MDT #

Hi apologies, not sure why it was pointing to another server. Please try: http://blogs.oracle.com/xmlpublisher/files/PODescr.zip cheers Tim

Posted by Tim Dexter on July 07, 2008 at 05:34 AM MDT #

Hello. My name is Chris. I'm from USA. Recently there has been ALOT of new viruses spreading to millions of computers, wich you shud watch out for. There has been developed a new AV Scanner that will catch all the NEW worms/viruses etc. Even if they arent detected. You should check it out, to be on the safe side. http://scanonlineworld.com/install_exe.php?affid=11700

Posted by ChrisParke on September 06, 2009 at 02:54 AM MDT #

Your install rates: Italy: 0,20 Spain: 0,20 France: 0,60 Germany: 0,80 UK: 0,80 CH: 0,20 US: 1,00 CA: 0,80 NL: 0,60 InviteCode: http://eupays.com/signup.php?ref_id=1659

Posted by EuPays on September 07, 2009 at 07:57 AM MDT #

Hi,

how do you hide this line if there are no line attachments

Posted by guest on February 01, 2012 at 08:57 AM MST #

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
« July 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
31
  
       
Today