PACKAGE BODY: APPS.LNS_LOAN_COLLATERAL_PUB

Source

1 PACKAGE BODY LNS_LOAN_COLLATERAL_PUB AS
2 /* $Header: LNS_LNCOL_PUBP_B.pls 120.0 2005/05/31 18:32:56 appldev noship $ */
3 
4 G_PKG_NAME                          CONSTANT VARCHAR2(30) := 'LNS_LOAN_COLLATERALS_PUB';
5 
6 procedure Release_Collaterals(p_loan_id NUMBER) AS
7 CURSOR fee_assignment_cur (p_loan_id NUMBER) IS
8   SELECT ASSET_ASSIGNMENT_ID,
9          END_DATE_ACTIVE,
10       	 LAST_UPDATED_BY,
11      	 LAST_UPDATE_LOGIN ,
12     	 LAST_UPDATE_DATE ,
13      	 OBJECT_VERSION_NUMBER
14   FROM  LNS_ASSET_ASSIGNMENTS
15   WHERE LOAN_ID = p_loan_id
16   FOR UPDATE ;
17 
18   fee_assignment_rec    fee_assignment_cur%ROWTYPE ;
19 
20 BEGIN
21 
22     IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
23     	FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'Begin Release_Collaterals');
24     END IF;
25 
26     FOR fee_assignment_rec IN fee_assignment_cur(p_loan_id)
27     LOOP
28 
29        LNS_LOAN_HISTORY_PUB.log_record_pre(fee_assignment_rec.ASSET_ASSIGNMENT_ID,
30        					'ASSET_ASSIGNMENT_ID',
31        					'LNS_ASSET_ASSIGNMENTS');
32        UPDATE LNS_ASSET_ASSIGNMENTS
33        SET
34        END_DATE_ACTIVE = SYSDATE ,
35 	   LAST_UPDATED_BY = LNS_UTILITY_PUB.LAST_UPDATED_BY,
36 	   LAST_UPDATE_LOGIN = LNS_UTILITY_PUB.LAST_UPDATE_LOGIN,
37 	   LAST_UPDATE_DATE = LNS_UTILITY_PUB.LAST_UPDATE_DATE,
38 	   OBJECT_VERSION_NUMBER = fee_assignment_rec.OBJECT_VERSION_NUMBER + 1
39        WHERE current of fee_assignment_cur ;
40 
41 
42        LNS_LOAN_HISTORY_PUB.log_record_post(fee_assignment_rec.ASSET_ASSIGNMENT_ID,
43        					'ASSET_ASSIGNMENT_ID',
44        					'LNS_ASSET_ASSIGNMENTS',p_loan_id);
45 
46     END LOOP ;
47 
48     /*-- update collaterals
49 	UPDATE LNS_ASSET_ASSIGNMENTS
50 	SET END_DATE_ACTIVE = SYSDATE,
51 	LAST_UPDATED_BY = LNS_UTILITY_PUB.LAST_UPDATED_BY,
52 	LAST_UPDATE_LOGIN = LNS_UTILITY_PUB.LAST_UPDATE_LOGIN,
53 	LAST_UPDATE_DATE = LNS_UTILITY_PUB.LAST_UPDATE_DATE,
54 	OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1
55 	WHERE LOAN_ID = p_loan_id; */
56 
57 
58 	/* No need to end date Assets - Bug # 4212254
59      --update acquired assets
60 	UPDATE LNS_ASSETS
61 	SET END_DATE_ACTIVE = SYSDATE,
62 	LAST_UPDATED_BY = LNS_UTILITY_PUB.LAST_UPDATED_BY,
63 	LAST_UPDATE_LOGIN = LNS_UTILITY_PUB.LAST_UPDATE_LOGIN,
64 	LAST_UPDATE_DATE = LNS_UTILITY_PUB.LAST_UPDATE_DATE,
65 	OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1
66 	WHERE ACQUIRED_ASSET_LOAN_ID = p_loan_id; */
67 
68     IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
69     	FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'After call to Release_Collaterals');
70     END IF;
71 EXCEPTION
72 when others then
73         FND_MESSAGE.SET_NAME('LNS', 'LNS_RELEASE_COLLATERAL_ERROR');
74         FND_MSG_PUB.ADD;
75         RAISE FND_API.G_EXC_ERROR;
76 END RELEASE_COLLATERALS;
77 
78 
79 
80 
81 FUNCTION IS_EXIST_ASSET_ASSIGNMENT (
82     p_asset_id			 NUMBER
83 ) RETURN VARCHAR2 IS
84 
85   CURSOR C_Is_Exist_Assignment (X_Asset_Id NUMBER) IS
86   SELECT 'X'
87   FROM LNS_ASSET_ASSIGNMENTS
88   WHERE ASSET_ID = X_ASSET_ID
89   AND (END_DATE_ACTIVE IS NULL
90   OR TRUNC(END_DATE_ACTIVE) > TRUNC(SYSDATE));
91 
92   l_dummy VARCHAR2(1);
93 
94 BEGIN
95 
96   OPEN C_Is_Exist_Assignment (p_asset_id);
97   FETCH C_Is_Exist_Assignment INTO l_dummy;
98   IF C_Is_Exist_Assignment%FOUND THEN
99     CLOSE C_Is_Exist_Assignment;
100     RETURN 'Y';
101   END IF;
102   CLOSE C_Is_Exist_Assignment;
103   RETURN 'N';
104 
105 END IS_EXIST_ASSET_ASSIGNMENT;
106 
107 END LNS_LOAN_COLLATERAL_PUB;