AMIF1000 Performance At AMIF_FIN_PHY.310530.Step10 When Processing Unitized Assets After Consolidating the Asset (Doc ID 2279109.1)

Last updated on JULY 11, 2017

Applies to:

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

Symptoms

On : 9.1 version, Integration - We are in FSCM 9.1, bundle 29.

ACTUAL BEHAVIOR
---------------
AMIF1000 Performance at AMIF_FIN_PHY.310530.Step10 hen Processing Large Load of Unitized Assets that has been consolidated.
AMIF_FIN_PHY.310530.Step10) (SQL) takes 3 second for each row.
It takes 35 minutes to run AM Loader when processing one interface load from PC which has 481 unitized assets and 45 min for 640 unitized assets.

-- 14.32.04 ..........(AMIF_FIN_PHY.310530.Step10) (SQL)
INSERT INTO PS_AM_VATATT_TAO10 (PROCESS_INSTANCE, BUSINESS_UNIT, ASSET_ID,
TRANSACTIONID, VAT_SETID, VAT_USE_ID, BUSINESS_UNIT_VAT, CHARTFIELD_PR1_VAL,
CHARTFIELD_PR2_VAL, CREATION_DT, SYSTEM_SOURCE, BUSINESS_UNIT_AP, VOUCHER_ID,
TRAN_DESCR, YEAR_OF_INIT, VAT_NBR_OF_YEARS, ACTIVE_INACT_FLAG,
VAT_RECOVERY_PCT, VAT_CONSTRAINT_PCT, VAT_TAXATION_PCT, VAT_ADMISSION_PCT,
VAT_CONFIN_PCT, VAT_TAXFIN_PCT, VAT_ADMFIN_PCT, VAT_RCVRFIN_PCT, CURRENCY_CD,
VAT_INV_AMT, VAT_RCVRY_AMT, TAX_CD_VAT, CATEGORY) SELECT
MAX(A.PROCESS_INSTANCE), A.BUSINESS_UNIT, A.ASSET_ID, 1, T.VAT_SETID,
T.VAT_USE_ID, T.BUSINESS_UNIT_VAT, T.CHARTFIELD_PR1_VAL,
T.CHARTFIELD_PR2_VAL, A.AUX_ACCTNG_DT, MAX(T.SYSTEM_SOURCE),
T.BUSINESS_UNIT_AP, T.VOUCHER_ID, 'AP Voucher',
CAST(TO_CHAR(A.AUX_ACCTNG_DT,'YYYY') AS NUMBER(4,0)),
MAX(C.VAT_NBR_OF_YEARS), 'A', MAX(T.VAT_RECOVERY_PCT),
MAX(T.VAT_CONSTRAINT_PCT), MAX(T.VAT_TAXATION_PCT), MAX(T.VAT_ADMISSION_PCT),
0, 0, 0, 0, MAX(T.TXN_CURRENCY_CD), SUM(T.VAT_INV_AMT), SUM(T.VAT_RCVRY_AMT),
MAX(T.TAX_CD_VAT), MAX(P.CATEGORY) FROM PS_AMIF_IPA_TMP10 A,
PS_ASSET_CLASS_TBL C, PS_SET_CNTRL_REC D, PS_INTFC_PHY_A T,
PS_PROFILE_DET_TBL P WHERE A.PROCESS_INSTANCE = 6674171 AND A.INTFC_TYPE =
'FAD' AND P.SETID = 'SHARE' AND P.PROFILE_ID = A.PROFILE_ID AND P.BOOK =
'CORP' AND P.EFFDT = ( SELECT MAX(Z.EFFDT) FROM PS_PROFILE_DET_TBL Z WHERE
Z.SETID = P.SETID AND Z.PROFILE_ID = P.PROFILE_ID AND Z.BOOK = P.BOOK AND
Z.EFFDT <= A.IN_SERVICE_DT) AND A.GROUP_ASSET_ID = ' ' AND A.BUSINESS_UNIT =
'HOSPL' AND A.CAPITALIZATION_SW IN ('1', '2', '3', '4') AND C.SETID = D.SETID
AND C.ASSET_CLASS = A.ASSET_CLASS AND D.SETCNTRLVALUE = A.BUSINESS_UNIT AND
D.RECNAME = 'ASSET_CLASS_TBL' AND T.SYSTEM_SOURCE = 'PAP' AND T.VAT_INV_AMT
<> 0 AND T.VAT_RCVRY_AMT <> 0 AND T.VAT_RECOVERY_PCT <> 0 AND
(T.VAT_ADMISSION_PCT <> 0 AND T.VAT_CONSTRAINT_PCT <> 0 AND
T.VAT_TAXATION_PCT <> 0) AND C.EFFDT=(SELECT MAX(EFFDT) FROM
PS_ASSET_CLASS_TBL C2 WHERE C2.SETID=C.SETID AND C2.ASSET_CLASS=C.ASSET_CLASS
AND C2.EFFDT<=T.INVOICE_DT) AND C.EFF_STATUS = 'A' AND T.BUSINESS_UNIT =
'HOSPL' AND T.ORIG_INTFC_ID = A.INTFC_ID AND T.ORIG_LINE_NUM =
A.INTFC_LINE_NUM AND T.INTFC_STATUS = 'CON' AND T.LOAD_STATUS = 'CON' AND
A.ASSET_ID = '000000109586' GROUP BY A.BUSINESS_UNIT, A.ASSET_ID,
T.BUSINESS_UNIT_AP, T.VOUCHER_ID, T.VAT_SETID, T.VAT_USE_ID,
T.BUSINESS_UNIT_VAT, T.CHARTFIELD_PR1_VAL, T.CHARTFIELD_PR2_VAL,
A.AUX_ACCTNG_DT
/
-- Row(s) affected: 0

-- 14.32.07 ..........(AMIF_FIN_PHY.310530.Step12) (SQL)

EXPECTED BEHAVIOR
-----------------------
The process should complete faster as we are planning large expansion to use EZ Unitize

STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. Interface data from Project Costing
2. Consolidate these rows from PC
3. Then Unitize the row that was consolidated
4. Run AMIF1000

Changes

 

Cause

Sign In with your My Oracle Support account

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

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms