Friday, January 16, 2015

Wf_Engine_Util.Function_Call(PO_POAPPROVAL_INIT1.GET_PO_ATTRIBUTES, POAPPRV, 829745-306510, XXXXX, RUN)

When attempting to approve a revised blanket purchase agreement the documents gets stuck in process, because an exception occurs during the following activity:
PO Approval Top Process/Get PO Attributes

Further the following Errored Activities are shown:
Activity Result Error Name
--------------------------------------------- --------------- ------------------------------
Error Message
Error Stack
3103: Attribute 'DOCUMENT_ID' does not exist for item 'POAPPRV/829745-306510'.

PO_POAPPROVAL_INIT1.SetPOHdrAttributes(SetPOHdrAttributes: 02. Values= PA)
PO_POAPPROVAL_INIT1.Get_PO_Attributes(PO_POAPPROVAL_INIT1.Get_PO_Attributes: 01)
Wf_Engine.GetItemAttrNumber(POAPPRV, 829745-306510, DOCUMENT_ID)
Wf_Engine_Util.Function_Call(PO_POAPPROVAL_INIT1.GET_PO_ATTRIBUTES, POAPPRV, 829745-306510, 177419, RUN)

Failed Activity  Get PO Attributes
Activity Type  Function
Error Name  -6512
Error Message  ORA-06512: line 
Error Stack  Wf_Engine_Util.Function_Call(PO_POAPPROVAL_INIT1.GET_PO_ATTRIBUTES, POAPPRV, 49510-522507, 170247, RUN)
One or more releases are created against the blanket purchase agreement containing one or more distributions for which the ordered quantity is zero. This can be determined by executing the following query:
SELECT pha.segment1 "PO Nr"
, pra.release_num "Release Nr", pll.shipment_num "Shipment Nr"
, pda.distribution_num "Distribution Nr"
FROM po_headers_all pha
, po_lines_all pla
, po_distributions_all pda
, po_line_locations_all pll
, po_releases_all pra
WHERE pra.po_release_id = pll.po_release_id
AND pll.line_location_id = pda.line_location_id
AND pda.po_line_id = pla.po_line_id
AND pla.po_header_id = pha.po_header_id
AND pha.segment1 = '&PO_NUM';
To implement the solution, please correct or delete the distributions with quantity zero. In order to do so, please take the following steps:
First reset the document from 'in process' status to either incomplete or requires reapproval (either is fine) using the reset script as provided in poresrel.sql  
These scripts allow the user to resubmit the document for approval by resetting the authorization status when documents are stuck in 'In-Process' or 'Pre-Approved' statuses. The scripts are:

    poxrespo.sql - to reset Standard, Blanket, Planned and Contract purchase orders
    poresrel.sql - to reset Blanket and scheduled releases
    poresreq.sql - to reset Internal and purchase requisitions

In order to obtain the scripts apply the proper patch.  The scripts will be available in the $PO_TOP/sql directory.

Then proceed with these next steps:

1. Navigate to Purchase Orders > Releases
2. Query up the involved blanket purchase agreement and release
3. Select the involved shipment
4. Choose button Distributions
5. Delete the distribution with the quantity zero
6. Save the changes
7. Retry the workflow. In order to do so, the following steps can be performed:

- Determine the wf_item_type and wf_item_key values from the involved BPA by executing the following query:
select segment1, wf_item_type, wf_item_key, authorization_status, org_id
from po_headers_all
where segment1 = '&po_nr'
and org_id = &org_id;

- Use the returned item type and item key to retry the workflow processes, by running the following command connected as APPS user:
SQL> exec wf_engine.startprocess('&WF_ITEM_TYPE','&WF_ITEM_KEY');

Instructions to Run Script

The script prompts the user for the following parameters :

    Document number.  Enter the document number in question.
    Organization id.  The organization id that the document belongs to. (Important:   This prompt should never be entered with a null value as the organization id is never null at the table level and must be entered or else the query to find the document to reset will not find the document).   Use these scripts to identify the organization id:

        SQL > select, prh.segment1, prh.org_id
        from po_requisition_headers_all prh,
        hr_all_organization_units hr
        where prh.org_id = hr.organization_id and
        prh.segment1 = '&Enter_Req_Number';

        Purchase Order:
        SQL > select, poh.segment1, poh.org_id
        from po_headers_all poh,
        hr_all_organization_units hr
        where poh.org_id = hr.organization_id and
        poh.segment1 = '&Enter_PO_Number';

        Purchase Order Release:
        SQL > select, poh.segment1, por.release_num, por.org_id
        from po_headers_all poh,
        po_releases_all por,
        hr_all_organization_units hr
        where poh.org_id = hr.organization_id and
        por.org_id = poh.org_id and
        poh.po_header_id = por.po_header_id and
        poh.segment1 = '&Enter_PO_Number' and
        por.release_num = '&Enter_Release_Num';

    Do you want to delete the action history since the last approval?

The standard practice is to choose No (N).

With poxrespo.sql and poresrel.sql an option to delete or update the action history since the last submission of the document for approval.

On selecting update, if there is an action pending on a particular user, the scripts will update the action code as 'No Action Taken'.

If the action history does not contain a record with pending user action, the update action will have no effect on the action history.

On selecting the delete option, the action history for the unsuccessful approval cycle would be deleted.

    For example.: for Standard PO Number 5211
    Revision Sequence Action Employee
    0   1 Submit    A
    0   2 forward   A
    0   3 Approve   B
    0   4 Reserve   B
    1   5 Submit    A
    1   6 forward   A
    1   7

Here the document is stuck after 1 successful approval for Revision 0.

Hence if you choose the Delete action for action history, the sequence number 5,6,7 will be deleted and the document would be set to Requires reapproval.

If you choose the Update option for this case then the action at sequence 7 would be updated to 'No Action Taken' and the note field would indicate 'Updated by reset script on '.

Note: If the option to delete the Action History is taken and a commit is issued at the end of the script, there is no possibility to restore the deleted action history

No comments: