My Oracle Support Banner

EAM9.2: Transaction Loader(AMIF1000) Process Fails at Step AMIF_FIN_PHY.310000.Step21 With Unique Constraint Error (Doc ID 2552676.1)

Last updated on OCTOBER 29, 2020

Applies to:

PeopleSoft Enterprise FIN Asset Management - Version 9.2 to 9.2 [Release 9]
Information in this document applies to any platform.

Symptoms

Issue:
-------

When attempting to interface PO receipt via AMIF1000, an error occurs.

ERROR
-----------------------
ORA-00001: unique constraint (EMDBO.PS_ASSET) violated
Failed SQL stmt: INSERT INTO PS_ASSET (BUSINESS_UNIT , ASSET_ID , ASSET_TYPE , ASSET_SUBTYPE , PARENT_ID , COMPONENT_OF_ID , ASSET_ASSIGNED_TO , TAGGABLE_SW , TAG_NUMBER , DESCR , DESCRSHORT , ASSET_STATUS , FINANCIAL_ASSET_SW , REPLACE_SW , REPLACE_ASSET_ID , CONVERSION_ID , NEW_ASSET_SW , AVAIL_SW , AVAIL_CONTACT , AVAIL_PHONE , ACQUISITION_CD , ACQUISITION_DT , FINANCING_CD , REPLACEMENT_COST , REPLACE_COST_DT , LEASE_ASSET_ID , PROFILE_ID , ASSET_CLASS , CAP_NUM , CAP_SEQUENCE , MFG_ID , MANUFACTURER , MODEL , PROD_VERSION , SERIAL_ID , PRODUCTION_DT , PLANT , MANUFACTUR_CONTACT , BUSINESS_UNIT_JV , INDEX_NAME , INDEX_DETAIL_NAME , LAST_INDEX_VALUE , COMPOSITE_SW , COMPOSITE_ID , FERC_CD , GROUP_ASSET_FLAG , CURRENCY_CD , RD_PLANT_ASSET , RD_START_DT , RD_USE_NBV , IP_ADDRESS , IP_ALIAS , COLLATERAL_ASSET , CLUSTERED_ASSET , LIFE , LAST_REVIEW_DT , AM_AST_UD_CHAR1 , AM_AST_UD_CHAR2 , AM_AST_UD_DATE1 , AM_AST_UD_NUM1 , AM_AST_UD_NUM2 , LAST_CALC_RPL_COST , HAZARDOUS_SW , SS_STATUS , VIN , ASSET_RESOURCE_SW , ALLOW_OVERBOOK_SW , CHARGE_BACK_SW , SCHEDULABLE_SW , EPL_TMPL_ID , REPAIRABLE_FLG , REPAIR_STATUS , BUSINESS_UNIT_WO , SHOP_ID , UNOWNED_FLG , WO_LOCATION_FLG , OFFLINE_FLG , ASSET_CRITICALITY , CREW_ID , AVAIL_FOR_USE , IT_UD_CHAR1 , IT_UD_CHAR2 , IT_UD_DATE1 , IN_SERVICE_DT , REGION_CD , SKU , CRM_IPROD_ID , CRM_IPROD_SETID , CRM_IPROD_STATUS , CRM_DEPT_SETID , DESCR_LONG , LINEAR_ASSET_SW , NBR_OF_CPU , CAP_THRSHLD_ID , IN_PHY_USE , SYNCID , SYNCDTTM , ARO_SW , ARO_ID , SES_LAST_DTTM) SELECT DISTINCT A.BUSINESS_UNIT , A.ASSET_ID , CASE WHEN (C.ARO_SW <> 'Y' OR A.ASSET_TYPE <> '200') THEN B.ASSET_TYPE ELSE A.ASSET_TYPE END , B.ASSET_SUBTYPE , A.PARENT_ID , A.COMPONENT_OF_ID , A.ASSET_ASSIGNED_TO , B.TAGGABLE_SW , A.TAG_NUMBER , A.DESCR , A.DESCRSHORT , A.ASSET_STATUS , CASE WHEN CAP_THR_ACTION = 'PHY' THEN 'N' ELSE B.FINANCIAL_ASSET_SW END , A.REPLACE_SW , A.REPLACE_ASSET_ID , A.CONVERSION_ID , A.NEW_ASSET_SW , A.AVAIL_SW , A.AVAIL_CONTACT , A.AVAIL_PHONE , B.ACQUISITION_CD , A.ACQUISITION_DT , A.FINANCING_CD , A.REPLACEMENT_COST , A.REPLACE_COST_DT , A.LEASE_ASSET_ID , A.PROFILE_ID , B.ASSET_CLASS , A.CAP_NUM , A.CAP_SEQUENCE , A.MFG_ID , A.MANUFACTURER , A.MODEL , A.PROD_VERSION , A.SERIAL_ID , A.PRODUCTION_DT , A.PLANT , A.MANUFACTUR_CONTACT , A.BUSINESS_UNIT_JV , B.INDEX_NAME , B.INDEX_DETAIL_NAME , A.LAST_INDEX_VALUE , A.COMPOSITE_SW , A.COMPOSITE_ID , A.FERC_CD , A.GROUP_ASSET_FLAG , A.CURRENCY_CD , B.RD_PLANT_ASSET , B.RD_START_DT , B.RD_USE_NBV , A.IP_ADDRESS , A.IP_ALIAS , ' ' , ' ' , 0 , NULL , A.AM_AST_UD_CHAR1 , A.AM_AST_UD_CHAR2 , A.AM_AST_UD_DATE1 , A.AM_AST_UD_NUM1 , A.AM_AST_UD_NUM2 , 0 , 'N' , ' ' , A.VIN , B.ASSET_RESOURCE_SW , A.ALLOW_OVERBOOK_SW , A.CHARGE_BACK_SW , A.SCHEDULABLE_SW , A.EPL_TMPL_ID , A.REPAIRABLE_FLG , A.REPAIR_STATUS , A.BUSINESS_UNIT_WO , A.SHOP_ID , A.UNOWNED_FLG , A.WO_LOCATION_FLG , A.OFFLINE_FLG , A.ASSET_CRITICALITY , A.CREW_ID , A.AVAIL_FOR_USE , A.IT_UD_CHAR1 , A.IT_UD_CHAR2 , A.IT_UD_DATE1 , A.IN_SERVICE_DT , A.REGION_CD , A.SKU , ' ' , ' ' , ' ' , ' ' , A.DESCR_LONG , A.LINEAR_ASSET_SW , A.NBR_OF_CPU , A.CAP_THRSHLD_ID , CASE WHEN A.IN_PHY_USE = 'Y' THEN 'Y' ELSE CASE WHEN B.ASSET_TYPE = '010' AND A.ASSET_STATUS = 'I' THEN 'Y' ELSE 'N' END END , 0 , NULL , CASE WHEN (C.ARO_SW = 'Y' AND A.ASSET_TYPE <> '200') THEN B.ARO_SW ELSE 'N' END , A.ARO_ID , CAST(SYSTIMESTAMP AS TIMESTAMP) FROM PS_AMIF_IPA_TMP4 A , PS_PROFILE_TBL B , PS_BUS_UNIT_TBL_AM C WHERE A.PROCESS_INSTANCE = 174945 AND A.INTFC_TYPE = :1 AND A.GROUP_ASSET_ID = :2 AND A.BUSINESS_UNIT = :3 AND C.BUSINESS_UNIT = :4 AND A.DEFAULT_PROFILE_SW <> 'N' AND A.INTFC_TYPE <> 'NAD' AND B.PROFILE_ID = A.PROFILE_ID AND B.SETID = :5 AND A.DESCR <> ' ' AND (A.PO_GROUP_ID = ' ' OR (A.PO_GROUP_ID <> ' ' AND A.PRIMARY_UNIT = 'Y'))

Process 174945 ABENDED at Step AMIF_FIN_PHY.310000.Step21 (SQL) -- RC = 805 (108,524)

 



STEPS
-----------------------
1. Confirm AM Business unit do not have ‘Allow duplicate tag numbers’ option checked
2. Create a PO with 1 line and 2 distribution lines.
  Both distribution lines are marked with US001 and FURNITURE asset profiles.
  - Line 1 to be capitalized. Ensure CAP# and Sequence is provided on Line 1.
  - Line 2 not capitalized.
3. Copy PO onto Receiver.
  a. Uncheck the ‘Interface Receipt’ checkbox.
  b. Update Allocation Type = Split Distributions on Receipt Distributions page.
  c. Click OK to exist the distribution page.
  d. Check the Serial checkbox then click into the ‘Pending’ link.
  e. Assign tag and Serial #’s.
  f. Click OK and receive message. Click ‘Yes’.
  g. Save and create the PO Receipt.
4. Run interface process to AM
  a. Process Receipts
  b. Retrieve Info from AP/PO
  c. Run Load Transactions into AM


Please see replication screenshots for more details.

Changes

 

Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Symptoms
Changes
Cause
Solution
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.