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