Troubleshooting
Problem
Why did the Oracle cai error come up Key words: CAI110 item_dnld item not found?
Symptom
PART: Interface 6.0 SP1 DCS
COMPONENT: <None>
OS: <None> -
<None>
DATABASE: <None> - <None>
WEB SERVER: <None> - <None>
WEB
BROWSER: <None> - <None>
Cause
Resolving The Problem
1) Lookup interface_error record with the following sql query:
select
count(*) from interface_errors
where whse='&whse'
and create_time_stamp >
sysdate - 1
;
2) If you get an Oracle error something like "Cai 110 :
Item Master record not found in MTL_SYSTEM_ITEMS_WMS_V in Item Download. Item
Id = 56936 Whse
Id = 101", "item not found" or "record not found in
item master", look at the mod program id in the error . This is usually a CAI
error. The error is in getting the
data from oracle into the Yantra DCS
interface table
3) Since this is an interface error and the error is
logged by CAI instead of and intbu program, look into the wrapper directory,
look at SKU download wrapper script
and at oraint directory under log
such as skud.log or any other appropriate log to find errors.
Example: FGI27|0|0|100|110|0|0|ORA-01403: no data found|
|PR_SKUD_SEL_ITEM|0|Item Master
record not found in
MTL_SYSTEM_ITEMS_WMS_V in Item Download. Item Id = 56936
Whse Id = 101 SQL Code
:100|0|20010816050501|YANTRA|CSKU
Upon looking into
$BASE/oraint/wmsyantra_objects/wrapper/item_dnld.sql found the package
PR_SKUD_SEL_ITEM and
saw that the error was encountered
when trying to do the following sql query (the dblink is yanprod and the
APPUSER is apps.):
select count(*) from
apps.mtl_system_items_wms_v@yanprod
where
inventory_item_id =56952 and organization_id=101 and
rownum=1;
RAISE EXCEPTION
IF SQLCD <> 0 THEN
LCL_ERROR_CONTEXT := 'Unable to
Select From Item Master ' || 'For Notification Record.';
4) Make
sure the oracle driver table record and the data table are in sync. Look into
the script for further information or in the linked case for an
example.
In this case the following 2 queries were
run to find if records were there in the driving table and the data table for
the errenous records:
1. SQL> select count(*) from
apps.cai_notification_export@yanprod
2 where
system_id3=101 and event_type_id=1 and
system_id1=56952
3
/
COUNT(*)
----------
8
2.
SQL> select count(*) from
apps.mtl_system_items_wms_v@yanprod
2 where
inventory_item_id =56952 and organization_id=101 and
rownum=1;
COUNT(*)
----------
0
Historical Number
PRI48153
Product Synonym
[<p><b>]Fact[</b><p>];
Was this topic helpful?
Document Information
Modified date:
16 June 2018
UID
swg21533578