Database Software

Sorry - I have also posted a similar question to this in the PL/SQL form:
10332115
But wanted to ask here too because this is related to Workflow related too, so I'm not sure where the query best sits.

--------------------------------------

I am working on changing an existing customisation we have on our PO Approval Workflow.

When I send an old PO for approval, the WF is erroring.

I have added debug statements so I can see at which point the WF falls over.

As part of the workflow change, we set an attribute on the POAPPRV workflow called "X_CONTRACT_IN_PLACE"

When the PO is sent for approval again, we want to find the value of that attribute.

It is falling over here:

-- ####
  l_progress := '15 Got This Far';
  IF (l_po_wf_debug = 'Y') THEN
     PO_WF_DEBUG_PKG.insert_debug(p_item_type,p_item_key,l_progress);
  END IF;
-- ####

          -- get attribute value
          -- cannot use GetItemAttrText because if a PO 
          -- is sent for approval twice, it will generate 2 different item_keys
         SELECT DISTINCT wiav.text_value
                    INTO l_xccc_contract
                FROM apps.wf_item_attribute_values wiav
                   , apps.wf_item_attributes wia
                   , apps.wf_items wi
                   , po.po_headers_all pha
               WHERE wiav.item_type = wia.item_type
                 AND wi.item_type = wiav.item_type
                 AND wi.item_key = wiav.item_key
                 AND wiav.NAME = wia.NAME
                 AND wi.user_key = pha.segment1
                 AND wiav.text_value IS NOT NULL
                 AND wiav.item_type = 'POAPPRV'
                 AND wia.NAME = 'X_CONTRACT_IN_PLACE'
                 AND wi.user_key = TO_CHAR(l_this_doc_num);

-- ####
  l_progress := '16 Got This Far';
  IF (l_po_wf_debug = 'Y') THEN
     PO_WF_DEBUG_PKG.insert_debug(p_item_type,p_item_key,l_progress);
  END IF;
-- ####

I can tell it's getting stuck at this point because when I check the po_wf_debug table it shows the "15 Got This Far" statement, but not the next one.

If I run the SQL in TOAD, it e.g. returns NULL for one of the POs I'm testing, because it's an old PO and the Attribute was not set on it. However, when the same bit of code is reached in the workflow, it is breaking.

SELECT DISTINCT wiav.text_value
--                    INTO l_xccc_contract
                FROM apps.wf_item_attribute_values wiav
                   , apps.wf_item_attributes wia
                   , apps.wf_items wi
               WHERE wiav.item_type = wia.item_type
                 AND wi.item_type = wiav.item_type
                 AND wi.item_key = wiav.item_key
                 AND wiav.NAME = wia.NAME
                 AND wiav.text_value IS NOT NULL
                 AND wiav.item_type = 'POAPPRV'
                 AND wia.NAME = 'X_CONTRACT_IN_PLACE'
                 AND wi.user_key = '50085068';   

Any advice much appreciated, as I'm rather stuck!

Thanks