[Home] [Help]
PACKAGE BODY: APPS.FA_LEASE_PITEMS_PKG
Source
1 PACKAGE BODY FA_LEASE_PITEMS_PKG AS
2 /* $Header: FAXLPIEB.pls 120.2 2005/07/28 00:19:42 tkawamur noship $ */
3
4 /* This is a wrapper procedure for inserting a row into table
5 FA_LEASE_PAYMENT_ITEMS.*/
6 PROCEDURE Insert_Row(
7
8 X_Lease_ID IN NUMBER,
9 X_Payment_Schedule_ID IN NUMBER,
10 X_Schedule_Amort_Line_Num IN NUMBER,
11 X_Export_Status IN VARCHAR2,
12 X_Lessor_ID IN NUMBER,
13 X_Lessor_Site_ID IN NUMBER,
14 X_Dist_Code_Combination_ID IN NUMBER,
15 X_Invoice_Number IN VARCHAR2,
16 X_Invoice_ID IN NUMBER,
17 X_Invoice_Line_ID IN NUMBER,
18 X_Terms_ID IN NUMBER,
19 X_Last_Updated_by IN NUMBER,
20 X_Last_Update_Date IN DATE,
21 X_Last_Update_Login IN NUMBER,
22 X_Created_by IN NUMBER,
23 X_Creation_Date IN DATE,
24 X_Request_ID IN NUMBER,
25 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) IS
26
27 BEGIN
28
29 INSERT INTO FA_Lease_Payment_Items(
30
31 Lease_ID,
32 Payment_Schedule_ID,
33 Schedule_Amort_Line_Num,
34 Export_Status,
35 Lessor_ID,
36 Lessor_Site_ID,
37 Dist_Code_Combination_ID,
38 Invoice_Number,
39 Invoice_ID,
40 Invoice_Line_ID,
41 Terms_ID,
42 Last_Updated_by,
43 Last_Update_Date,
44 Last_Update_Login,
45 Created_by,
46 Creation_Date,
47 Request_ID)
48
49 VALUES (
50
51 X_Lease_ID,
52 X_Payment_Schedule_ID,
53 X_Schedule_Amort_Line_Num,
54 X_Export_Status,
55 X_Lessor_ID,
56 X_Lessor_Site_ID,
57 X_Dist_Code_Combination_ID,
58 X_Invoice_Number,
59 X_Invoice_ID,
60 X_Invoice_Line_ID,
61 X_Terms_ID,
62 X_Last_Updated_by,
63 X_Last_Update_Date,
64 X_Last_Update_Login,
65 X_Created_by,
66 X_Creation_Date,
67 X_Request_ID);
68
69 EXCEPTION
70
71 WHEN others THEN
72
73 FA_SRVR_MSG.ADD_MESSAGE(
74
75 CALLING_FN => 'FA_LEASE_PITEMS_PKG.Insert_Row'
76 ,p_log_level_rec => p_log_level_rec);
77
78 END Insert_Row;
79
80 /* This is a wrapper procedure for updating a row of table
81 FA_LEASE_PAYMENT_ITEMS.*/
82 PROCEDURE Update_Row(
83
84 X_Rowid IN VARCHAR2,
85 X_Lease_ID IN NUMBER,
86 X_Payment_Schedule_ID IN NUMBER,
87 X_Schedule_Amort_Line_Num IN NUMBER,
88 X_Export_Status IN VARCHAR2,
89 X_Lessor_ID IN NUMBER,
90 X_Lessor_Site_ID IN NUMBER,
91 X_Dist_Code_Combination_ID IN NUMBER,
92 X_Invoice_Number IN VARCHAR2,
93 X_Invoice_ID IN NUMBER,
94 X_Invoice_Line_ID IN NUMBER,
95 X_Terms_ID IN NUMBER,
96 X_Last_Updated_by IN NUMBER,
97 X_Last_Update_Date IN DATE,
98 X_Last_Update_Login IN NUMBER,
99 X_Created_by IN NUMBER,
100 X_Creation_Date IN DATE,
101 X_Request_ID IN NUMBER := NULL,
102 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) IS
103
104 BEGIN
105
106 UPDATE FA_Lease_Payment_Items SET
107
108 Lease_ID = X_Lease_ID,
109 Payment_Schedule_ID = X_Payment_Schedule_ID,
110 Schedule_Amort_Line_Num = X_Schedule_Amort_Line_Num,
111 Export_Status = X_Export_Status,
112 Lessor_ID = X_Lessor_ID,
113 Lessor_Site_ID = X_Lessor_Site_ID,
114 Dist_Code_Combination_ID= X_Dist_Code_Combination_ID,
115 Invoice_Number = X_Invoice_Number,
116 Invoice_ID = X_Invoice_ID,
117 Invoice_Line_ID = X_Invoice_Line_ID,
118 Terms_ID = X_Terms_ID,
119 Last_Updated_by = X_Last_Updated_by,
120 Last_Update_Date = X_Last_Update_Date,
121 Last_Update_Login = X_Last_Update_Login,
122 Created_by = X_Created_by,
123 Creation_Date = X_Creation_Date,
124 Request_ID = X_Request_ID
125
126 WHERE Rowid = X_Rowid;
127
128 EXCEPTION
129
130 WHEN others THEN
131
132 FA_SRVR_MSG.ADD_MESSAGE(
133
134 CALLING_FN => 'FA_LEASE_PITEMS_PKG.Update_Row'
135 ,p_log_level_rec => p_log_level_rec);
136
137 END Update_Row;
138
139 /* Procedure Lock_Rows() locks all rows in FA_LEASE_PAYMENT_ITEMS
140 associated with the specified lease.*/
141 PROCEDURE Lock_Rows(
142 X_Lease_ID IN NUMBER,
143 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) IS
144
145 CURSOR payitems IS
146
147 SELECT *
148 FROM FA_LEASE_PAYMENT_ITEMS
149 WHERE Lease_ID = X_Lease_ID
150 FOR UPDATE NOWAIT;
151
152 V_Rowdata payitems%ROWTYPE;
153
154 BEGIN
155
156 OPEN payitems;
157
158 FETCH payitems INTO V_Rowdata;
159
160 CLOSE payitems;
161
162 END Lock_Rows;
163
164 /* Procedure Delete_Rows() deletes all rows in FA_LEASE_PAYMENT_ITEMS
165 associated with the specified lease.*/
166 PROCEDURE Delete_Rows(
167
168 X_Lease_ID IN NUMBER,
169 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) IS
170
171 BEGIN
172
173
174 DELETE FROM FA_LEASE_PAYMENT_ITEMS
175 WHERE Lease_ID = X_Lease_ID;
176
177 END Delete_Rows;
178
179 /* Procedure Payments_Itemize() will create payment items for a lease.
180 New rows will be inserted into table FA_LEASE_PAYMENT_ITEMS. It will
181 be called after a user associates a schedule with a lease. A user
182 can also sumbit a concurrent program to run it to create payment
183 items for existing leases. */
184 FUNCTION Payments_Itemize (
185
186 P_Lease_ID IN NUMBER,
187 P_Payment_Schedule_ID IN NUMBER,
188 P_Lessor_ID IN NUMBER,
189 P_Lessor_Site_ID IN NUMBER := NULL,
190 P_Dist_Code_Combination_ID IN NUMBER := NULL,
191 P_Terms_ID IN NUMBER,
192 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null)
193
194 RETURN BOOLEAN IS
195
196 V_Lessor_ID Number;
197 V_Lessor_Site_ID Number;
198 V_Dist_Code_Combination_ID Number;
199 V_Terms_ID Number;
200 V_User_ID Number;
201 V_Invoice_Number VARCHAR2(50);
202 V_Lease_Number VARCHAR2(15);
203
204 CURSOR payment_items IS
205
206 SELECT Amortization_Line_Num Amort_Line_Num
207 FROM FA_AMORT_SCHEDULES
208 WHERE Payment_Schedule_ID = P_Payment_Schedule_ID;
209
210 BEGIN
211
212 V_User_ID := TO_Number(FND_Profile.Value('USER_ID'));
213
214 IF (P_Lessor_Site_ID is NULL or P_Dist_Code_Combination_ID is NULL) THEN
215
216 /* get Lessor Site and Account related with the lease. */
217 SELECT Lessor_ID, Lessor_Site_ID,
218 Dist_Code_Combination_ID, Lease_Number
219 INTO V_Lessor_ID, V_Lessor_Site_ID,
220 V_Dist_Code_Combination_ID, V_Lease_Number
221 FROM FA_Leases
222 WHERE Lease_ID = P_Lease_ID;
223
224 IF (V_Lessor_Site_ID is NULL or V_Dist_Code_Combination_ID is NULL) THEN
225
226 -- lessor site and code combination id are required, the user will
227 -- be notified by a message window
228 RETURN FALSE;
229
230 END IF;
231
232 ELSE
233
234 SELECT Lease_Number INTO V_Lease_Number
235 FROM FA_Leases WHERE Lease_ID = P_Lease_ID;
236 V_Lessor_ID := P_Lessor_ID;
237 V_Lessor_Site_ID := P_Lessor_Site_ID;
238 V_Dist_Code_Combination_ID := P_Dist_Code_Combination_ID;
239
240 END IF;
241
242 V_Terms_ID := P_Terms_ID;
243
244 -- insert rows into FA_LEASE_PAYMENT_ITEMS table
245 FOR current_item IN payment_items LOOP
246
247 --create an informative invoice number
248 V_Invoice_Number := 'FA-'||V_Lease_Number||'-'||current_item.Amort_Line_Num;
249
250 FA_LEASE_PITEMS_PKG.Insert_Row(
251 X_Lease_ID => P_Lease_ID,
252 X_Payment_Schedule_ID => P_Payment_Schedule_ID,
253 X_Schedule_Amort_Line_Num => current_item.Amort_Line_Num,
254 X_Export_Status => 'NEW',
255 X_Lessor_ID => V_Lessor_ID,
256 X_Lessor_Site_ID => V_Lessor_Site_ID,
257 X_Dist_Code_Combination_ID => V_Dist_Code_Combination_ID,
258 X_Invoice_Number => V_Invoice_Number,
259 X_Invoice_ID => NULL,
260 X_Invoice_Line_ID => NULL,
261 X_Terms_ID => V_Terms_ID,
262 X_Last_Updated_by => V_User_ID,
263 X_Last_Update_Date => Sysdate,
264 X_Last_Update_Login => V_User_ID,
265 X_Created_by => V_User_ID,
266 X_Creation_Date => Sysdate,
267 X_Request_ID => NULL
268 ,p_log_level_rec => p_log_level_rec);
269
270 END LOOP;
271
272 RETURN TRUE;
273
274 EXCEPTION
275
276 WHEN others THEN
277
278 FA_SRVR_MSG.ADD_MESSAGE(
279
280 CALLING_FN => 'FA_LEASE_PITEMS_PKG.PAYMENTS_ITEMIZE'
281 ,p_log_level_rec => p_log_level_rec);
282
283 END Payments_Itemize;
284
285 END FA_LEASE_PITEMS_PKG;