/**-------Call Asset Retirement API-------**/
CREATE OR REPLACE PROCEDURE API_CALL_ASSET_RETIRE
AS
API_ERROR EXCEPTION;
/*TEST ASSET INFO */
L_ASSET_ID NUMBER;
L_BOOK_TYPE_CODE VARCHAR2 (15) := 'MWANI_CORP_BOOK';
L_USER_ID NUMBER := 0;
L_COST_RETIRED NUMBER;
L_PROCEEDS_OF_SALE NUMBER := 0;
L_COST_OF_REMOVAL NUMBER := 0;
V_REQUEST_ID NUMBER;
-- USER_ID MUST PROPERLY BE SET TO RUN CALC GAIN/LOSS
/* DEFINE LOCAL RECORD TYPES */
L_TRANS_REC FA_API_TYPES.TRANS_REC_TYPE;
L_DIST_TRANS_REC FA_API_TYPES.TRANS_REC_TYPE;
L_ASSET_HDR_REC FA_API_TYPES.ASSET_HDR_REC_TYPE;
L_ASSET_RETIRE_REC FA_API_TYPES.ASSET_RETIRE_REC_TYPE;
L_ASSET_DIST_TBL FA_API_TYPES.ASSET_DIST_TBL_TYPE;
L_SUBCOMP_TBL FA_API_TYPES.SUBCOMP_TBL_TYPE;
L_INV_TBL FA_API_TYPES.INV_TBL_TYPE;
/* MISC INFO */
L_API_VERSION NUMBER := 1;
L_INIT_MSG_LIST VARCHAR2 (1) := FND_API.G_FALSE;
L_COMMIT VARCHAR2 (1) := FND_API.G_TRUE;
L_VALIDATION_LEVEL NUMBER := FND_API.G_VALID_LEVEL_FULL;
L_CALLING_FN VARCHAR2 (80) := 'RETIREMENT ASET WRAPPER';
L_RETURN_STATUS VARCHAR2 (1) := FND_API.G_FALSE;
L_MSG_COUNT NUMBER := 0;
L_MSG_DATA VARCHAR2 (512);
L_COUNT NUMBER;
L_REQUEST_ID NUMBER;
I NUMBER := 0;
TEMP_STR VARCHAR2 (512);
MESG_COUNT NUMBER;
P_SESSION_ID NUMBER;
P_REQUESTER_ID NUMBER;
L_DATE_RETIRED DATE;
CURSOR CUR_FA_ADDITION
IS
SELECT C.SEQ_ID,
A.ASSET_NUMBER,
A.ASSET_ID,
B.COST,
C.DATE_RETIRED,
C.PROCEEDS_OF_SALE,
C.COST_OF_REMOVAL,
C.REQUESTER_ID
FROM FA_ADDITIONS_B A, FA_BOOKS_V B, XX_ASSET_RET_STG_TBL C
WHERE A.ASSET_ID = B.ASSET_ID
AND A.ASSET_NUMBER = TO_CHAR(C.ASSET_NUMBER)
AND NVL (C.X_STATUS, 'N') IN ('N', 'E')
AND C.SEQ_ID IS NOT NULL
ORDER BY C.SEQ_ID;
BEGIN
FND_GLOBAL.APPS_INITIALIZE (USER_ID => fnd_profile.VALUE('USER_ID'),
RESP_ID => fnd_profile.VALUE('RESP_ID'),
RESP_APPL_ID => fnd_profile.VALUE('RESP_APPL_ID'));
-- insert into xx_temp_tbl2 --column(ASSET_NO,BOOK_TYPE,X_STATUS,X_MESSAGE,P_ERRBUF,P_RETCODE,RET_DATE)
--values('API CALL',NULL,NULL,NULL,NULL,NULL,SYSDATE);
--commit;
FOR I IN CUR_FA_ADDITION
LOOP
L_ASSET_ID := I.ASSET_ID;
L_COST_RETIRED := I.COST;
L_DATE_RETIRED := I.DATE_RETIRED;
DBMS_OUTPUT.DISABLE;
DBMS_OUTPUT.ENABLE (1000000);
DBMS_OUTPUT.PUT_LINE ('BEGIN');
FA_SRVR_MSG.INIT_SERVER_MESSAGE;
FA_DEBUG_PKG.SET_DEBUG_FLAG (DEBUG_FLAG => 'YES');
-- GET STANDARD WHO INFO
L_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
FND_PROFILE.GET ('LOGIN_ID', L_TRANS_REC.WHO_INFO.LAST_UPDATE_LOGIN);
FND_PROFILE.GET ('USER_ID', L_TRANS_REC.WHO_INFO.LAST_UPDATED_BY);
IF (L_TRANS_REC.WHO_INFO.LAST_UPDATED_BY IS NULL)
THEN
L_TRANS_REC.WHO_INFO.LAST_UPDATED_BY := -1;
END IF;
IF (L_TRANS_REC.WHO_INFO.LAST_UPDATE_LOGIN IS NULL)
THEN
L_TRANS_REC.WHO_INFO.LAST_UPDATE_LOGIN := -1;
END IF;
L_TRANS_REC.WHO_INFO.LAST_UPDATE_DATE := SYSDATE;
L_TRANS_REC.WHO_INFO.CREATION_DATE :=
L_TRANS_REC.WHO_INFO.LAST_UPDATE_DATE;
L_TRANS_REC.WHO_INFO.CREATED_BY :=
L_TRANS_REC.WHO_INFO.LAST_UPDATED_BY;
L_TRANS_REC.TRANSACTION_TYPE_CODE := NULL;
-- THIS WILL BE DETERMINED INSIDE API
L_TRANS_REC.TRANSACTION_DATE_ENTERED := NULL;
L_ASSET_HDR_REC.ASSET_ID := L_ASSET_ID;
L_ASSET_HDR_REC.BOOK_TYPE_CODE := L_BOOK_TYPE_CODE;
L_ASSET_HDR_REC.PERIOD_OF_ADDITION := NULL;
L_ASSET_RETIRE_REC.RETIREMENT_PRORATE_CONVENTION := NULL;
L_ASSET_RETIRE_REC.DATE_RETIRED := L_DATE_RETIRED; --NULL;
-- WILL BE CURRENT PERIOD BY DEFAULT
L_ASSET_RETIRE_REC.UNITS_RETIRED := NULL;
L_ASSET_RETIRE_REC.COST_RETIRED := L_COST_RETIRED;
L_ASSET_RETIRE_REC.PROCEEDS_OF_SALE := I.PROCEEDS_OF_SALE; -- L_PROCEEDS_OF_SALE;
L_ASSET_RETIRE_REC.COST_OF_REMOVAL := I.COST_OF_REMOVAL; -- L_COST_OF_REMOVAL;
L_ASSET_RETIRE_REC.RETIREMENT_TYPE_CODE := 'SALE';
L_ASSET_RETIRE_REC.TRADE_IN_ASSET_ID := NULL;
L_ASSET_RETIRE_REC.CALCULATE_GAIN_LOSS := FND_API.G_FALSE;
FND_PROFILE.PUT ('USER_ID', L_USER_ID);
L_ASSET_DIST_TBL.DELETE;
FA_RETIREMENT_PUB.DO_RETIREMENT (
P_API_VERSION => L_API_VERSION,
P_INIT_MSG_LIST => L_INIT_MSG_LIST,
P_COMMIT => L_COMMIT,
P_VALIDATION_LEVEL => L_VALIDATION_LEVEL,
P_CALLING_FN => L_CALLING_FN,
X_RETURN_STATUS => L_RETURN_STATUS,
X_MSG_COUNT => L_MSG_COUNT,
X_MSG_DATA => L_MSG_DATA,
PX_TRANS_REC => L_TRANS_REC,
PX_DIST_TRANS_REC => L_DIST_TRANS_REC,
PX_ASSET_HDR_REC => L_ASSET_HDR_REC,
PX_ASSET_RETIRE_REC => L_ASSET_RETIRE_REC,
P_ASSET_DIST_TBL => L_ASSET_DIST_TBL,
P_SUBCOMP_TBL => L_SUBCOMP_TBL,
P_INV_TBL => L_INV_TBL);
IF L_RETURN_STATUS = FND_API.G_FALSE
THEN
UPDATE XX_ASSET_RET_STG_TBL
SET X_STATUS = 'E', X_MESSAGE = L_MSG_DATA
WHERE ASSET_NUMBER = I.ASSET_NUMBER;
COMMIT;
RAISE API_ERROR;
END IF;
DBMS_OUTPUT.PUT_LINE (
'ASSET RETIREMENT DONE: RETIREMENT_ID: '
|| L_ASSET_RETIRE_REC.RETIREMENT_ID);
UPDATE XX_ASSET_RET_STG_TBL
SET X_STATUS = 'R', X_MESSAGE = 'ASSET RETIRED SUCCESSFULLY'
WHERE ASSET_NUMBER = I.ASSET_NUMBER;
COMMIT;
-- DUMP DEBUG MESSAGES WHEN RUN IN DEBUG MODE TO LOG FILE
IF (FA_DEBUG_PKG.PRINT_DEBUG)
THEN
FA_DEBUG_PKG.WRITE_DEBUG_LOG;
END IF;
FA_SRVR_MSG.ADD_MESSAGE (CALLING_FN => L_CALLING_FN,
NAME => 'FA_SHARED_END_SUCCESS',
TOKEN1 => 'PROGRAM',
VALUE1 => 'RETIREMENT_API');
MESG_COUNT := FND_MSG_PUB.COUNT_MSG;
IF (MESG_COUNT > 0)
THEN
TEMP_STR := FND_MSG_PUB.GET (FND_MSG_PUB.G_FIRST, FND_API.G_FALSE);
DBMS_OUTPUT.PUT_LINE ('DUMP: ' || TEMP_STR);
FOR I IN 1 .. (MESG_COUNT - 1)
LOOP
TEMP_STR :=
FND_MSG_PUB.GET (FND_MSG_PUB.G_NEXT, FND_API.G_FALSE);
DBMS_OUTPUT.PUT_LINE ('DUMP: ' || TEMP_STR);
END LOOP;
ELSE
DBMS_OUTPUT.PUT_LINE ('DUMP: NO MESSAGE !');
END IF;
END LOOP;
EXCEPTION
WHEN API_ERROR
THEN
ROLLBACK WORK;
FA_SRVR_MSG.ADD_MESSAGE (CALLING_FN => L_CALLING_FN,
NAME => 'FA_SHARED_PROGRAM_FAILED',
TOKEN1 => 'PROGRAM',
VALUE1 => L_CALLING_FN);
MESG_COUNT := FND_MSG_PUB.COUNT_MSG;
-- P_ERRBUF := SQLCODE || '-' || SQLERRM;
-- P_RETCODE := 2;
IF (MESG_COUNT > 0)
THEN
TEMP_STR := FND_MSG_PUB.GET (FND_MSG_PUB.G_FIRST, FND_API.G_FALSE);
DBMS_OUTPUT.PUT_LINE ('DUMP: ' || TEMP_STR);
FOR I IN 1 .. (MESG_COUNT - 1)
LOOP
TEMP_STR :=
FND_MSG_PUB.GET (FND_MSG_PUB.G_NEXT, FND_API.G_FALSE);
DBMS_OUTPUT.PUT_LINE ('DUMP: ' || TEMP_STR);
END LOOP;
ELSE
DBMS_OUTPUT.PUT_LINE ('DUMP: NO MESSAGE !');
END IF;
COMMIT;
BEGIN
FND_GLOBAL.APPS_INITIALIZE (USER_ID => fnd_profile.VALUE('USER_ID'),
RESP_ID => fnd_profile.VALUE('RESP_ID'),
RESP_APPL_ID => fnd_profile.VALUE('RESP_APPL_ID'));
V_REQUEST_ID :=
FND_REQUEST.SUBMIT_REQUEST ('OFA',
'FARET',
NULL,
NULL,
FALSE,
L_BOOK_TYPE_CODE);
COMMIT;
DBMS_OUTPUT.PUT_LINE (V_REQUEST_ID);
END;
END ;