Blog dedicated to Oracle Applications (E-Business Suite) Technology; covers Apps Architecture, Administration and third party bolt-ons to Apps

Tuesday, February 12, 2008

ponUpgAucItemPrc.sql fails during 11.5.10.2 maintenance pack

While applying 11.5.10.2 maintenance pack 3480000, the worker running ponUpgAucItemPrc.sql failed with following error:

ORA-01407: cannot update
("APPLSYS"."FND_ATTACHED_DOCUMENTS"."CREATED_BY") to NULL
ORA-06512: at line 230

Oracle had originally provided a datafix patch 6490593, which used to fix the problem in previous iterations of the upgrade. However in the current iteration which is done on a latest clone of Production, the same error appears when the datafix patch 6490593 is applied. After a long SR, Oracle provided the following direction:

CAUSE DETERMINATION
===================
The cause of the issue is invalid / incorrect data in fnd_attached_documents wrt to auction lines and fnd_attached_documents wrt to bid lines.


0. Apply patch 6691493

1. Apply the following sql file:

-- Query to update fnd_attached_documents wrt to auction lines.
Update fnd_attached_documents fad
Set fad.created_by = -1
WHERE fad.entity_name = 'PON_AUCTION_ITEM_PRICES_ALL'
AND fad.pk1_value not in
(SELECT to_char(paha.auction_header_id)
FROM pon_auction_headers_all paha);

-- Query to update fnd_attached_documents wrt to bid lines.
Update fnd_attached_documents fad
Set fad.created_by = -1
WHERE fad.entity_name = 'PON_BID_ITEM_PRICES'
AND fad.pk2_value not in
(SELECT to_char(pbh.bid_number)
FROM pon_bid_headers pbh);

Commit;
exit;
.
2. Apply Patch 6490593.

No comments: