X

An Oracle blog about BI Publisher

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

Join the discussion

Comments ( 5 )
  • guest Monday, July 7, 2008
    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
  • Tim Dexter Monday, July 7, 2008
    Hi
    apologies, not sure why it was pointing to another server. Please try:
    http://blogs.oracle.com/xmlpublisher/files/PODescr.zip
    cheers
    Tim
  • ChrisParke Sunday, September 6, 2009
    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
  • EuPays Monday, September 7, 2009
    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
  • guest Wednesday, February 1, 2012

    Hi,

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


Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.