[Home] [Help]
PACKAGE BODY: APPS.FA_LPITEMS_EXPT_PKG
Source
1 PACKAGE BODY FA_LPITEMS_EXPT_PKG AS
2 /* $Header: FAXLPTPB.pls 120.9 2005/08/17 00:45:05 yyoon noship $ */
3
4 /*This is a wrapper procedure for inserting rows into table
5 AP_INVOICE_LINES_INTERFACE.*/
6 PROCEDURE Invoice_Insert_Row(
7
8 X_Invoice_ID IN NUMBER,
9 X_Invoice_Num IN VARCHAR2,
10 X_Vendor_ID IN NUMBER,
11 X_Vendor_Site_ID IN NUMBER,
12 X_Invoice_Amount IN NUMBER,
13 X_Invoice_Currency_Code IN VARCHAR2,
14 X_Source IN VARCHAR2,
15 X_Terms_ID IN NUMBER,
16 X_Last_Updated_by IN NUMBER,
17 X_Last_Update_Date IN DATE,
18 X_Last_Update_Login IN NUMBER,
19 X_Created_by IN NUMBER,
20 X_Creation_Date IN DATE,
21 X_GL_Date IN DATE,
22 X_Invoice_Date IN DATE,
23 X_Org_ID IN NUMBER,
24 X_LE_ID IN NUMBER,
25 X_LE_Name IN VARCHAR2,
26 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) IS
27
28 BEGIN
29
30 INSERT INTO AP_Invoices_Interface(
31
32 Invoice_ID,
33 Invoice_Num,
34 Vendor_ID,
35 Vendor_Site_ID,
36 Invoice_Amount,
37 Invoice_Currency_Code,
38 Source,
39 Terms_ID,
40 Last_Updated_by,
41 Last_Update_Date,
42 Last_Update_Login,
43 Created_by,
44 Creation_Date,
45 GL_Date,
46 Invoice_Date,
47 Invoice_Received_Date,
48 Org_ID,
49 Legal_Entity_ID,
50 Legal_Entity_Name,
51 Goods_Received_Date
52 )
53
54 VALUES (
55
56 X_Invoice_ID,
57 X_Invoice_Num,
58 X_Vendor_ID,
59 X_Vendor_Site_ID,
60 X_Invoice_Amount,
61 X_Invoice_Currency_Code,
62 X_Source,
63 X_Terms_ID,
64 X_Last_Updated_by,
65 X_Last_Update_Date,
66 X_Last_Update_Login,
67 X_Created_by,
68 X_Creation_Date,
69 X_GL_Date,
70 X_Invoice_Date,
71 X_GL_Date, -- BUG# 2267117
72 X_Org_ID,
73 X_LE_ID,
74 X_LE_Name,
75 X_GL_Date -- BUG# 3900673
76 );
77
78
79 EXCEPTION
80
81 WHEN others THEN
82
83 FA_SRVR_MSG.ADD_MESSAGE(
84
85 CALLING_FN => 'FA_LPITEMS_EXPT_PKG.Invoice_Insert_Row'
86 ,p_log_level_rec => p_log_level_rec);
87
88 END Invoice_Insert_Row;
89
90 /*This is a wrapper procedure for inserting rows into table
91 AP_INVOICE_LINES_INTERFACE.*/
92 PROCEDURE Invoice_Line_Insert_Row(
93
94 X_Invoice_ID IN NUMBER,
95 X_Invoice_Line_ID IN NUMBER,
96 X_Line_Number IN NUMBER,
97 X_Line_Type_Lookup_Code IN VARCHAR2 := 'ITEM',
98 X_Amount IN NUMBER,
99 X_Dist_Code_Combination_ID IN NUMBER,
100 X_Last_Updated_by IN NUMBER,
101 X_Last_Update_Date IN DATE,
102 X_Last_Update_Login IN NUMBER,
103 X_Created_by IN NUMBER,
104 X_Creation_Date IN DATE,
105 X_Accounting_Date IN DATE,
106 X_Org_ID IN NUMBER,
107 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) IS
108
109 BEGIN
110
111 INSERT INTO AP_Invoice_Lines_Interface(
112
113 Invoice_ID,
114 Invoice_Line_ID,
115 Line_Number,
116 Line_Type_Lookup_Code,
117 Amount,
118 Dist_Code_Combination_ID,
119 Last_Updated_by,
120 Last_Update_Date,
121 Last_Update_Login,
122 Created_by,
123 Creation_Date,
124 Accounting_Date,
125 Org_ID)
126
127 VALUES (
128
129 X_Invoice_ID,
130 X_Invoice_Line_ID,
131 X_Line_Number,
132 X_Line_Type_Lookup_Code,
133 X_Amount,
134 X_Dist_Code_Combination_ID,
135 X_Last_Updated_by,
136 X_Last_Update_Date,
137 X_Last_Update_Login,
138 X_Created_by,
139 X_Creation_Date,
140 X_Accounting_Date,
141 X_Org_ID);
142
143 EXCEPTION
144
145 WHEN others THEN
146
147 FA_SRVR_MSG.ADD_MESSAGE(
148
149 CALLING_FN => 'FA_LPITEMS_EXPT_PKG.Invoice_Line_Insert_Row'
150 ,p_log_level_rec => p_log_level_rec);
151
152 END Invoice_Line_Insert_Row;
153
154 /* Procedure Payment_Items_to_AP pushes lease payment items in Fixed
155 Assets into Account Payables. For all rows with Export Status
156 'POST', it will create new rows in tables AP_INVOICES_INTERFACE and
157 AP_INVOICE_LINES_INTERFACE. It will be called when a user presses
158 the EXPORT button in the Export Lease Payments to Payables window
159 and run as a concurrent program.*/
160 PROCEDURE Payment_Items_to_AP(
161 errbuf out nocopy varchar2,
162 retcode out nocopy number,
163 argument1 IN VARCHAR2 := NULL,
164 argument2 IN VARCHAR2 := NULL,
165 argument3 IN VARCHAR2 := NULL,
166 argument4 IN VARCHAR2 := NULL,
167 argument5 IN VARCHAR2 := NULL,
168 argument6 IN VARCHAR2 := NULL,
169 argument7 IN VARCHAR2 := NULL,
170 argument8 IN VARCHAR2 := NULL,
171 argument9 IN VARCHAR2 := NULL,
172 argument10 IN VARCHAR2 := NULL,
173 argument11 IN VARCHAR2 := NULL,
174 argument12 IN VARCHAR2 := NULL,
175 argument13 IN VARCHAR2 := NULL,
176 argument14 IN VARCHAR2 := NULL,
177 argument15 IN VARCHAR2 := NULL,
178 argument16 IN VARCHAR2 := NULL,
179 argument17 IN VARCHAR2 := NULL,
180 argument18 IN VARCHAR2 := NULL,
181 argument19 IN VARCHAR2 := NULL,
182 argument20 IN VARCHAR2 := NULL,
183 argument21 IN VARCHAR2 := NULL,
184 argument22 IN VARCHAR2 := NULL,
185 argument23 IN VARCHAR2 := NULL,
186 argument24 IN VARCHAR2 := NULL,
187 argument25 IN VARCHAR2 := NULL,
188 argument26 IN VARCHAR2 := NULL,
189 argument27 IN VARCHAR2 := NULL,
190 argument28 IN VARCHAR2 := NULL,
191 argument29 IN VARCHAR2 := NULL,
192 argument30 IN VARCHAR2 := NULL,
193 argument31 IN VARCHAR2 := NULL,
194 argument32 IN VARCHAR2 := NULL,
195 argument33 IN VARCHAR2 := NULL,
196 argument34 IN VARCHAR2 := NULL,
197 argument35 IN VARCHAR2 := NULL,
198 argument36 IN VARCHAR2 := NULL,
199 argument37 IN VARCHAR2 := NULL,
200 argument38 IN VARCHAR2 := NULL,
201 argument39 IN VARCHAR2 := NULL,
202 argument40 IN VARCHAR2 := NULL,
203 argument41 IN VARCHAR2 := NULL,
204 argument42 IN VARCHAR2 := NULL,
205 argument43 IN VARCHAR2 := NULL,
206 argument44 IN VARCHAR2 := NULL,
207 argument45 IN VARCHAR2 := NULL,
208 argument46 IN VARCHAR2 := NULL,
209 argument47 IN VARCHAR2 := NULL,
210 argument48 IN VARCHAR2 := NULL,
211 argument49 IN VARCHAR2 := NULL,
212 argument50 IN VARCHAR2 := NULL,
213 argument51 IN VARCHAR2 := NULL,
214 argument52 IN VARCHAR2 := NULL,
215 argument53 IN VARCHAR2 := NULL,
216 argument54 IN VARCHAR2 := NULL,
217 argument55 IN VARCHAR2 := NULL,
218 argument56 IN VARCHAR2 := NULL,
219 argument57 IN VARCHAR2 := NULL,
220 argument58 IN VARCHAR2 := NULL,
221 argument59 IN VARCHAR2 := NULL,
222 argument60 IN VARCHAR2 := NULL,
223 argument61 IN VARCHAR2 := NULL,
224 argument62 IN VARCHAR2 := NULL,
225 argument63 IN VARCHAR2 := NULL,
226 argument64 IN VARCHAR2 := NULL,
227 argument65 IN VARCHAR2 := NULL,
228 argument66 IN VARCHAR2 := NULL,
229 argument67 IN VARCHAR2 := NULL,
230 argument68 IN VARCHAR2 := NULL,
231 argument69 IN VARCHAR2 := NULL,
232 argument70 IN VARCHAR2 := NULL,
233 argument71 IN VARCHAR2 := NULL,
234 argument72 IN VARCHAR2 := NULL,
235 argument73 IN VARCHAR2 := NULL,
236 argument74 IN VARCHAR2 := NULL,
237 argument75 IN VARCHAR2 := NULL,
238 argument76 IN VARCHAR2 := NULL,
239 argument77 IN VARCHAR2 := NULL,
240 argument78 IN VARCHAR2 := NULL,
241 argument79 IN VARCHAR2 := NULL,
242 argument80 IN VARCHAR2 := NULL,
243 argument81 IN VARCHAR2 := NULL,
244 argument82 IN VARCHAR2 := NULL,
245 argument83 IN VARCHAR2 := NULL,
246 argument84 IN VARCHAR2 := NULL,
247 argument85 IN VARCHAR2 := NULL,
248 argument86 IN VARCHAR2 := NULL,
249 argument87 IN VARCHAR2 := NULL,
250 argument88 IN VARCHAR2 := NULL,
251 argument89 IN VARCHAR2 := NULL,
252 argument90 IN VARCHAR2 := NULL,
253 argument91 IN VARCHAR2 := NULL,
254 argument92 IN VARCHAR2 := NULL,
255 argument93 IN VARCHAR2 := NULL,
256 argument94 IN VARCHAR2 := NULL,
257 argument95 IN VARCHAR2 := NULL,
258 argument96 IN VARCHAR2 := NULL,
259 argument97 IN VARCHAR2 := NULL,
260 argument98 IN VARCHAR2 := NULL,
261 argument99 IN VARCHAR2 := NULL,
262 argument100 IN VARCHAR2 := NULL,
263 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) IS
264
265 V_Invoice_ID NUMBER;
266 V_Invoice_Num VARCHAR2(50);
267 V_Lease_Number VARCHAR2(15);
268 V_Invoice_Line_ID NUMBER;
269 V_Line_Number NUMBER;
270 V_Line_Type_Lookup_Code VARCHAR2(25);
271 V_Source VARCHAR2(25);
272 V_Dist_Code_Combination_ID NUMBER;
273 V_Vendor_ID NUMBER;
274 V_Vendor_Site_ID NUMBER;
275 V_Terms_ID NUMBER;
276 V_Vendor_Name VARCHAR2(240);
277 V_Vendor_Site_Name VARCHAR2(100);
278 V_Invoice_Amount NUMBER;
279 V_Invoice_Currency_Code VARCHAR2(15);
280 V_Payment_Date DATE;
281 V_Org_ID NUMBER := -1;
282 Temp_Org_ID NUMBER;
283 V_Org_Name VARCHAR2(240);
284 V_Request_ID NUMBER;
285 V_User_ID NUMBER;
286 V_Row VARCHAR2(500);
287 V_Title VARCHAR2(500);
288
289 /* Stamping LE */
290 V_Return_Status VARCHAR2(1);
291 V_Msg_Data VARCHAR2(1000);
292 V_Ptop_LE_Info XLE_BUSINESSINFO_GRP.ptop_le_rec;
293 V_LE_ID NUMBER(15);
294 V_LE_Name VARCHAR2(240);
295
296
297
298 /* Bug 1782129 : Added a code to populate a correct
299 ORG_ID from lessor_site_id. Added AP Payment Term ID - YYOON */
300
301 /* Bug 2513136 : fl.lessor_site_id is replaced with flpi.lessor_site_id */
302
303 CURSOR post_payment_items IS
304
305 SELECT flpi.Lease_ID Lease_ID,
306 flpi.Payment_Schedule_ID Payment_Schedule_ID,
307 flpi.Schedule_Amort_Line_Num Amort_Line_Num,
308 flpi.Dist_Code_Combination_ID,
309 flpi.Invoice_Number Invoice_Number,
310 flpi.Terms_ID Terms_ID,
311 fas.Payment_Date Payment_Date,
312 fas.Payment_Amount Payment_Amount,
313 fl.Lease_Number Lease_Number,
314 fl.Currency_Code Currency_Code,
315 fl.Lessor_ID Lessor_ID,
316 flpi.Lessor_Site_ID Lessor_Site_ID,
317 pv.Vendor_Name Lessor_Name,
318 pvsa.Vendor_Site_Code Lessor_Site_Name,
319 pvsa.Org_ID Org_ID,
320 hao.Name Org_Name
321 FROM FA_LEASE_PAYMENT_ITEMS flpi,
322 FA_LEASES fl, FA_AMORT_SCHEDULES fas,
323 PO_VENDORS pv, PO_VENDOR_SITES_ALL pvsa,
324 HR_ALL_ORGANIZATION_UNITS hao
325 WHERE flpi.Export_Status = 'POST'
326 AND flpi.Lease_ID = fl.Lease_ID
327 AND flpi.Payment_Schedule_ID = fas.Payment_Schedule_ID
328 AND flpi.Schedule_Amort_Line_Num = fas.Amortization_Line_Num
329 AND pv.Vendor_ID = fl.Lessor_ID
330 AND pvsa.Vendor_Site_ID = flpi.Lessor_Site_ID
331 AND hao.Organization_ID = pvsa.Org_ID
332 ORDER BY hao.Organization_ID, pv.Vendor_ID
333 /* code fix for bug no.3649844. adding the update clause to avoid duplicate lines in AP interface table*/
334 FOR UPDATE;
335
336 BEGIN
337
338 V_Request_ID := FND_Global.Conc_Request_ID;
339 V_User_ID := TO_Number(FND_Profile.Value('USER_ID'));
340 V_Line_Number := 1;
341 V_Source := 'Oracle Assets';
342 V_Line_Type_Lookup_Code := 'ITEM';
343
344 FND_File.Put(FND_FILE.LOG, lpad('Export Lease Payments to Payables', 65, ' '));
345 FND_File.New_Line(FND_FILE.LOG,2);
346
347 V_Title := 'Lease Number Lessor Name Lessor Site Amount'||
348 ' Invoice Number Status';
349 FND_File.Put(FND_FILE.LOG, V_Title);
350 FND_File.New_Line(FND_FILE.LOG,2);
351 FOR current_item IN post_payment_items LOOP
352
353 SELECT ap_invoices_interface_s.nextval, ap_invoice_lines_interface_s.nextval
354 INTO V_Invoice_ID, V_Invoice_Line_ID
355 FROM dual;
356
357 -- set other variables from current_item;
358 V_Vendor_ID := current_item.Lessor_ID;
359 V_Invoice_Num := current_item.Invoice_Number;
360 V_Lease_Number := current_item.Lease_Number;
361 V_Vendor_Site_ID := current_item.Lessor_Site_ID;
362 V_Vendor_Name := current_item.Lessor_Name;
363 V_Vendor_Site_Name := current_item.Lessor_Site_Name;
364 V_Dist_Code_Combination_ID := current_item.Dist_Code_Combination_ID;
365 V_Invoice_Amount := current_item.Payment_Amount;
366 V_Invoice_Currency_Code := current_item.Currency_Code;
367 V_Payment_Date := current_item.Payment_Date;
368 V_Terms_ID := current_item.Terms_ID;
369 Temp_Org_ID := V_Org_ID;
370 V_Org_ID := current_item.Org_ID;
371 V_Org_Name := current_item.Org_Name;
372
373
374 /* Stamping LE:
375 We populate the following columns in AP_INVOICE_INTERFACES.
376 LEGAL_ENTITY_ID NUMBER(15)
377 LEGAL_ENTITY_NAME VARCHAR2(50) <- could be optional
378
379 Changed the signature of FA_LPITEMS_EXPT_PKG.Invoice_Insert_Row
380 to pass on the above two paramters.
381 */
382
383 begin
384
385 XLE_BUSINESSINFO_GRP.GET_PURCHASETOPAY_INFO(
386 X_return_status => V_Return_Status,
387 X_msg_data => V_Msg_Data,
388 P_registration_code => null,
389 P_registration_number => null,
390 P_location_id => null,
391 P_code_combination_id => V_Dist_Code_Combination_ID,
392 P_operating_unit_id => current_item.Org_ID,
393 X_ptop_le_info => V_Ptop_LE_Info);
394
395 V_LE_ID := V_Ptop_LE_Info.Legal_Entity_ID;
396 V_LE_Name := V_Ptop_LE_Info.Name;
397
398 exception
399 when others then
400 FND_File.Put(FND_File.Log, V_Msg_Data);
401 FND_File.New_Line(FND_FILE.LOG,1);
402 raise;
403 end;
404
405
406 -- insert into table AP_INVOICES_INTERFACE
407 FA_LPITEMS_EXPT_PKG.Invoice_Insert_Row(
408
409 X_Invoice_ID => V_Invoice_ID,
410 X_Invoice_Num => V_Invoice_Num,
411 X_Vendor_ID => V_Vendor_ID,
412 X_Vendor_Site_ID => V_Vendor_Site_ID,
413 X_Invoice_Amount => V_Invoice_Amount,
414 X_Invoice_Currency_Code => V_Invoice_Currency_Code,
415 X_Source => V_Source,
416 X_Terms_ID => V_Terms_ID,
417 X_Last_Updated_by => V_User_ID,
418 X_Last_Update_Date => Sysdate,
419 X_Last_Update_Login => V_User_ID,
420 X_Created_by => V_User_ID,
421 X_Creation_Date => Sysdate,
422 X_GL_Date => V_Payment_Date,
423 X_Invoice_Date => V_Payment_Date,
424 X_Org_ID => V_Org_ID,
425 X_LE_ID => V_LE_ID,
426 X_LE_Name => V_LE_Name
427 ,p_log_level_rec => p_log_level_rec);
428
429 -- insert into table AP_INVOICE_LINE_INTERFACE
430 FA_LPITEMS_EXPT_PKG.Invoice_Line_Insert_Row(
431
432 X_Invoice_ID => V_Invoice_ID,
433 X_Invoice_Line_ID => V_Invoice_Line_ID,
434 X_Line_Number => V_Line_Number,
435 X_Line_Type_Lookup_Code => V_Line_Type_Lookup_Code,
436 X_Amount => V_Invoice_Amount,
437 X_Dist_Code_Combination_ID => V_Dist_Code_Combination_ID,
438 X_Last_Updated_by => V_User_ID,
439 X_Last_Update_Date => Sysdate,
440 X_Last_Update_Login => V_User_ID,
441 X_Created_by => V_User_ID,
442 X_Creation_Date => Sysdate,
443 X_Accounting_Date => V_Payment_Date,
444 X_Org_ID => V_Org_ID
445 ,p_log_level_rec => p_log_level_rec);
446
447 /*update the FA_Lease_Payment_Items table with new information.*/
448 UPDATE FA_Lease_Payment_Items
449 SET Invoice_ID = V_Invoice_ID,
450 Invoice_Line_ID = V_Invoice_Line_ID,
451 Request_ID = V_Request_ID,
452 Export_Status = 'POSTED'
453 WHERE Lease_ID = current_item.Lease_ID
454 AND Payment_Schedule_ID = current_item.Payment_Schedule_ID
455 AND Schedule_Amort_Line_Num = current_item.Amort_Line_Num;
456
457 if (V_Org_ID <> Temp_Org_ID) then
458 FND_File.New_Line(FND_FILE.LOG,2);
459 FND_File.Put(FND_FILE.LOG, 'Organization Name: '||V_Org_Name);
460 FND_File.New_Line(FND_FILE.LOG,2);
461 end if;
462
463 V_Row := rpad(V_Lease_Number,15,' ')||' ' ||
464 rpad(substr(V_Vendor_Name,1,20),20,' ')||' '||
465 rpad(V_Vendor_Site_Name,15,' ')||' '||
466 rpad(substr(ltrim(to_char(V_Invoice_Amount,'9999999999999D99')),1,15),15,' ')||' '||
467 rpad(substr(V_Invoice_Num,1,25),25,' ')||' ==> Succeeded';
468
469 FND_File.Put(FND_File.Log, V_Row);
470 FND_File.New_Line(FND_FILE.LOG,1);
471
472 END LOOP;
473
474 EXCEPTION
475
476 WHEN others THEN
477
478 V_Row := V_Lease_Number||V_Vendor_Name||V_Vendor_Site_Name||
479 to_char(V_Invoice_Amount)|| V_Invoice_Num||'==> Failed';
480
481 FND_File.Put(FND_File.Log, V_Row);
482 FND_File.New_Line(FND_FILE.LOG,1);
483
484 FA_SRVR_MSG.ADD_MESSAGE(
485
486 CALLING_FN => 'FA_LPITEMS_EXPT_PKG.Payment_Items_to_AP'
487 ,p_log_level_rec => p_log_level_rec);
488
489 END Payment_Items_to_AP;
490
491 END FA_LPITEMS_EXPT_PKG;