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