Oracle Fixed Asset Details Query

SELECT
    A.ASSET_NUMBER,
    A.DESCRIPTION,
    A.ASSET_TYPE,
    D.SEGMENT1 ASSET_KEY,
    C.SEGMENT1 MAJOR_CATEGORY,
    C.SEGMENT2 MINOR_CATEGORY,
    B.DEPRN_METHOD_CODE,
    B.LIFE_IN_MONTHS / 12 LIFE,
    B.BOOK_TYPE_CODE,
    B.DATE_PLACED_IN_SERVICE,
    B.DEPRECIATE_FLAG,
    B.COST,
    H.UNITS_ASSIGNED UNITS,
    G.SEGMENT1 COMPANY,
    G.SEGMENT2 DEPARTMENT,
    G.SEGMENT3 ACCOUNT,
    L.SEGMENT1 COUNTRY,
    L.SEGMENT2 STATE,
    L.SEGMENT3 CITY,
    L.SEGMENT4 BUILDING
FROM
    FA_ADDITIONS A,
    FA_BOOKS_V B,
    FA_CATEGORIES C,
    FA_ASSET_KEYWORDS D,
    GL_CODE_COMBINATIONS G,
    FA_DISTRIBUTION_HISTORY H,
    FA_LOCATIONS L
WHERE
        A.ASSET_ID = B.ASSET_ID
    AND
        A.ASSET_ID = H.ASSET_ID
    AND
        A.ASSET_CATEGORY_ID = C.CATEGORY_ID
    AND
        H.CODE_COMBINATION_ID = G.CODE_COMBINATION_ID
    AND
        H.LOCATION_ID = L.LOCATION_ID
    AND
        A.ASSET_KEY_CCID = D.CODE_COMBINATION_ID
    AND
        B.BOOK_TYPE_CODE LIKE 'MWANI_CORP_BOOK'
    AND
        H.TRANSACTION_HEADER_ID_OUT IS NULL

Oracle Retired Fixed Asset Query

SELECT
    RET.ROWID RET_ROWID,
    RET.RETIREMENT_ID,
    RET.BOOK_TYPE_CODE,
    RET.ASSET_ID,
    RET.TRANSACTION_HEADER_ID_IN,
    RET.DATE_RETIRED,
    RET.DATE_EFFECTIVE,
    RET.COST_RETIRED,
    RET.STATUS,
    RET.LAST_UPDATE_DATE,
    RET.LAST_UPDATED_BY,
    RET.RETIREMENT_PRORATE_CONVENTION,
    RET.TRANSACTION_HEADER_ID_OUT,
    RET.UNITS,
    RET.COST_OF_REMOVAL,
    RET.NBV_RETIRED,
    RET.GAIN_LOSS_AMOUNT,
    RET.PROCEEDS_OF_SALE,
    RET.GAIN_LOSS_TYPE_CODE,
    RET.RETIREMENT_TYPE_CODE,
    RET.ITC_RECAPTURED,
    RET.ITC_RECAPTURE_ID,
    RET.REFERENCE_NUM,
    RET.SOLD_TO,
    RET.TRADE_IN_ASSET_ID,
    RET.STL_METHOD_CODE,
    RET.STL_LIFE_IN_MONTHS,
    RET.STL_DEPRN_AMOUNT,
    RET.CREATED_BY,
    RET.CREATION_DATE,
    RET.LAST_UPDATE_LOGIN,
    RET.ATTRIBUTE1 RET_ATTRIBUTE1,
    RET.ATTRIBUTE2 RET_ATTRIBUTE2,
    RET.ATTRIBUTE3 RET_ATTRIBUTE3,
    RET.ATTRIBUTE4 RET_ATTRIBUTE4,
    RET.ATTRIBUTE5 RET_ATTRIBUTE5,
    RET.ATTRIBUTE6 RET_ATTRIBUTE6,
    RET.ATTRIBUTE7 RET_ATTRIBUTE7,
    RET.ATTRIBUTE8 RET_ATTRIBUTE8,
    RET.ATTRIBUTE9 RET_ATTRIBUTE9,
    RET.ATTRIBUTE10 RET_ATTRIBUTE10,
    RET.ATTRIBUTE11 RET_ATTRIBUTE11,
    RET.ATTRIBUTE12 RET_ATTRIBUTE12,
    RET.ATTRIBUTE13 RET_ATTRIBUTE13,
    RET.ATTRIBUTE14 RET_ATTRIBUTE14,
    RET.ATTRIBUTE15 RET_ATTRIBUTE15,
    RET.ATTRIBUTE_CATEGORY_CODE RET_ATTRIBUTE_CATEGORY_CODE,
    RET.REVAL_RESERVE_RETIRED,
    RET.UNREVALUED_COST_RETIRED,
    AD.ASSET_NUMBER ASSET_NUMBER,
    BKS.COST COST,
    AH.UNITS CURRENT_UNITS,
    TRADE_IN.ASSET_NUMBER TRADE_IN_ASSET_NUMBER,
    ADT.DESCRIPTION TRADE_IN_ASSET_DESC,
    TH.TRANSACTION_NAME,
    TH.ATTRIBUTE1,
    TH.ATTRIBUTE2,
    TH.ATTRIBUTE3,
    TH.ATTRIBUTE4,
    TH.ATTRIBUTE5,
    TH.ATTRIBUTE6,
    TH.ATTRIBUTE7,
    TH.ATTRIBUTE8,
    TH.ATTRIBUTE9,
    TH.ATTRIBUTE10,
    TH.ATTRIBUTE11,
    TH.ATTRIBUTE12,
    TH.ATTRIBUTE13,
    TH.ATTRIBUTE14,
    TH.ATTRIBUTE15,
    TH.ATTRIBUTE_CATEGORY_CODE,
    BC.CURRENT_FISCAL_YEAR,
    BC.FISCAL_YEAR_NAME,
    FY.START_DATE FY_START_DATE,
    FY.END_DATE FY_END_DATE,
    TH.INVOICE_TRANSACTION_ID,
    BKS.GROUP_ASSET_ID GROUP_ASSET_ID,
    RET.RECOGNIZE_GAIN_LOSS RECOGNIZE_GAIN_LOSS,
    RET.RECAPTURE_RESERVE_FLAG RECAPTURE_RESERVE_FLAG,
    RET.LIMIT_PROCEEDS_FLAG LIMIT_PROCEEDS_FLAG,
    RET.TERMINAL_GAIN_LOSS TERMINAL_GAIN_LOSS,
    RET.REDUCTION_RATE REDUCTION_RATE,
    RET.EOFY_RESERVE EOFY_RESERVE,
    RET.RESERVE_RETIRED RESERVE_RETIRED,
    RET.RECAPTURE_AMOUNT RECAPTURE_AMOUNT,
    TH.DATE_EFFECTIVE TRANSACTION_DATE_EFFECTIVE,
    FC.SEGMENT1 CATEGORY,
    AD.ASSET_CATEGORY_ID ASSET_CATEGORY_ID
FROM
    FA_RETIREMENTS RET,
    FA_ADDITIONS_B AD,
    FA_CATEGORIES FC,
    FA_BOOKS BKS,
    FA_BOOK_CONTROLS BC,
    FA_FISCAL_YEAR FY,
    FA_ASSET_HISTORY AH,
    FA_ADDITIONS_B TRADE_IN,
    FA_ADDITIONS_TL ADT,
    FA_TRANSACTION_HEADERS TH
WHERE
        AD.ASSET_ID = RET.ASSET_ID
    AND
        AD.ASSET_CATEGORY_ID = FC.CATEGORY_ID
    AND
        BKS.BOOK_TYPE_CODE = RET.BOOK_TYPE_CODE
    AND
        BKS.ASSET_ID = RET.ASSET_ID
    AND
        BKS.TRANSACTION_HEADER_ID_OUT = RET.TRANSACTION_HEADER_ID_IN
    AND
        BKS.DATE_INEFFECTIVE > AH.DATE_EFFECTIVE
    AND
        BKS.DATE_INEFFECTIVE <= NVL(
            AH.DATE_INEFFECTIVE,
            SYSDATE
        )
    AND
        AH.ASSET_ID = RET.ASSET_ID
    AND
        TRADE_IN.ASSET_ID (+) = RET.TRADE_IN_ASSET_ID
    AND
        TH.TRANSACTION_HEADER_ID = RET.TRANSACTION_HEADER_ID_IN
    AND
        BC.BOOK_TYPE_CODE = RET.BOOK_TYPE_CODE
    AND
        RET.BOOK_TYPE_CODE LIKE 'MWANI_CORP_BOOK'
    AND
        FY.FISCAL_YEAR_NAME = BC.FISCAL_YEAR_NAME
    AND
        FY.FISCAL_YEAR = BC.CURRENT_FISCAL_YEAR
    AND
        ADT.ASSET_ID (+) = TRADE_IN.ASSET_ID
    AND
        ADT.LANGUAGE (+) = USERENV('LANG')

Web ADI Error: An error has occurred while running an API import. No return type specified in PL/SQL parameter list 222:MWANIFARET6_PLS attribute 3

Web ADI Error:

·         Out of he 1row(s) successfully uploaded to the staging tables, 1 row(s) failed validation.

·         An error has occurred while running an API import. No return type specified in PL/SQL parameter list 222:MWANIFARET6_PLS attribute 3

\

Please check you defined integrator definition by using this script.

Script:

select *from BNE_ATTRIBUTES where ATTRIBUTE_CODE like '%MWANIFARET6_XA1%';

If you check Attrubite1=’Function’ , In our package we have defined procedure not function.

Required to update the Attribute1='PROCEDURE' and Attribute4=NULL

Script:

update BNE_ATTRIBUTES

set attribute1 = 'PROCEDURE', attribute4 = null 

where application_id = 222 and attribute_code like 'MWANIFARET6_XA1';

Now restart the system and use Web ADI template again. Issue will be Resolved.