1 PACKAGE BODY LNS_AMORTIZATION_LINES_PKG AS
2 /* $Header: LNS_AMLNS_TBLH_B.pls 120.0 2005/05/31 18:39:07 appldev noship $ */
3
4 /* Insert_Row procedure */
5 PROCEDURE Insert_Row(
6 X_AMORTIZATION_LINE_ID IN OUT NOCOPY NUMBER
7 ,P_AMORTIZATION_SCHEDULE_ID IN NUMBER
8 ,P_LOAN_ID IN NUMBER
9 ,P_LINE_TYPE IN VARCHAR2
10 ,P_AMOUNT IN NUMBER
11 ,P_CUST_TRX_ID IN NUMBER
12 ,P_CUST_TRX_LINE_ID IN NUMBER
13 ,P_FEE_ID IN NUMBER
14 ,P_OBJECT_VERSION_NUMBER IN NUMBER
15 ,P_CREATION_DATE IN DATE
16 ,P_CREATED_BY IN NUMBER
17 ,P_LAST_UPDATE_DATE IN DATE
18 ,P_LAST_UPDATED_BY IN NUMBER
19 ,P_LAST_UPDATE_LOGIN IN NUMBER
20 ,P_FEE_SCHEDULE_ID IN NUMBER
21 ) IS
22 BEGIN
23 INSERT INTO LNS_AMORTIZATION_LINES
24 (
25 AMORTIZATION_LINE_ID
26 ,AMORTIZATION_SCHEDULE_ID
27 ,LOAN_ID
28 ,LINE_TYPE
29 ,AMOUNT
30 ,CUST_TRX_ID
31 ,CUST_TRX_LINE_ID
32 ,FEE_ID
33 ,OBJECT_VERSION_NUMBER
34 ,CREATION_DATE
35 ,CREATED_BY
36 ,LAST_UPDATE_DATE
37 ,LAST_UPDATED_BY
38 ,LAST_UPDATE_LOGIN
39 ,FEE_SCHEDULE_ID
40 ) VALUES (
41 DECODE(X_AMORTIZATION_LINE_ID, FND_API.G_MISS_NUM, LNS_AMORTIZATION_LINES_S.NEXTVAL, NULL, LNS_AMORTIZATION_LINES_S.NEXTVAL, X_AMORTIZATION_LINE_ID)
42 ,DECODE(P_AMORTIZATION_SCHEDULE_ID, FND_API.G_MISS_NUM, NULL, P_AMORTIZATION_SCHEDULE_ID)
43 ,DECODE(P_LOAN_ID, FND_API.G_MISS_NUM, NULL, P_LOAN_ID)
44 ,DECODE(P_LINE_TYPE, FND_API.G_MISS_CHAR, NULL, P_LINE_TYPE)
45 ,DECODE(P_AMOUNT, FND_API.G_MISS_NUM, NULL, P_AMOUNT)
46 ,DECODE(P_CUST_TRX_ID, FND_API.G_MISS_NUM, NULL, P_CUST_TRX_ID)
47 ,DECODE(P_CUST_TRX_LINE_ID, FND_API.G_MISS_NUM, NULL, P_CUST_TRX_LINE_ID)
48 ,DECODE(P_FEE_ID, FND_API.G_MISS_NUM, NULL, P_FEE_ID)
49 ,DECODE(P_OBJECT_VERSION_NUMBER, FND_API.G_MISS_NUM, NULL, P_OBJECT_VERSION_NUMBER)
50 ,LNS_UTILITY_PUB.CREATION_DATE
51 ,LNS_UTILITY_PUB.CREATED_BY
52 ,LNS_UTILITY_PUB.LAST_UPDATE_DATE
53 ,LNS_UTILITY_PUB.LAST_UPDATED_BY
54 ,LNS_UTILITY_PUB.LAST_UPDATE_LOGIN
55 ,DECODE(P_FEE_SCHEDULE_ID, FND_API.G_MISS_NUM, NULL, P_FEE_SCHEDULE_ID)
56 ) RETURNING
57 AMORTIZATION_LINE_ID
58 INTO
59 X_AMORTIZATION_LINE_ID;
60 END Insert_Row;
61
62 /* Update_Row procedure */
63 PROCEDURE Update_Row(
64 P_AMORTIZATION_LINE_ID IN NUMBER
65 ,P_AMORTIZATION_SCHEDULE_ID IN NUMBER
66 ,P_LOAN_ID IN NUMBER
67 ,P_LINE_TYPE IN VARCHAR2
68 ,P_AMOUNT IN NUMBER
69 ,P_CUST_TRX_ID IN NUMBER
70 ,P_CUST_TRX_LINE_ID IN NUMBER
71 ,P_FEE_ID IN NUMBER
72 ,P_OBJECT_VERSION_NUMBER IN NUMBER
73 ,P_LAST_UPDATE_DATE IN DATE
74 ,P_LAST_UPDATED_BY IN NUMBER
75 ,P_LAST_UPDATE_LOGIN IN NUMBER
76 ,P_FEE_SCHEDULE_ID IN NUMBER
77 ) IS
78 BEGIN
79 UPDATE LNS_AMORTIZATION_LINES SET
80 AMORTIZATION_SCHEDULE_ID = DECODE(P_AMORTIZATION_SCHEDULE_ID, NULL, AMORTIZATION_SCHEDULE_ID, FND_API.G_MISS_NUM, NULL, P_AMORTIZATION_SCHEDULE_ID)
81 ,LOAN_ID = DECODE(P_LOAN_ID, NULL, LOAN_ID, FND_API.G_MISS_NUM, NULL, P_LOAN_ID)
82 ,LINE_TYPE = DECODE(P_LINE_TYPE, NULL, LINE_TYPE, FND_API.G_MISS_CHAR, NULL, P_LINE_TYPE)
83 ,AMOUNT = DECODE(P_AMOUNT, NULL, AMOUNT, FND_API.G_MISS_NUM, NULL, P_AMOUNT)
84 ,CUST_TRX_ID = DECODE(P_CUST_TRX_ID, NULL, CUST_TRX_ID, FND_API.G_MISS_NUM, NULL, P_CUST_TRX_ID)
85 ,CUST_TRX_LINE_ID = DECODE(P_CUST_TRX_LINE_ID, NULL, CUST_TRX_LINE_ID, FND_API.G_MISS_NUM, NULL, P_CUST_TRX_LINE_ID)
86 ,FEE_ID = DECODE(P_FEE_ID, NULL, FEE_ID, FND_API.G_MISS_NUM, NULL, P_FEE_ID)
87 ,OBJECT_VERSION_NUMBER = DECODE(P_OBJECT_VERSION_NUMBER, NULL, OBJECT_VERSION_NUMBER, FND_API.G_MISS_NUM, NULL, P_OBJECT_VERSION_NUMBER)
88 ,LAST_UPDATE_DATE = LNS_UTILITY_PUB.LAST_UPDATE_DATE
89 ,LAST_UPDATED_BY = LNS_UTILITY_PUB.LAST_UPDATED_BY
90 ,LAST_UPDATE_LOGIN = LNS_UTILITY_PUB.LAST_UPDATE_LOGIN
91 ,FEE_SCHEDULE_ID = DECODE(P_FEE_SCHEDULE_ID, NULL, FEE_SCHEDULE_ID, FND_API.G_MISS_NUM, NULL, P_FEE_SCHEDULE_ID)
92 WHERE AMORTIZATION_LINE_ID = P_AMORTIZATION_LINE_ID;
93
94 if (sql%notfound) then
95 raise no_data_found;
96 end if;
97 END Update_Row;
98
99 /* Delete_Row procedure */
100 PROCEDURE Delete_Row(P_AMORTIZATION_LINE_ID IN NUMBER) IS
101 BEGIN
102 DELETE FROM LNS_AMORTIZATION_LINES
103 WHERE AMORTIZATION_LINE_ID = P_AMORTIZATION_LINE_ID;
104
105 if (sql%notfound) then
106 raise no_data_found;
107 end if;
108 END Delete_Row;
109
110 /* Lock_Row procedure */
111 PROCEDURE Lock_Row(
112 P_AMORTIZATION_LINE_ID IN NUMBER
113 ,P_AMORTIZATION_SCHEDULE_ID IN NUMBER
114 ,P_LOAN_ID IN NUMBER
115 ,P_LINE_TYPE IN VARCHAR2
116 ,P_AMOUNT IN NUMBER
117 ,P_CUST_TRX_ID IN NUMBER
118 ,P_CUST_TRX_LINE_ID IN NUMBER
119 ,P_FEE_ID IN NUMBER
120 ,P_OBJECT_VERSION_NUMBER IN NUMBER
121 ,P_CREATION_DATE IN DATE
122 ,P_CREATED_BY IN NUMBER
123 ,P_LAST_UPDATE_DATE IN DATE
124 ,P_LAST_UPDATED_BY IN NUMBER
125 ,P_LAST_UPDATE_LOGIN IN NUMBER
126 ,P_FEE_SCHEDULE_ID IN NUMBER
127 ) IS
128 CURSOR C IS SELECT * FROM LNS_AMORTIZATION_LINES
129 WHERE AMORTIZATION_LINE_ID = P_AMORTIZATION_LINE_ID
130 FOR UPDATE of AMORTIZATION_LINE_ID NOWAIT;
131 Recinfo C%ROWTYPE;
132 BEGIN
133 OPEN C;
134 FETCH C INTO Recinfo;
135 IF (C%NOTFOUND) THEN
136 CLOSE C;
137 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
138 APP_EXCEPTION.Raise_Exception;
139 END IF;
140 CLOSE C;
141
142 IF (
143 (Recinfo.AMORTIZATION_LINE_ID = P_AMORTIZATION_LINE_ID)
144 AND ( (Recinfo.AMORTIZATION_SCHEDULE_ID = P_AMORTIZATION_SCHEDULE_ID)
145 OR ( (Recinfo.AMORTIZATION_SCHEDULE_ID IS NULL)
146 AND (P_AMORTIZATION_SCHEDULE_ID IS NULL)))
147 AND ( (Recinfo.LOAN_ID = P_LOAN_ID)
148 OR ( (Recinfo.LOAN_ID IS NULL)
149 AND (P_LOAN_ID IS NULL)))
150 AND ( (Recinfo.LINE_TYPE = P_LINE_TYPE)
151 OR ( (Recinfo.LINE_TYPE IS NULL)
152 AND (P_LINE_TYPE IS NULL)))
153 AND ( (Recinfo.AMOUNT = P_AMOUNT)
154 OR ( (Recinfo.AMOUNT IS NULL)
155 AND (P_AMOUNT IS NULL)))
156 AND ( (Recinfo.CUST_TRX_ID = P_CUST_TRX_ID)
157 OR ( (Recinfo.CUST_TRX_ID IS NULL)
158 AND (P_CUST_TRX_ID IS NULL)))
159 AND ( (Recinfo.CUST_TRX_LINE_ID = P_CUST_TRX_LINE_ID)
160 OR ( (Recinfo.CUST_TRX_LINE_ID IS NULL)
161 AND (P_CUST_TRX_LINE_ID IS NULL)))
162 AND ( (Recinfo.FEE_ID = P_FEE_ID)
163 OR ( (Recinfo.FEE_ID IS NULL)
164 AND (P_FEE_ID IS NULL)))
165 AND ( (Recinfo.OBJECT_VERSION_NUMBER = P_OBJECT_VERSION_NUMBER)
166 OR ( (Recinfo.OBJECT_VERSION_NUMBER IS NULL)
167 AND (P_OBJECT_VERSION_NUMBER IS NULL)))
168 AND ( (Recinfo.CREATION_DATE = P_CREATION_DATE)
169 OR ( (Recinfo.CREATION_DATE IS NULL)
170 AND (P_CREATION_DATE IS NULL)))
171 AND ( (Recinfo.CREATED_BY = P_CREATED_BY)
172 OR ( (Recinfo.CREATED_BY IS NULL)
173 AND (P_CREATED_BY IS NULL)))
174 AND ( (Recinfo.LAST_UPDATE_DATE = P_LAST_UPDATE_DATE)
175 OR ( (Recinfo.LAST_UPDATE_DATE IS NULL)
176 AND (P_LAST_UPDATE_DATE IS NULL)))
177 AND ( (Recinfo.LAST_UPDATED_BY = P_LAST_UPDATED_BY)
178 OR ( (Recinfo.LAST_UPDATED_BY IS NULL)
179 AND (P_LAST_UPDATED_BY IS NULL)))
180 AND ( (Recinfo.LAST_UPDATE_LOGIN = P_LAST_UPDATE_LOGIN)
181 OR ( (Recinfo.LAST_UPDATE_LOGIN IS NULL)
182 AND (P_LAST_UPDATE_LOGIN IS NULL)))
183 AND ( (Recinfo.FEE_SCHEDULE_ID = P_FEE_SCHEDULE_ID)
184 OR ( (Recinfo.FEE_SCHEDULE_ID IS NULL)
185 AND (P_FEE_SCHEDULE_ID IS NULL)))
186 ) THEN
187 return;
188 ELSE
189 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
190 APP_EXCEPTION.Raise_Exception;
191 END IF;
192 END Lock_Row;
193 END;
194