[Home] [Help]
PACKAGE BODY: APPS.PN_CREATE_ACC
Source
1 package body PN_CREATE_ACC as
2 -- $Header: PNCRACCB.pls 120.6.12010000.1 2008/07/30 18:18:45 appldev ship $
3
4
5 CURSOR c_ar_data (
6 p_low_lease_id pn_leases.lease_id%TYPE,
7 p_high_lease_id pn_leases.lease_id%TYPE,
8 p_sch_start_date pn_payment_schedules.schedule_date%TYPE,
9 p_sch_end_date pn_payment_schedules.schedule_date%TYPE,
10 p_period_name pn_payment_schedules.period_name%TYPE,
11 p_customer_id pn_payment_terms.customer_id%TYPE)
12 IS
13 SELECT pt.payment_term_id,
14 pt.lease_id,
15 pi.payment_item_id,
16 pi.accounted_date,
17 ps.schedule_date,
18 pt.legal_entity_id,
19 pt.set_of_books_id,
20 pi.org_id,
21 ps.payment_schedule_id,
22 pi1.actual_amount
23 FROM PN_PAYMENT_TERMS pt,
24 PN_LEASES_ALL le ,
25 PN_PAYMENT_ITEMS_ALL pi,
26 PN_PAYMENT_ITEMS_ALL pi1,
27 PN_PAYMENT_SCHEDULES_ALL ps
28 WHERE pt.lease_id = le.lease_id
29 AND le.lease_class_code in ('THIRD_PARTY','SUB_LEASE')
30 AND LE.LEASE_ID BETWEEN P_LOW_LEASE_ID AND P_HIGH_LEASE_ID
31 AND ps.lease_id = le.lease_id
32 AND pi.payment_schedule_id = ps.payment_schedule_id
33 AND pi.payment_term_id = pt.payment_term_id
34 AND ps.payment_Status_lookup_code ='APPROVED'
35 AND ps.schedule_date between p_sch_start_date and p_sch_end_date
36 AND ps.period_name = nvl(p_period_name ,ps.period_name)
37 AND pi.payment_item_type_lookup_code = 'NORMALIZED'
38 AND pi.transferred_to_ar_flag is NULL
39 AND PT.NORMALIZE = 'Y'
40 AND LE.STATUS ='F'
41 AND pt.customer_id = nvl(p_customer_id,pt.customer_id)
42 AND pi1.payment_schedule_id = pi.payment_schedule_id
43 AND pi1.payment_term_id = pi.payment_term_id
44 AND pi1.payment_item_type_lookup_code = 'CASH'
45 AND ((pi1.transferred_to_ar_flag ='Y' AND pi1.actual_Amount <>0 )
46 OR (pi.transferred_to_ar_flag IS NULL AND pi1.actual_Amount = 0 ))
47 ORDER BY ps.payment_schedule_id;
48
49 CURSOR c_ar_data_le_upg (
50 p_low_lease_id pn_leases.lease_id%TYPE,
51 p_high_lease_id pn_leases.lease_id%TYPE,
52 p_sch_start_date pn_payment_schedules.schedule_date%TYPE,
53 p_sch_end_date pn_payment_schedules.schedule_date%TYPE,
54 p_period_name pn_payment_schedules.period_name%TYPE,
55 p_customer_id pn_payment_terms.customer_id%TYPE)
56 IS
57 SELECT pt.payment_term_id payment_term_id,
58 pt.legal_entity_id legal_entity_id,
59 pt.org_id org_id,
60 pt.customer_id customer_id,
61 pt.cust_trx_type_id cust_trx_type_id
62 FROM PN_PAYMENT_TERMS pt,
63 PN_LEASES_ALL le ,
64 PN_PAYMENT_ITEMS_ALL pi,
65 PN_PAYMENT_ITEMS_ALL pi1,
66 PN_PAYMENT_SCHEDULES_ALL ps
67 WHERE pt.lease_id = le.lease_id
68 AND le.lease_class_code in ('THIRD_PARTY','SUB_LEASE')
69 AND LE.LEASE_ID BETWEEN P_LOW_LEASE_ID AND P_HIGH_LEASE_ID
70 AND ps.lease_id = le.lease_id
71 AND pi.payment_schedule_id = ps.payment_schedule_id
72 AND pi.payment_term_id = pt.payment_term_id
73 AND ps.payment_Status_lookup_code ='APPROVED'
74 AND ps.schedule_date between p_sch_start_date and p_sch_end_date
75 AND ps.period_name = nvl(p_period_name ,ps.period_name)
76 AND pi.payment_item_type_lookup_code = 'NORMALIZED'
77 AND pi.transferred_to_ar_flag is NULL
78 AND PT.NORMALIZE = 'Y'
79 AND LE.STATUS ='F'
80 AND pt.customer_id = nvl(p_customer_id,pt.customer_id)
81 AND pi1.payment_schedule_id = pi.payment_schedule_id
82 AND pi1.payment_term_id = pi.payment_term_id
83 AND pi1.payment_item_type_lookup_code = 'CASH'
84 AND ((pi1.transferred_to_ar_flag ='Y' AND pi1.actual_amount <>0 )
85 OR (pi.transferred_to_ar_flag IS NULL AND pi1.actual_amount = 0 ))
86 AND pt.legal_entity_id IS NULL
87 ORDER BY pt.payment_term_id ;
88
89 CURSOR c_ap_data(
90 p_low_lease_id pn_leases.lease_id%TYPE,
91 p_high_lease_id pn_leases.lease_id%TYPE,
92 p_sch_start_date pn_payment_schedules.schedule_date%TYPE,
93 p_sch_end_date pn_payment_schedules.schedule_date%TYPE,
94 p_period_name pn_payment_schedules.period_name%TYPE,
95 p_vendor_id pn_payment_terms.vendor_id%TYPE)
96 IS
97 SELECT pt.payment_term_id,
98 pt.lease_id,
99 pt.set_of_books_id,
100 pt.legal_entity_id,
101 pi.payment_item_id,
102 pi.due_date,
103 ps.schedule_date,
104 pi.org_id,
105 ps.payment_schedule_id,
106 pi1.actual_amount
107 FROM pn_payment_terms pt,
108 pn_leases_all le ,
109 pn_payment_items_all pi,
110 pn_payment_items_all pi1,
111 pn_payment_schedules_all ps
112 WHERE pt.lease_id = le.lease_id
113 AND le.lease_class_code = 'DIRECT'
114 and LE.LEASE_ID BETWEEN P_LOW_LEASE_ID AND P_HIGH_LEASE_ID
115 and ps.lease_id = le.lease_id
116 and pi.payment_schedule_id = ps.payment_schedule_id
117 and pi.payment_term_id = pt.payment_term_id
118 and ps.payment_Status_lookup_code ='APPROVED'
119 and ps.schedule_date BETWEEN p_sch_start_date AND p_sch_end_date
120 and ps.period_name = nvl(p_period_name ,ps.period_name)
121 AND pi.payment_item_type_lookup_code = 'NORMALIZED'
122 and pi.transferred_to_ap_flag IS NULL
123 and PT.NORMALIZE = 'Y'
124 AND LE.STATUS ='F'
125 AND LE.parent_lease_id IS NULL
126 and pt.vendor_id = nvl(p_vendor_id,pt.vendor_id)
127 and pi1.payment_schedule_id = pi.payment_schedule_id
128 and pi1.payment_term_id = pi.payment_term_id
129 and pi1.payment_item_type_lookup_code = 'CASH'
130 and ((pi1.transferred_to_ap_flag ='Y' AND pi1.actual_Amount <>0 )
131 OR (pi.transferred_to_ap_flag IS NULL AND pi1.actual_Amount = 0 ))
132 ORDER BY ps.payment_schedule_id;
133
134 CURSOR c_ap_data_le_upg(
135 p_low_lease_id pn_leases.lease_id%TYPE,
136 p_high_lease_id pn_leases.lease_id%TYPE,
137 p_sch_start_date pn_payment_schedules.schedule_date%TYPE,
138 p_sch_end_date pn_payment_schedules.schedule_date%TYPE,
139 p_period_name pn_payment_schedules.period_name%TYPE,
140 p_vendor_id pn_payment_terms.vendor_id%TYPE)
141 IS
142 SELECT pt.payment_term_id payment_term_id,
143 pt.legal_entity_id legal_entity_id,
144 pt.org_id org_id,
145 pt.vendor_id vendor_id,
146 pt.vendor_site_id vendor_site_id
147 FROM pn_payment_terms pt,
148 pn_leases_all le ,
149 pn_payment_items_all pi,
150 pn_payment_items_all pi1,
151 pn_payment_schedules_all ps
152 WHERE pt.lease_id = le.lease_id
153 AND le.lease_class_code = 'DIRECT'
154 AND LE.LEASE_ID BETWEEN P_LOW_LEASE_ID AND P_HIGH_LEASE_ID
155 AND ps.lease_id = le.lease_id
156 AND pi.payment_schedule_id = ps.payment_schedule_id
157 AND pi.payment_term_id = pt.payment_term_id
158 AND ps.payment_Status_lookup_code ='APPROVED'
159 AND ps.schedule_date BETWEEN p_sch_start_date AND p_sch_end_date
160 AND ps.period_name = nvl(p_period_name ,ps.period_name)
161 AND pi.payment_item_type_lookup_code = 'NORMALIZED'
162 AND pi.transferred_to_ap_flag IS NULL
163 AND PT.NORMALIZE = 'Y'
164 AND LE.STATUS ='F'
165 AND LE.parent_lease_id IS NULL
166 AND pt.vendor_id = nvl(p_vendor_id,pt.vendor_id)
167 AND pi1.payment_schedule_id = pi.payment_schedule_id
168 AND pi1.payment_term_id = pi.payment_term_id
169 AND pi1.payment_item_type_lookup_code = 'CASH'
170 AND ((pi1.transferred_to_ap_flag ='Y' AND pi1.actual_Amount <>0 )
171 OR (pi.transferred_to_ap_flag IS NULL AND pi1.actual_Amount = 0 ))
172 AND pt.legal_entity_id IS NULL
173 ORDER BY pt.payment_term_id;
174
175 CURSOR c_lease_num(p_lease_id pn_leases.lease_id%TYPE)
176 IS
177 SELECT lease_num
178 FROM pn_leases_all
179 WHERE lease_id = p_lease_id;
180
181 -------------------------------------------------------------------------------
182 -- PROCEDURE : CREATE_AP_ACC_R12
183 -- DESCRIPTION: Create accounting for normalize payment items in R12
184 -- HISTORY
185 -- 20-JUL-05 ftanudja o Created for SLA uptake #4527233.
186 -- 01-DEC-05 Hareesha o Changes for Lazy upgrade for LE uptake.
187 -- 12-MAY-06 sdmahesh o Bug # 5219481
188 -- Set transferred_to_ap_flag in PN_PAYMENT_ITEMS
189 -- Stamped xla_event_id PN_PAYMENT_ITEMS
190 -- Set transfer related information in PN_PAYMENT_SCHEDULES
191 -- 27-NOV-06 sdmahesh o Changed event_id_tbl_typ to NUMBER
192 -------------------------------------------------------------------------------
193
194 PROCEDURE CREATE_AP_ACC_R12(
195 P_start_date IN VARCHAR2 ,
196 P_end_date IN VARCHAR2 ,
197 P_low_lease_id IN NUMBER ,
198 P_high_lease_id IN NUMBER ,
199 P_period_name IN VARCHAR2 ,
200 p_vendor_id IN NUMBER ,
201 P_Org_id IN NUMBER
202 ) AS
203
204 l_low_lease_id pn_leases.lease_id%TYPE;
205 l_high_lease_id pn_leases.lease_id%TYPE;
206 l_sch_start_date pn_payment_schedules.schedule_date%TYPE;
207 l_sch_end_date pn_payment_schedules.schedule_date%TYPE;
208 l_lia_account NUMBER;
209 l_prior_payment_schedule_id NUMBER;
210 l_created_by NUMBER;
211 l_last_updated_by NUMBER;
212 l_last_update_login NUMBER;
213 l_last_update_date DATE;
214 l_creation_date DATE;
215
216 TYPE NUMBER_tbl_typ IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
217 TYPE item_id_tbl_typ IS TABLE OF pn_payment_items_all.payment_item_id%TYPE INDEX BY BINARY_INTEGER;
218 TYPE schedule_id_tbl_typ IS TABLE OF pn_payment_schedules_all.payment_schedule_id%TYPE INDEX BY BINARY_INTEGER;
219 TYPE event_id_tbl_typ IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
220
221 term_ID_tbl NUMBER_tbl_typ;
222 LE_tbl NUMBER_tbl_typ;
223 item_id_tbl item_id_tbl_typ;
224 schedule_id_tbl schedule_id_tbl_typ;
225 event_id_tbl event_id_tbl_typ;
226
227 l_index NUMBER;
228 l_index_item NUMBER;
229 l_index_sched NUMBER;
230 l_failed NUMBER;
231 l_success NUMBER;
232
233 BEGIN
234
235 pnp_debug_pkg.put_log_msg('PN_CREATE_ACC.CREATE_AP_ACC_R12 (+)');
236
237 IF P_START_DATE IS NULL THEN
238 l_sch_start_date := to_date('01/01/0001','mm/dd/yyyy');
239 ELSE
240 l_sch_start_date := fnd_date.canonical_to_date(p_start_date);
241 END IF;
242
243 IF P_END_DATE IS NULL THEN
244 l_sch_end_date := to_date('12/31/4712','mm/dd/yyyy');
245 ELSE
246 l_sch_end_date := fnd_date.canonical_to_date(p_end_date);
247 END IF;
248
249 IF P_LOW_LEASE_ID IS NULL THEN
250 l_low_lease_id := -1;
251 ELSE
252 l_low_lease_id := p_low_lease_id;
253 END IF;
254
255 IF P_HIGH_LEASE_ID IS NULL THEN
256 l_high_lease_id := 9999999999999;
257 ELSE
258 l_high_lease_id := p_high_lease_id;
259 END IF;
260
261 l_failed := 0;
262 l_success := 0;
263
264 term_ID_tbl.DELETE;
265 LE_tbl.DELETE;
266 l_index := 1;
267
268 FOR le_rec IN c_ap_data_le_upg (p_low_lease_id => l_low_lease_id,
269 p_high_lease_id => l_high_lease_id,
270 p_sch_start_date => l_sch_start_date,
271 p_sch_end_date => l_sch_end_date,
272 p_period_name => p_period_name,
273 p_vendor_id => p_vendor_id)
274 LOOP
275
276 term_ID_tbl(l_index) := le_rec.payment_term_id;
277
278 l_lia_account :=
279 PN_EXP_TO_AP.get_liability_acc(
280 p_payment_term_id => le_rec.payment_term_id,
281 p_vendor_id => le_rec.vendor_id,
282 p_vendor_site_id => le_rec.vendor_site_id);
283
284 LE_tbl(l_index) :=
285 pn_r12_util_pkg.get_le_for_ap(
286 p_code_combination_id => l_lia_account,
287 p_location_id => le_rec.vendor_site_id,
288 p_org_id => le_rec.org_id);
289
290 l_index := l_index + 1;
291
292 END LOOP;
293
294 IF term_ID_tbl.COUNT > 0 THEN
295
296 FORALL i IN term_ID_tbl.FIRST..term_ID_tbl.LAST
297 UPDATE pn_payment_terms_all
298 SET legal_entity_id = LE_tbl(i)
299 WHERE payment_term_id = term_ID_tbl(i);
300
301 END IF;
302
303 pnp_debug_pkg.log('Before cursor c_term open');
304
305 item_id_tbl.DELETE;
306 schedule_id_tbl.DELETE;
307 event_id_tbl.DELETE;
308
309 l_index_item := 1;
310 l_index_sched := 1;
311 l_prior_payment_schedule_id := -999;
312 l_created_by := FND_GLOBAL.user_id;
313 l_last_updated_by := FND_GLOBAL.USER_ID;
314 l_last_update_login := FND_GLOBAL.LOGIN_ID;
315 l_last_update_date := SYSDATE;
316 l_creation_date := SYSDATE;
317
318 FOR acct_rec IN c_ap_data (p_low_lease_id => l_low_lease_id,
319 p_high_lease_id => l_high_lease_id,
320 p_sch_start_date => l_sch_start_date,
321 p_sch_end_date => l_sch_end_date,
322 p_period_name => p_period_name,
323 p_vendor_id => p_vendor_id)
324 LOOP
325
326 BEGIN
327
328 pn_xla_event_pkg.create_xla_event(
329 p_payment_item_id => acct_rec.payment_item_id
330 ,p_legal_entity_id => acct_rec.legal_entity_id
331 ,p_ledger_id => acct_rec.set_of_books_id
332 ,p_org_id => acct_rec.org_id
333 ,p_bill_or_pay => 'PAY'
334 ,p_event_id => event_id_tbl(l_index_item)
335 );
336
337 item_id_tbl(l_index_item) := acct_rec.payment_item_id;
338 l_index_item := l_index_item + 1;
339
340 IF ( acct_rec.payment_schedule_id <> l_Prior_Payment_Schedule_Id
341 and acct_rec.actual_amount = 0 ) THEN
342
343 l_Prior_Payment_Schedule_Id := acct_rec.payment_schedule_id;
344 schedule_id_tbl(l_index_sched) := acct_rec.payment_schedule_id;
345 l_index_sched := l_index_sched + 1;
346
347 END IF;
348
349 l_success := l_success + 1;
350
351 IF l_index_item > 1000 THEN
352
353 pnp_debug_pkg.log('Updating payment items');
354
355 FORALL i IN item_id_tbl.FIRST..item_id_tbl.LAST
356 UPDATE pn_payment_items_all
357 SET transferred_to_ap_flag = 'Y',
358 xla_event_id = event_id_tbl(i),
359 last_updated_by = l_last_updated_by,
360 last_update_login = l_last_update_login,
361 last_update_date = l_last_update_date
362 WHERE payment_item_id = item_id_tbl(i);
363
364 pnp_debug_pkg.log('Updating Payment schedules ');
365
366 IF schedule_id_tbl.COUNT > 0 THEN
367
368 FORALL i IN schedule_id_tbl.FIRST..schedule_id_tbl.LAST
372 last_updated_by = l_last_updated_by,
369 UPDATE PN_Payment_Schedules_all
370 SET Transferred_By_User_Id = l_last_updated_by,
371 Transfer_Date = l_last_update_date,
373 last_update_login = l_last_update_login,
374 last_update_date = l_last_update_date
375 WHERE Payment_Schedule_Id = schedule_id_tbl(i);
376
377 END IF;
378
379 item_id_tbl.DELETE;
380 schedule_id_tbl.DELETE;
381 event_id_tbl.DELETE;
382
383 l_index_item := 1;
384 l_index_sched := 1;
385 l_prior_payment_schedule_id := -999;
386
387 END IF;
388
389 EXCEPTION
390 WHEN OTHERS THEN
391 l_failed := l_failed + 1;
392
393 IF l_failed = 1 THEN
394 fnd_message.set_name ('PN','PN_XPEAM_ERR_LINES');
395 fnd_message.set_token ('ER_LNO', ' ');
396 pnp_debug_pkg.put_log_msg(fnd_message.get);
397 END IF;
398
399 fnd_message.set_name ('PN','PN_ITEM_ID');
400 fnd_message.set_token ('ID', acct_rec.payment_item_id);
401 pnp_debug_pkg.put_log_msg(fnd_message.get);
402 END;
403 END LOOP;
404
405 pnp_debug_pkg.log('Updating remaining payment items');
406
407 IF item_id_tbl.COUNT > 0 THEN
408
409 FORALL i IN item_id_tbl.FIRST..item_id_tbl.LAST
410 UPDATE pn_payment_items_all
411 SET transferred_to_ap_flag = 'Y' ,
412 xla_event_id = event_id_tbl(i),
413 last_updated_by = l_last_updated_by,
414 last_update_login = l_last_update_login,
415 last_update_date = l_last_update_date
416 WHERE payment_item_id = item_id_tbl(i);
417
418 END IF;
419
420 pnp_debug_pkg.log('Updating remaining Payment schedules');
421
422 IF schedule_id_tbl.COUNT > 0 THEN
423
424 FORALL i IN schedule_id_tbl.FIRST..schedule_id_tbl.LAST
425 UPDATE PN_Payment_Schedules_all
426 SET Transferred_By_User_Id = l_last_updated_by,
427 Transfer_Date = l_last_update_date,
428 last_updated_by = l_last_updated_by,
429 last_update_login = l_last_update_login,
430 last_update_date = l_last_update_date
431 WHERE Payment_Schedule_Id = schedule_id_tbl(i);
432
433 END IF;
434
435 pnp_debug_pkg.put_log_msg('
436 ================================================================================');
437 fnd_message.set_name ('PN','PN_XPEAM_FAIL_LN');
438 fnd_message.set_token ('FAIL_LNO', to_char(l_failed));
439 pnp_debug_pkg.put_log_msg(fnd_message.get);
440
441 fnd_message.set_name ('PN','PN_XPEAM_SUCS_LN');
442 fnd_message.set_token ('SUC_LNO', to_char(l_success));
443 pnp_debug_pkg.put_log_msg(fnd_message.get);
444
445 fnd_message.set_name ('PN','PN_XPEAM_PROC_LN');
446 fnd_message.set_token ('PR_LNO', to_char(l_failed + l_success));
447 pnp_debug_pkg.put_log_msg(fnd_message.get);
448
449 pnp_debug_pkg.put_log_msg('
450 ================================================================================');
451
452 pnp_debug_pkg.put_log_msg('PN_CREATE_ACC.CREATE_AP_ACC_R12 (-)');
453
454 END CREATE_AP_ACC_R12;
455
456 -------------------------------------------------------------------------------
457 -- PROCEDURE : CREATE_AR_ACC_R12
458 -- DESCRIPTION: Create accounting for normalize billing items in R12
459 -- HISTORY
460 -- 20-JUL-05 ftanudja o Created for SLA uptake. #4527233
461 -- 01-DEC-05 Hareesha o Passed pn_mo_cache_utils.get_current_org_id
462 -- while inserting
463 -- 01-DEC-05 Hareesha o Changes for Lazy upgrade for LE uptake.
464 -- 12-MAY-06 sdmahesh o Bug # 5219481
465 -- Set transferred_to_ar_flag in PN_PAYMENT_ITEMS
466 -- Stamped xla_event_id PN_PAYMENT_ITEMS
467 -- Set transfer related information in PN_PAYMENT_SCHEDULES
468 -- 27-NOV-06 sdmahesh o Changed event_id_tbl_typ to NUMBER
469 -------------------------------------------------------------------------------
470
471 PROCEDURE CREATE_AR_ACC_R12(
472 P_start_date IN VARCHAR2 ,
473 P_end_date IN VARCHAR2 ,
474 P_low_lease_id IN NUMBER ,
475 P_high_lease_id IN NUMBER ,
476 P_period_name IN VARCHAR2 ,
477 p_customer_id IN NUMBER ,
478 P_Org_id IN NUMBER
479 ) AS
480
481 l_low_lease_id pn_leases.lease_id%TYPE;
482 l_high_lease_id pn_leases.lease_id%TYPE;
483 l_sch_start_date pn_payment_schedules.schedule_date%TYPE;
484 l_sch_end_date pn_payment_schedules.schedule_date%TYPE;
485
486 TYPE item_id_tbl_typ IS TABLE OF pn_payment_items_all.payment_item_id%TYPE INDEX BY BINARY_INTEGER;
487 TYPE schedule_id_tbl_typ IS TABLE OF pn_payment_schedules_all.payment_schedule_id%TYPE INDEX BY BINARY_INTEGER;
488 TYPE NUMBER_tbl_typ IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
489 TYPE event_id_tbl_typ IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
490
491 term_ID_tbl NUMBER_tbl_typ;
492 LE_tbl NUMBER_tbl_typ;
496
493 item_id_tbl item_id_tbl_typ;
494 schedule_id_tbl schedule_id_tbl_typ;
495 event_id_tbl event_id_tbl_typ;
497 l_index NUMBER;
498 l_index_item NUMBER;
499 l_index_sched NUMBER;
500 l_prior_payment_schedule_id NUMBER;
501 l_created_by NUMBER;
502 l_last_updated_by NUMBER;
503 l_last_update_login NUMBER;
504 l_last_update_date DATE;
505 l_creation_date DATE;
506 l_failed NUMBER;
507 l_success NUMBER;
508
509 BEGIN
510
511 pnp_debug_pkg.put_log_msg('PN_CREATE_ACC.CREATE_AR_ACC_R12 (+)');
512
513 IF P_START_DATE IS NULL THEN
514 l_sch_start_date := to_date('01/01/0001','mm/dd/yyyy');
515 ELSE
516 l_sch_start_date := fnd_date.canonical_to_date(p_start_date);
517 END IF;
518
519 IF P_END_DATE IS NULL THEN
520 l_sch_end_date := to_date('12/31/4712','mm/dd/yyyy');
521 ELSE
522 l_sch_end_date := fnd_date.canonical_to_date(p_end_date);
523 END IF;
524
525 IF P_LOW_LEASE_ID IS NULL THEN
526 l_low_lease_id := -1;
527 ELSE
528 l_low_lease_id := p_low_lease_id;
529 END IF;
530
531 IF P_HIGH_LEASE_ID IS NULL THEN
532 l_high_lease_id := 9999999999999;
533 ELSE
534 l_high_lease_id := p_high_lease_id;
535 END IF;
536
537 l_failed := 0;
538 l_success := 0;
539
540 term_ID_tbl.DELETE;
541 LE_tbl.DELETE;
542
543 l_index := 1;
544
545 FOR le_rec IN c_ar_data_le_upg (p_low_lease_id => l_low_lease_id,
546 p_high_lease_id => l_high_lease_id,
547 p_sch_start_date => l_sch_start_date,
548 p_sch_end_date => l_sch_end_date,
549 p_period_name => p_period_name,
550 p_customer_id => p_customer_id)
551 LOOP
552
553 term_ID_tbl(l_index) := le_rec.payment_term_id;
554
555 LE_tbl(l_index) :=
556 pn_r12_util_pkg.get_le_for_ar(
557 p_customer_id => le_rec.customer_id,
558 p_transaction_type_id => le_rec.cust_trx_type_id,
559 p_org_id => le_rec.org_id);
560
561 l_index := l_index + 1;
562
563 END LOOP;
564
565 IF term_ID_tbl.COUNT > 0 THEN
566
567 FORALL i IN term_ID_tbl.FIRST..term_ID_tbl.LAST
568 UPDATE pn_payment_terms_all
569 SET legal_entity_id = LE_tbl(i)
570 WHERE payment_term_id = term_ID_tbl(i);
571
572 END IF;
573
574 pnp_debug_pkg.log('Before cursor c_term open');
575
576 item_id_tbl.DELETE;
577 schedule_id_tbl.DELETE;
578 event_id_tbl.DELETE;
579
580 l_index_item := 1;
581 l_index_sched := 1;
582 l_prior_payment_schedule_id := -999;
583 l_created_by := FND_GLOBAL.user_id;
584 l_last_updated_by := FND_GLOBAL.USER_ID;
585 l_last_update_login := FND_GLOBAL.LOGIN_ID;
586 l_last_update_date := SYSDATE;
587 l_creation_date := SYSDATE;
588
589 FOR acct_rec IN c_ar_data (p_low_lease_id => l_low_lease_id,
590 p_high_lease_id => l_high_lease_id,
591 p_sch_start_date => l_sch_start_date,
592 p_sch_end_date => l_sch_end_date,
593 p_period_name => p_period_name,
594 p_customer_id => p_customer_id)
595 LOOP
596 BEGIN
597 pn_xla_event_pkg.create_xla_event(
598 p_payment_item_id => acct_rec.payment_item_id
599 ,p_legal_entity_id => acct_rec.legal_entity_id
600 ,p_ledger_id => acct_rec.set_of_books_id
601 ,p_org_id => acct_rec.org_id
602 ,p_bill_or_pay => 'BILL'
603 ,p_event_id => event_id_tbl(l_index_item)
604 );
605
606 item_id_tbl(l_index_item) := acct_rec.payment_item_id;
607 l_index_item := l_index_item + 1;
608
609 IF ( acct_rec.payment_schedule_id <> l_Prior_Payment_Schedule_Id
610 and acct_rec.actual_amount = 0 ) THEN
611
612 l_Prior_Payment_Schedule_Id := acct_rec.payment_schedule_id;
613 schedule_id_tbl(l_index_sched) := acct_rec.payment_schedule_id;
614 l_index_sched := l_index_sched + 1;
615
616 END IF;
617
618 l_success := l_success + 1;
619
620 IF l_index_item = 1000 THEN
621
622 pnp_debug_pkg.log('Updating payment items');
623
624 FORALL i IN item_id_tbl.FIRST..item_id_tbl.LAST
625 UPDATE pn_payment_items_all
626 SET transferred_to_ar_flag = 'Y' ,
627 xla_event_id = event_id_tbl(i),
628 last_updated_by = l_last_updated_by,
629 last_update_login = l_last_update_login,
630 last_update_date = l_last_update_date
631 WHERE payment_item_id = item_id_tbl(i);
632
633 pnp_debug_pkg.log('Updating Payment schedules');
634
635 IF schedule_id_tbl.COUNT > 0 THEN
636
637 FORALL i IN schedule_id_tbl.FIRST..schedule_id_tbl.LAST
641 last_updated_by = l_last_updated_by,
638 UPDATE PN_Payment_Schedules_all
639 SET Transferred_By_User_Id = l_last_updated_by,
640 Transfer_Date = l_last_update_date,
642 last_update_login = l_last_update_login,
643 last_update_date = l_last_update_date
644 WHERE Payment_Schedule_Id = schedule_id_tbl(i);
645
646 END IF;
647
648 item_id_tbl.DELETE;
649 schedule_id_tbl.DELETE;
650 event_id_tbl.DELETE;
651
652 l_index_item := 1;
653 l_index_sched := 1;
654 l_prior_payment_schedule_id := -999;
655
656 END IF;
657
658 EXCEPTION
659 WHEN OTHERS THEN
660 l_failed := l_failed + 1;
661
662 IF l_failed = 1 THEN
663 fnd_message.set_name ('PN','PN_XPEAM_ERR_LINES');
664 fnd_message.set_token ('ER_LNO', ' ');
665 pnp_debug_pkg.put_log_msg(fnd_message.get);
666 END IF;
667
668 fnd_message.set_name ('PN','PN_ITEM_ID');
669 fnd_message.set_token ('ID', acct_rec.payment_item_id);
670 pnp_debug_pkg.put_log_msg(fnd_message.get);
671
672 END;
673 END LOOP;
674
675 pnp_debug_pkg.log('Updating remaining payment items');
676
677 IF item_id_tbl.COUNT > 0 THEN
678
679 FORALL i IN item_id_tbl.FIRST..item_id_tbl.LAST
680 UPDATE pn_payment_items_all
681 SET transferred_to_ar_flag = 'Y' ,
682 xla_event_id = event_id_tbl(i),
683 last_updated_by = l_last_updated_by,
684 last_update_login = l_last_update_login,
685 last_update_date = l_last_update_date
686 WHERE payment_item_id = item_id_tbl(i);
687
688 END IF;
689
690 pnp_debug_pkg.log('Updating remaining Payment schedules');
691
692 IF schedule_id_tbl.COUNT > 0 THEN
693
694 FORALL i IN schedule_id_tbl.FIRST..schedule_id_tbl.LAST
695 UPDATE PN_Payment_Schedules_all
696 SET Transferred_By_User_Id = l_last_updated_by,
697 Transfer_Date = l_last_update_date,
698 last_updated_by = l_last_updated_by,
699 last_update_login = l_last_update_login,
700 last_update_date = l_last_update_date
701 WHERE Payment_Schedule_Id = schedule_id_tbl(i);
702
703 END IF;
704
705 pnp_debug_pkg.put_log_msg('
706 ================================================================================');
707 fnd_message.set_name ('PN','PN_XPEAM_FAIL_LN');
708 fnd_message.set_token ('FAIL_LNO', to_char(l_failed));
709 pnp_debug_pkg.put_log_msg(fnd_message.get);
710
711 fnd_message.set_name ('PN','PN_XPEAM_SUCS_LN');
712 fnd_message.set_token ('SUC_LNO', to_char(l_success));
713 pnp_debug_pkg.put_log_msg(fnd_message.get);
714
715 fnd_message.set_name ('PN','PN_XPEAM_PROC_LN');
716 fnd_message.set_token ('PR_LNO', to_char(l_failed + l_success));
717 pnp_debug_pkg.put_log_msg(fnd_message.get);
718
719 pnp_debug_pkg.put_log_msg('
720 ================================================================================');
721
722 pnp_debug_pkg.put_log_msg('PN_CREATE_ACC.CREATE_AR_ACC_R12 (-)');
723
724 END CREATE_AR_ACC_R12;
725
726 -------------------------------------------------------------------------------
727 -- PROCDURE : CREATE_ACC
728 -- INVOKED FROM :
729 -- PURPOSE :
730 -- HISTORY :
731 -- 14-jul-05 sdmahesh o Bug 4284035 - Replaced pn_ae_headers,pn_ae_lines pnl
732 -- with _ALL table.
733 -- 01-DEC-05 Hareesha o passed pn_mo_cache_utils.get_current_org_id to
734 -- get_profile_value.
735 -- 01-DEC-05 Hareesha o Added check to call c_default_gl_period only
736 -- incase of R12 only.
737 -------------------------------------------------------------------------------
738 PROCEDURE CREATE_ACC (
739 errbuf out NOCOPY varchar2,
740 retcode out NOCOPY varchar2,
741 P_journal_category IN VARCHAR2,
742 p_default_gl_date IN VARCHAR2,
743 P_batch_name in varchar2,
744 P_start_date IN VARCHAR2,
745 P_end_date IN VARCHAR2,
746 P_low_lease_id IN NUMBER ,
747 P_high_lease_id IN NUMBER ,
748 P_period_name in varchar2,
749 p_vendor_id in Number ,
750 p_customer_id in number ,
751 P_selection_type in varchar2,
752 p_gl_transfer_mode in varchar2 ,
753 p_submit_journal_import in varchar2 ,
754 p_process_days in varchar2,
755 p_debug_flag in varchar2 ,
756 P_validate_account in varchar2 ,
757 P_Org_id IN NUMBER
758 ) as
759
760 p_default_period varchar2(250);
761 l_from_date date;
762 l_to_date date;
763 l_message VARCHAR2(2000);
764
765 CURSOR c_default_gl_period IS
766 SELECT period_name
767 FROM gl_period_statuses
768 WHERE closing_status IN ('O', 'F')
772 AND adjustment_period_flag = 'N'
769 AND set_of_books_id = pn_mo_cache_utils.get_profile_value('PN_SET_OF_BOOKS_ID',
770 pn_mo_cache_utils.get_current_org_id)
771 AND application_id = 101
773 AND fnd_date.canonical_to_date(p_default_gl_date) BETWEEN start_date AND end_date;
774
775 CURSOR c_min_max_date IS
776 SELECT min(accounting_date),
777 max(accounting_date)
778 FROM pn_ae_headers_all pnh,
779 pn_ae_lines_all pnl
780 WHERE pnh.ae_header_id = pnl.ae_header_id
781 AND pnl.gl_sl_link_id IS NULL ;
782
783 BEGIN
784 --Print all input parameters
785 fnd_message.set_name ('PN','PN_CRACC_INP_PARAMS');
786 fnd_message.set_token ('JC', P_journal_category);
787 fnd_message.set_token ('BATCH_NAME', p_batch_name);
788 fnd_message.set_token ('DATE', to_char(fnd_date.canonical_to_date(p_default_gl_date),'mm/dd/yyyy'));
789 fnd_message.set_token ('NAME', p_period_name);
790 fnd_message.set_token ('ST_DATE', to_char(fnd_date.canonical_to_date(p_start_date),'mm/dd/yyyy'));
791 fnd_message.set_token ('END_DATE',to_char(fnd_date.canonical_to_date(p_end_date),'mm/dd/yyyy'));
792 fnd_message.set_token ('NUM_LOW', p_low_lease_id);
793 fnd_message.set_token ('NUM_HIGH', p_high_lease_id);
794 fnd_message.set_token ('VEND_ID', p_vendor_id);
795 fnd_message.set_token ('CUST_ID', p_customer_id);
796 fnd_message.set_token ('MODE', p_gl_transfer_mode);
797 fnd_message.set_token ('IMP_FLAG', p_submit_journal_import);
798 fnd_message.set_token ('ACCNT_FLAG', p_validate_account);
799 fnd_message.set_token ('ORG_ID', p_org_id);
800 fnd_message.set_token ('TYPE', p_selection_type);
801 pnp_debug_pkg.put_log_msg(fnd_message.get);
802
803 IF pn_r12_util_pkg.is_r12 THEN
804 NULL;
805 ELSE
806 OPEN c_default_gl_period;
807 FETCH c_default_gl_period INTO p_default_period;
808 IF c_default_gl_period%NOTFOUND THEN
809 CLOSE c_default_gl_period;
810 fnd_message.set_name('PN','PN_GL_PERIOD_NOT_OPEN');
811 errbuf := fnd_message.get;
812 Retcode := 2;
813 ROLLBACK;
814 RETURN;
815 END IF;
816 CLOSE c_default_gl_period;
817 END IF;
818
819 pnp_debug_pkg.log('Default GL period Name = '|| p_default_period);
820
821 IF p_journal_category ='PM REVENUE' THEN
822
823 IF pn_r12_util_pkg.is_r12 THEN
824 CREATE_AR_ACC_R12 (
825 P_start_date ,
826 P_end_date ,
827 P_low_lease_id ,
828 P_high_lease_id ,
829 P_period_name ,
830 p_customer_id ,
831 P_Org_id);
832
833 ELSE
834 CREATE_AR_ACC( P_journal_category ,
835 p_default_gl_date ,
836 p_default_period ,
837 P_start_date ,
838 P_end_date ,
839 P_low_lease_id ,
840 P_high_lease_id ,
841 P_period_name ,
842 p_customer_id ,
843 P_Org_id
844 );
845 END IF;
846
847
848 ELSIF p_journal_category ='PM EXPENSE' then
849
850 IF pn_r12_util_pkg.is_r12 THEN
851
852 CREATE_AP_ACC_R12 (
853 P_start_date ,
854 P_end_date ,
855 P_low_lease_id ,
856 P_high_lease_id ,
857 P_period_name ,
858 p_vendor_id ,
859 P_Org_id);
860 ELSE
861 CREATE_AP_ACC(
862 P_journal_category ,
863 p_default_gl_date ,
864 p_default_period ,
865 P_start_date ,
866 P_end_date ,
867 P_low_lease_id ,
868 P_high_lease_id ,
869 P_period_name ,
870 p_vendor_id ,
871 P_Org_id
872 );
873 END IF;
874
875 ELSIF p_journal_category ='A' then
876
877 IF pn_r12_util_pkg.is_r12 THEN
878
879 CREATE_AP_ACC_R12 (
880 P_start_date ,
881 P_end_date ,
882 P_low_lease_id ,
883 P_high_lease_id ,
884 P_period_name ,
885 p_vendor_id ,
886 P_Org_id);
887
888 CREATE_AR_ACC_R12 (
889 P_start_date ,
890 P_end_date ,
891 P_low_lease_id ,
892 P_high_lease_id ,
893 P_period_name ,
897
894 p_customer_id ,
895 P_Org_id);
896 ELSE
898 CREATE_AR_ACC(
899 P_journal_category ,
900 p_default_gl_date ,
901 p_default_period ,
902 P_start_date ,
903 P_end_date ,
904 P_low_lease_id ,
905 P_high_lease_id ,
906 P_period_name ,
907 p_customer_id ,
908 P_Org_id
909 );
910
911 CREATE_AP_ACC(
912 P_journal_category ,
913 p_default_gl_date ,
914 p_default_period ,
915 P_start_date ,
916 P_end_date ,
917 P_low_lease_id ,
918 P_high_lease_id ,
919 P_period_name ,
920 p_vendor_id ,
921 P_Org_id
922 );
923
924 END IF;
925 END IF;
926
927 IF NOT pn_r12_util_pkg.is_r12 THEN
928
929 /* Call GL transfer only for valied date range */
930
931 OPEN c_min_max_date ;
932 FETCH c_min_max_date INTO l_from_date ,l_to_date;
933 CLOSE c_min_max_date;
934
935 pnp_debug_pkg.log('From Date to xla Procedure = '||To_char(l_from_date,'MM/DD/YYYY'));
936 pnp_debug_pkg.log('To Date to xla Procedure = '||To_char(l_to_date,'MM/DD/YYYY'));
937
938 /* Call GL transfer only for valied date range */
939
940 IF NOT(l_from_date is null AND l_to_date is null) THEN
941
942 PN_GL_TRANSFER.gl_transfer(
943 p_journal_category ,
944 P_selection_type ,
945 P_batch_name ,
946 trunc(l_from_date) ,
947 trunc(l_to_date) ,
948 P_validate_account ,
949 p_gl_transfer_mode ,
950 p_submit_journal_import ,
951 p_process_days ,
952 p_debug_flag
953 );
954 END IF;
955
956 END IF;
957
958 EXCEPTION
959 WHEN OTHERS THEN
960 Errbuf := SQLERRM;
961 Retcode := 2;
962 rollback;
963 return;
964 END CREATE_ACC;
965
966 -------------------------------------------------------------------------------
967 -- FUNCTION: GET_ACCOUNTED_AMOUNT
968 -- SCOPE: PUBLIC
969 -- DESCRIPTION: Gets the accounted amount according to the currency conv rules
970 -- This procedure is called if the accounted amount in the
971 -- pay/bill item is found to be null.
972 --
973 -- HISTORY:
974 -- 29-FEB-2004 Kiran o Created. Bug#3446051
975 -------------------------------------------------------------------------------
976 FUNCTION GET_ACCOUNTED_AMOUNT( p_amount IN NUMBER,
977 p_functional_currency IN VARCHAR2,
978 p_currency IN VARCHAR2,
979 p_rate IN NUMBER,
980 p_conv_date IN DATE,
981 p_conv_type IN VARCHAR2)
982
983 RETURN NUMBER IS
984 -- variables for curr conversion
985 l_conv_type VARCHAR2(15);
986 l_conv_date DATE;
987 -- amt to return
988 l_accounted_amt NUMBER;
989
990 BEGIN
991
992 pnp_debug_pkg.log('PN_CREATE_ACC.GET_ACCOUNTED_AMOUNT ----- (+)');
993
994 IF p_conv_date > SYSDATE THEN
995 l_conv_date := SYSDATE;
996 ELSE
997 l_conv_date := p_conv_date;
998 END IF;
999
1000 IF UPPER(l_conv_type) = 'USER' THEN
1001 l_accounted_amt := NVL(p_amount,0) * NVL(p_rate,0);
1002 ELSE
1003 l_accounted_amt := PNP_UTIL_FUNC.export_curr_amount
1004 (currency_code => p_currency,
1005 export_currency_code => p_functional_currency,
1006 export_date => l_conv_date,
1007 conversion_type => p_conv_type,
1008 actual_amount => NVL(p_amount,0),
1009 p_called_from => 'NOTPNTAUPMT');
1010 END IF;
1011
1012 pnp_debug_pkg.log('Accounted Amount = '||l_accounted_amt);
1013 pnp_debug_pkg.log('PN_CREATE_ACC.GET_ACCOUNTED_AMOUNT ----- (-)');
1014
1015 RETURN l_accounted_amt;
1016
1017 EXCEPTION
1018 WHEN OTHERS THEN
1019 RAISE;
1020
1021 END GET_ACCOUNTED_AMOUNT;
1022 -------------------------------------------------------------------------------
1023 -- PROCEDURE : CREATE_AR_ACC
1024 -- DESCRIPTION: This is create the accounting lines for Normalized Billing
1025 -- items
1026 -- History
1027 -- 17-Oct-2002 Ashish Kumar o Created
1028 -- 29-Sep-2003 Ashish o Bug#3160981 in the cursor c_term change the
1032 -- o Added code to split accounted_Amount per
1029 -- lease_class_code from SUBLEASE to SUB_LEASE
1030 -- 29-FEB-2004 Kiran o Added call to GET_ACCOUNTED_AMOUNT() to
1031 -- ensure accounted_Amount is NOT NULL.
1033 -- distributions
1034 -- o indented code - bug # 3446951
1035 -- 14-jul-2005 SatyaDeep o replaced pn_distributions,pn_payment_terms,
1036 -- pn_leases,pn__payment_items,pn_payment_schedules
1037 -- with their respective _ALL tables
1038 -- 01-DEC-05 Hareesha o Passed pn_mo_cache_utils.get_current_org_id
1039 -- to get_profile_value.
1040 -- Inserted pn_mo_cache_utils.get_current_org_id as
1041 -- org_id into interface tables.
1042 -- 25-DEC-06 acprakas o Bug#5739873. Modified procedure to form
1043 -- header and line description with lease number
1044 -- instead of lease id.
1045 --------------------------------------------------------------------------------
1046 PROCEDURE CREATE_AR_ACC(
1047 P_journal_category IN VARCHAR2 ,
1048 p_default_gl_date IN VARCHAR2 ,
1049 p_default_period IN VARCHAR2 ,
1050 P_start_date IN VARCHAR2 ,
1051 P_end_date IN VARCHAR2 ,
1052 P_low_lease_id IN NUMBER ,
1053 P_high_lease_id IN NUMBER ,
1054 P_period_name IN VARCHAR2 ,
1055 p_customer_id IN NUMBER ,
1056 P_Org_id IN NUMBER
1057 )
1058 AS
1059 v_pn_lease_id PN_LEASES.lease_id%TYPE;
1060 v_pn_period_name PN_PAYMENT_SCHEDULES.period_name%TYPE;
1061 v_pn_code_combination_id PN_PAYMENT_TERMS.code_combination_id%TYPE;
1062 v_pn_term_id PN_PAYMENT_TERMS.ap_ar_term_id%TYPE;
1063 v_pn_trx_type_id PN_PAYMENT_TERMS.cust_trx_type_id%TYPE;
1064 v_transaction_date PN_PAYMENT_ITEMS.due_date%TYPE;
1065 v_normalize PN_PAYMENT_TERMS.normalize%type;
1066 v_pn_payment_item_id PN_PAYMENT_ITEMS.payment_item_id%TYPE;
1067 v_pn_payment_term_id PN_PAYMENT_ITEMS.payment_term_id%TYPE;
1068 v_pn_currency_code PN_PAYMENT_ITEMS.currency_code%TYPE;
1069 v_pn_export_currency_code PN_PAYMENT_ITEMS.export_currency_code%TYPE;
1070 v_pn_export_currency_amount PN_PAYMENT_ITEMS.export_currency_amount%TYPE;
1071 v_pn_payment_schedule_id PN_PAYMENT_ITEMS.payment_schedule_id%TYPE;
1072 v_pn_accounted_date PN_PAYMENT_ITEMS.accounted_date%TYPE;
1073 v_pn_rate PN_PAYMENT_ITEMS.rate%TYPE;
1074 l_amt NUMBER;
1075 l_prior_payment_schedule_id NUMBER := -999;
1076 l_created_by NUMBER := FND_GLOBAL.user_id;
1077 l_last_updated_by NUMBER := FND_GLOBAL.USER_ID;
1078 l_last_update_login NUMBER := FND_GLOBAL.LOGIN_ID;
1079 l_last_update_date DATE := sysdate;
1080 l_creation_date DATE := SYSDATE;
1081 l_msgBuff VARCHAR2 (2000) := NULL;
1082 l_context VARCHAR2(2000);
1083 l_precision NUMBER;
1084 l_ext_precision NUMBER;
1085 l_min_acct_unit NUMBER;
1086 t_count NUMBER := 0;
1087 s_count NUMBER := 0;
1088 l_err_msg1 VARCHAR2(2000);
1089 l_err_msg2 VARCHAR2(2000);
1090 l_err_msg3 VARCHAR2(2000);
1091 l_err_msg4 VARCHAR2(2000);
1092 l_total_rev_amt NUMBER := 0;
1093 l_total_rev_percent NUMBER := 0;
1094 l_diff_amt NUMBER := 0;
1095 l_set_of_books_id NUMBER := TO_NUMBER(pn_mo_cache_utils.get_profile_value('PN_SET_OF_BOOKS_ID'
1096 ,pn_mo_cache_utils.get_current_org_id));
1097 l_func_curr_code gl_sets_of_books.currency_code%TYPE;
1098 l_conv_rate_type pn_currencies.conversion_type%TYPE;
1099 v_pn_EVENT_TYPE_CODE pn_payment_terms.EVENT_TYPE_CODE%TYPE;
1100 l_header_ID PN_AE_HEADERS.AE_HEADER_ID%TYPE;
1101 l_LINE_ID PN_AE_LINES.AE_LINE_ID%TYPE;
1102 l_EVENT_ID PN_ACCOUNTING_EVENTS.ACCOUNTING_EVENT_ID%TYPE;
1103 l_event_number NUMBER;
1104 l_rev_number NUMBER;
1105 l_unearn_number NUMBER;
1106 v_pn_accounted_amount NUMBER;
1107 l_term_id NUMBER := 0;
1108 v_cash_actual_amount NUMBER := 0;
1109 l_start_date RA_CUST_TRX_LINE_GL_DIST.gl_date%type ;
1110 l_sch_start_date DATE ;
1111 l_sch_end_date DATE;
1112 l_low_lease_id PN_LEASES.lease_id%TYPE;
1113 l_high_lease_id PN_LEASES.lease_id%TYPE;
1114 l_total_acc_amt NUMBER := 0;
1115 l_total_acc_percent NUMBER := 0;
1116 l_header_desc VARCHAR2(240);
1120
1117 l_line_desc VARCHAR2(240);
1118 l_message VARCHAR2(2000);
1119 v_pn_lease_num PN_LEASES.lease_num%TYPE; --Bug#5739873
1121 CURSOR get_func_curr_code(p_set_of_books_id IN NUMBER) IS
1122 SELECT currency_code ,chart_of_accounts_id
1123 FROM gl_sets_of_books
1124 WHERE set_of_books_id = p_set_of_books_id;
1125
1126 CURSOR get_acnt_info(p_term_id NUMBER) IS
1127 SELECT account_id,
1128 account_class,
1129 percentage
1130 FROM pn_distributions_all
1131 WHERE payment_term_id = p_term_id;
1132
1133 TYPE acnt_type IS
1134 TABLE OF get_acnt_info%ROWTYPE
1135 INDEX BY BINARY_INTEGER;
1136
1137 rev_acnt_tab acnt_type;
1138 acc_acnt_tab acnt_type;
1139 l_rev_cnt NUMBER := 0;
1140 l_acc_cnt NUMBER := 0;
1141
1142 CURSOR get_send_flag(p_lease_id NUMBER) IS
1143 SELECT nvl(send_entries, 'Y')
1144 FROM pn_lease_details_all
1145 WHERE lease_id = p_lease_id;
1146
1147 CURSOR C_TERM IS
1148 SELECT pt.payment_term_id,
1149 pt.ap_ar_term_id,
1150 pt.cust_trx_type_id,
1151 le.lease_id,
1152 pt.normalize,
1153 PT.EVENT_TYPE_CODE,
1154 pi.payment_item_id,
1155 pi.currency_code,
1156 pi.export_currency_amount,
1157 pi.export_currency_code,
1158 pi.payment_schedule_id,
1159 ps.period_name,
1160 pi.due_date,
1161 pi.accounted_date,
1162 pi.rate,
1163 pi.accounted_amount,
1164 pi1.actual_amount,
1165 ps.schedule_date
1166 FROM PN_PAYMENT_TERMS pt,
1167 PN_LEASES_ALL le ,
1168 PN_PAYMENT_ITEMS_ALL pi,
1169 PN_PAYMENT_ITEMS_ALL pi1,
1170 PN_PAYMENT_SCHEDULES_ALL ps
1171 WHERE pt.lease_id = le.lease_id
1172 AND le.lease_class_code in ('THIRD_PARTY','SUB_LEASE')
1173 AND LE.LEASE_ID BETWEEN L_LOW_LEASE_ID AND L_HIGH_LEASE_ID
1174 AND ps.lease_id = le.lease_id
1175 AND pi.payment_schedule_id = ps.payment_schedule_id
1176 AND pi.payment_term_id = pt.payment_term_id
1177 AND ps.payment_Status_lookup_code ='APPROVED'
1178 AND ps.schedule_date between l_sch_start_date and l_sch_end_date
1179 AND ps.period_name = nvl(p_period_name ,ps.period_name)
1180 AND pi.payment_item_type_lookup_code = 'NORMALIZED'
1181 AND pi.transferred_to_ar_flag is NULL
1182 AND PT.NORMALIZE = 'Y'
1183 AND LE.STATUS ='F'
1184 AND pt.customer_id = nvl(p_customer_id,pt.customer_id)
1185 AND pi1.payment_schedule_id = pi.payment_schedule_id
1186 AND pi1.payment_term_id = pi.payment_term_id
1187 AND pi1.payment_item_type_lookup_code = 'CASH'
1188 AND ((pi1.transferred_to_ar_flag ='Y' AND pi1.actual_Amount <>0 )
1189 OR (pi.transferred_to_ar_flag IS NULL AND pi1.actual_Amount = 0 ))
1190 ORDER BY pt.payment_term_id ;
1191
1192 CURSOR C_VALID_PERIOD IS
1193 SELECT 1
1194 FROM gl_period_statuses
1195 WHERE closing_status IN ('O', 'F')
1196 AND set_of_books_id = pn_mo_cache_utils.get_profile_value('PN_SET_OF_BOOKS_ID',
1197 pn_mo_cache_utils.get_current_org_id)
1198 AND application_id = 101
1199 AND adjustment_period_flag = 'N'
1200 AND period_name = v_pn_period_name;
1201
1202 l_valid_period NUMBER := 0;
1203 l_period_name VARCHAR2(250);
1204 v_schedule_date DATE ;
1205 l_chart_of_id NUMBER;
1206
1207 l_send_flag pn_lease_details_all.send_entries%TYPE := 'Y';
1208 l_lease_id NUMBER := 0;
1209
1210 -- variables for accounted amt.
1211 l_item_accounted_amt NUMBER;
1212 l_accounted_amt NUMBER;
1213 l_total_rev_acc_amt NUMBER;
1214 l_diff_acc_amt NUMBER;
1215
1216 BEGIN
1217
1218 pnp_debug_pkg.log('at start of the Procedure CREATE_AR_ACC');
1219
1220 if p_start_date is null then
1221 l_sch_start_date := to_date('01/01/0001','mm/dd/yyyy');
1222 else
1223 l_sch_start_date := fnd_date.canonical_to_date(p_start_date);
1224 end if;
1225
1226 if p_end_date is null then
1227 l_sch_end_date := to_date('12/31/4712','mm/dd/yyyy');
1228 else
1229 l_sch_end_date := fnd_date.canonical_to_date(p_end_date);
1230 end if;
1231
1232 if p_low_lease_id is null then
1233 l_low_lease_id := -1;
1234 else
1235 l_low_lease_id := p_low_lease_id;
1236 end if;
1237
1238 if p_high_lease_id is null then
1239 l_high_lease_id := 9999999999999;
1240 else
1241 l_high_lease_id := p_high_lease_id;
1242 end if;
1243
1244 pnp_debug_pkg.log('Before cursor c_term open');
1245
1246 OPEN c_term ;
1247
1248 LOOP
1249
1250 l_context := 'Fetching from the cursor';
1251 FETCH c_term INTO
1252 v_pn_payment_term_id,
1253 v_pn_term_id,
1254 v_pn_trx_type_id,
1255 v_pn_lease_id,
1256 v_normalize,
1257 v_pn_event_type_code,
1258 v_pn_payment_item_id,
1259 v_pn_currency_code,
1260 v_pn_export_currency_amount,
1261 v_pn_export_currency_code,
1262 v_pn_payment_schedule_id,
1266 v_pn_rate,
1263 v_pn_period_name,
1264 v_transaction_date,
1265 v_pn_accounted_date,
1267 v_pn_accounted_amount,
1268 v_cash_actual_amount,
1269 v_schedule_date;
1270 EXIT WHEN c_term%NOTFOUND ;
1271
1272 /* Get send entries flag for the lease */
1273 IF l_lease_id <> v_pn_lease_id THEN
1274 OPEN get_send_flag(v_pn_lease_id);
1275 FETCH get_send_flag INTO l_send_flag;
1276 CLOSE get_send_flag;
1277 l_lease_id := v_pn_lease_id;
1278
1279 fnd_message.set_name ('PN','PN_CRACC_LEASE_SEND');
1280 fnd_message.set_token ('NUM', l_lease_id);
1281 fnd_message.set_token ('FLAG', l_send_flag);
1282 pnp_debug_pkg.put_log_msg(fnd_message.get);
1283
1284 END IF;
1285
1286 /* Do processing only if send_flag is Yes */
1287 IF (nvl(l_send_flag,'Y') = 'Y') THEN
1288
1289 pnp_debug_pkg.put_log_msg('
1290 ================================================================================');
1291 fnd_message.set_name ('PN','PN_LEASE_ID');
1292 fnd_message.set_token ('ID', v_pn_lease_id);
1293 l_message := fnd_message.get;
1294 fnd_message.set_name ('PN','PN_ITEM_ID');
1295 fnd_message.set_token ('ID', v_pn_payment_item_id);
1296 l_message := l_message||' - '||fnd_message.get;
1297 fnd_message.set_name ('PN','PN_SCHEDULED_DATE');
1298 fnd_message.set_token ('DATE', to_char(v_schedule_date,'mm/dd/yyyy'));
1299 l_message := l_message||' - '||fnd_message.get;
1300 pnp_debug_pkg.put_log_msg(l_message);
1301 pnp_debug_pkg.put_log_msg('
1302 ================================================================================');
1303
1304 /* Check for Conversion Type and Conversion Rate for Currency Code */
1305
1306 OPEN get_func_curr_code(l_set_of_books_id);
1307 FETCH get_func_curr_code INTO l_func_curr_code ,l_chart_of_id;
1308 CLOSE get_func_curr_code;
1309
1310 l_conv_rate_type
1311 := PNP_UTIL_FUNC.check_conversion_type
1312 ( l_func_curr_code
1313 ,pn_mo_cache_utils.get_current_org_id);
1314
1315 fnd_message.set_name ('PN','PN_CRACC_CV_TYPE');
1316 fnd_message.set_token ('CT', l_conv_rate_type);
1317 pnp_debug_pkg.put_log_msg(fnd_message.get);
1318
1319 fnd_message.set_name ('PN','PN_CRACC_CV_RATE');
1320 fnd_message.set_token ('CR', v_pn_rate);
1321 pnp_debug_pkg.put_log_msg(fnd_message.get);
1322
1323
1324 /* if the accounted amount is null, then, GET IT!
1325 Ensure we populate accounted_CR/DR in the AE Lines */
1326 IF v_pn_accounted_amount IS NULL THEN
1327 l_item_accounted_amt := GET_ACCOUNTED_AMOUNT
1328 (p_amount => v_pn_export_currency_amount,
1329 p_functional_currency => l_func_curr_code,
1330 p_currency => v_pn_currency_code,
1331 p_rate => v_pn_rate,
1332 p_conv_date => v_transaction_date,
1333 p_conv_type => l_conv_rate_type);
1334 ELSE
1335 l_item_accounted_amt := v_pn_accounted_amount;
1336 END IF;
1337
1338 t_count := t_count + 1;
1339
1340 /* Default the precision to 2 */
1341 l_precision := 2;
1342
1343 /* Get the correct precision for the currency so that the amount can be rounded off */
1344 fnd_currency.get_info(v_pn_export_currency_code, l_precision, l_ext_precision, l_min_acct_unit);
1345
1346 OPEN c_valid_period;
1347 FETCH c_valid_period INTO l_valid_period;
1348 IF c_valid_period%notfound THEN
1349 l_start_date := fnd_date.canonical_to_date(p_default_gl_date);
1353 := PNP_UTIL_FUNC.Get_Start_Date
1350 l_period_name := p_default_period;
1351 ELSE
1352 l_start_date
1354 ( V_PN_PERIOD_NAME
1355 ,pn_mo_cache_utils.get_current_org_id);
1356 l_period_name := v_pn_period_name;
1357 END IF;
1358 CLOSE c_valid_period;
1359
1360 fnd_message.set_name ('PN','PN_CRACC_ACC_DATE');
1361 fnd_message.set_token ('DATE', to_char(l_start_date,'mm/dd/yyyy'));
1362 pnp_debug_pkg.put_log_msg(fnd_message.get);
1363
1364 fnd_message.set_name ('PN','PN_CRACC_ACC_PRD');
1365 fnd_message.set_token ('PERIOD', l_period_name);
1366 pnp_debug_pkg.put_log_msg(fnd_message.get);
1367
1368 /* if pay term changed, re init, create accounting EVENT */
1369 IF l_term_id <> v_pn_payment_term_id THEN
1370
1371 l_term_id := v_pn_payment_term_id ;
1372
1373 /* Initailize the tables */
1374 acc_acnt_tab.DELETE;
1375 rev_acnt_tab.DELETE;
1376
1377 l_acc_cnt := 0;
1378 l_rev_cnt := 0;
1379
1380 FOR acnt_rec IN get_acnt_info(v_pn_payment_term_id)
1381 LOOP
1382 IF acnt_rec.account_class = 'REV' THEN
1383 l_rev_cnt := l_rev_cnt + 1;
1384 rev_acnt_tab(l_rev_cnt) := acnt_rec;
1385 ELSIF acnt_rec.account_class = 'UNEARN' THEN
1386 l_acc_cnt := l_acc_cnt + 1;
1387 acc_acnt_tab(l_acc_cnt) := acnt_rec;
1388 END IF;
1389 END LOOP;
1390
1391 SELECT nvl(max(event_number),0) + 1
1392 INTO l_event_number
1393 FROM PN_ACCOUNTING_EVENTS_ALL
1394 WHERE source_table = 'PN_PAYMENT_TERMS'
1395 AND SOURCE_ID = v_pn_payment_term_id
1396 AND EVENT_TYPE_CODE = v_pn_event_type_code;
1397
1398 pnp_debug_pkg.log('Before event insert');
1399
1400 INSERT INTO PN_ACCOUNTING_EVENTS_ALL
1401 (
1402 ACCOUNTING_EVENT_ID ,
1403 EVENT_TYPE_CODE ,
1404 ACCOUNTING_DATE ,
1405 EVENT_NUMBER ,
1406 EVENT_STATUS_CODE ,
1407 SOURCE_TABLE ,
1408 SOURCE_ID ,
1409 CREATION_DATE ,
1410 CREATED_BY ,
1411 LAST_UPDATE_DATE ,
1412 LAST_UPDATED_BY ,
1413 LAST_UPDATE_LOGIN ,
1414 PROGRAM_UPDATE_DATE ,
1415 PROGRAM_ID ,
1416 PROGRAM_APPLICATION_ID ,
1417 REQUEST_ID ,
1418 ORG_ID ,
1419 CANNOT_ACCOUNT_FLAG
1420 )
1421 VALUES
1422 (PN_ACCOUNTING_EVENTS_S.nextval,
1423 nvl(V_PN_EVENT_TYPE_CODE ,'ABS'),
1424 SYSDATE,
1425 l_event_number,
1426 'ACCOUNTED',
1427 'PN_PAYMENT_TERMS',
1428 l_term_id,
1429 l_creation_date,
1430 l_created_by,
1431 l_last_update_date,
1432 l_last_updated_by,
1433 l_last_update_login,
1434 SYSDATE,
1435 FND_GLOBAL.conc_program_id,
1436 FND_GLOBAL.prog_appl_id,
1437 FND_GLOBAL.conc_request_id,
1438 pn_mo_cache_utils.get_current_org_id,
1439 NULL
1440 )
1441 RETURNING ACCOUNTING_EVENT_ID INTO l_EVENT_id ;
1442
1443 END IF; /* if pay term changed, re init, create accounting EVENT */
1444
1445 pnp_debug_pkg.log('Before header insert');
1446
1447 OPEN c_lease_num(v_pn_lease_id);
1448 FETCH c_lease_num INTO v_pn_lease_num;
1449 CLOSE c_lease_num;
1450
1451 l_header_desc := 'Property Manager - '|| 'Lease Number - ' ||v_pn_lease_num ; --Bug#5739873
1452
1453
1454 INSERT INTO PN_AE_HEADERS_ALL
1455 (AE_HEADER_ID ,
1456 ACCOUNTING_EVENT_ID ,
1457 SET_OF_BOOKS_ID ,
1458 AE_CATEGORY ,
1459 CROSS_CURRENCY_FLAG ,
1460 PERIOD_NAME ,
1461 ACCOUNTING_DATE ,
1462 GL_TRANSFER_FLAG ,
1463 GL_TRANSFER_RUN_ID ,
1464 DESCRIPTION ,
1465 ORG_ID ,
1466 CREATION_DATE ,
1467 CREATED_BY ,
1468 LAST_UPDATE_DATE ,
1469 LAST_UPDATED_BY ,
1470 LAST_UPDATE_LOGIN ,
1471 PROGRAM_UPDATE_DATE ,
1472 PROGRAM_APPLICATION_ID ,
1473 PROGRAM_ID ,
1474 REQUEST_ID ,
1475 ACCOUNTING_ERROR_CODE
1476 )
1477 VALUES
1478 (PN_AE_HEADERS_S.nextval,
1479 l_EVENT_ID,
1480 l_set_of_books_id ,
1481 'PM REVENUE' ,
1482 'N',
1483 l_period_name,
1484 l_start_date,
1485 'N',
1486 -1,
1487 l_header_desc,
1488 pn_mo_cache_utils.get_current_org_id,
1492 l_last_updated_by,
1489 l_creation_date,
1490 l_created_by,
1491 l_last_update_date,
1493 l_last_update_login,
1494 SYSDATE,
1495 FND_GLOBAL.prog_appl_id,
1496 FND_GLOBAL.conc_program_id,
1497 FND_GLOBAL.conc_request_id,
1498 NULL
1499 )
1500 RETURNING AE_HEADER_ID INTO l_header_id;
1501
1502 l_total_rev_amt := 0;
1503 l_total_rev_percent := 0;
1504 l_rev_number := 0;
1505
1506 l_accounted_amt := 0;
1507 l_total_rev_acc_amt := 0;
1508 l_diff_acc_amt := 0;
1509
1510 /* for each REVENUE account, create AE line */
1511 FOR i IN 1..rev_acnt_tab.COUNT LOOP
1512 -- actual amount percentages
1513 l_amt := ROUND((v_pn_export_currency_amount * rev_acnt_tab(i).percentage)/100,l_precision);
1514 l_total_rev_amt := l_total_rev_amt + l_amt;
1515 -- accounted amount percentages
1516 l_accounted_amt
1517 := ROUND((l_item_accounted_amt * rev_acnt_tab(i).percentage)/100,l_precision);
1518 l_total_rev_acc_amt := l_total_rev_acc_amt + l_accounted_amt;
1519 -- percentage
1520 l_total_rev_percent := l_total_rev_percent + nvl(rev_acnt_tab(i).percentage,100);
1521
1522 IF l_total_rev_percent = 100 THEN
1523 -- correction for actual
1524 l_diff_amt := l_total_rev_amt - v_pn_export_currency_amount ;
1525 l_amt := l_amt - l_diff_amt;
1526 -- correction for accounted
1527 l_diff_acc_amt := l_total_rev_acc_amt - l_item_accounted_amt;
1528 l_accounted_amt := l_accounted_amt - l_diff_acc_amt;
1529 END IF;
1530
1531 fnd_message.set_name ('PN','PN_CRACC_CRD_AMT');
1532 fnd_message.set_token ('AMT', to_char(round(l_amt,l_precision)));
1533 pnp_debug_pkg.put_log_msg(fnd_message.get);
1534
1535 fnd_message.set_name ('PN','PN_CRACC_REC_ACC ');
1536 fnd_message.set_token ('ACC', FA_RX_FLEX_PKG.get_value(
1537 p_application_id => 101,
1538 p_id_flex_code => 'GL#',
1539 p_id_flex_num => l_chart_of_id,
1540 p_qualifier => 'ALL',
1541 p_ccid => rev_acnt_tab(i).account_id));
1542 pnp_debug_pkg.put_log_msg(fnd_message.get);
1543
1544 pnp_debug_pkg.log('Inserting into lines for Revenue');
1545
1546 l_line_desc := 'Property Manager - '|| 'Lease Number - ' ||v_pn_lease_num ; --Bug#5739873
1547 l_rev_number := l_rev_number + 1;
1548
1549 INSERT INTO PN_AE_LINES_ALL
1550 (
1551 AE_LINE_ID ,
1552 AE_HEADER_ID ,
1553 AE_LINE_NUMBER ,
1554 AE_LINE_TYPE_CODE ,
1555 CODE_COMBINATION_ID ,
1556 CURRENCY_CODE ,
1557 CURRENCY_CONVERSION_TYPE,
1558 CURRENCY_CONVERSION_DATE,
1559 CURRENCY_CONVERSION_RATE,
1560 ENTERED_DR ,
1561 ENTERED_CR ,
1562 ACCOUNTED_DR ,
1563 ACCOUNTED_CR ,
1564 SOURCE_TABLE ,
1565 SOURCE_ID ,
1566 DESCRIPTION ,
1567 ACCOUNTING_ERROR_CODE ,
1568 ORG_ID ,
1569 CREATION_DATE ,
1570 CREATED_BY ,
1571 LAST_UPDATE_DATE ,
1572 LAST_UPDATED_BY ,
1573 LAST_UPDATE_LOGIN ,
1574 PROGRAM_UPDATE_DATE ,
1575 PROGRAM_APPLICATION_ID ,
1576 PROGRAM_ID ,
1577 REQUEST_ID
1578 )
1579 VALUES
1580 (
1581 PN_AE_LINES_S.NEXTVAL,
1582 L_HEADER_ID,
1583 l_rev_number,
1584 rev_acnt_tab(i).account_class,
1585 rev_acnt_tab(i).account_id,
1586 v_pn_export_currency_code,
1587 l_conv_rate_type ,
1588 v_pn_accounted_date,
1589 v_pn_rate,
1590 null,
1591 l_amt,
1592 null,
1593 l_accounted_amt,
1594 'PN_PAYMENT_ITEMS',
1595 V_PN_PAYMENT_ITEM_ID,
1596 l_line_desc,
1597 NULL,
1598 pn_mo_cache_utils.get_current_org_id,
1599 l_creation_date,
1600 l_created_by,
1601 l_last_update_date,
1602 l_last_updated_by,
1603 l_last_update_login,
1604 SYSDATE,
1605 FND_GLOBAL.prog_appl_id,
1606 FND_GLOBAL.conc_program_id,
1607 FND_GLOBAL.conc_request_id
1608 );
1609
1610 pnp_debug_pkg.log('Inserted into lines for Revenue');
1611
1612 END LOOP; /* for each REVENUE account, create AE line */
1613
1614 l_amt := 0;
1615 l_diff_amt := 0;
1616 l_unearn_number := 0;
1620 l_accounted_amt := 0;
1617 l_total_acc_amt := 0;
1618 l_total_acc_percent := 0;
1619
1621 l_total_rev_acc_amt := 0;
1622 l_diff_acc_amt := 0;
1623
1624 /* for each UNEARN account, create AE line */
1625 FOR i IN 1..acc_acnt_tab.COUNT LOOP
1626 -- actual amount percentages
1627 l_amt := ROUND((v_pn_export_currency_amount * acc_acnt_tab(i).percentage)/100,l_precision);
1628 l_total_acc_amt := l_total_acc_amt + l_amt;
1629 -- accounted amount percentages
1630 l_accounted_amt
1631 := ROUND((l_item_accounted_amt * acc_acnt_tab(i).percentage)/100,l_precision);
1632 l_total_rev_acc_amt := l_total_rev_acc_amt + l_accounted_amt;
1633 -- percentage
1634 l_total_acc_percent := l_total_acc_percent + nvl(acc_acnt_tab(i).percentage,100);
1635
1636 IF l_total_acc_percent = 100 THEN
1637 -- correction for actual
1638 l_diff_amt := l_total_acc_amt - v_pn_export_currency_amount ;
1639 l_amt := l_amt - l_diff_amt;
1640 -- correction for accounted
1641 l_diff_acc_amt := l_total_rev_acc_amt - l_item_accounted_amt;
1642 l_accounted_amt := l_accounted_amt - l_diff_acc_amt;
1643 END IF;
1644
1645 fnd_message.set_name ('PN','PN_CRACC_DB_ASS_AMT');
1646 fnd_message.set_token ('AMT', to_char(round(l_amt,l_precision)));
1647 pnp_debug_pkg.put_log_msg(fnd_message.get);
1648
1649 fnd_message.set_name ('PN','PN_CRACC_ASST_ACC ');
1650 fnd_message.set_token ('ACC', FA_RX_FLEX_PKG.get_value(
1651 p_application_id => 101,
1652 p_id_flex_code => 'GL#',
1653 p_id_flex_num => l_chart_of_id,
1654 p_qualifier => 'ALL',
1655 p_ccid => acc_acnt_tab(i).account_id));
1656 pnp_debug_pkg.put_log_msg(fnd_message.get);
1657
1658 pnp_debug_pkg.log('Inserting into lines for Accrued Asset');
1659 l_line_desc := 'Property Manager - '|| 'Lease Number - ' ||v_pn_lease_num ; --Bug#5739873
1660
1661 l_unearn_number := l_unearn_number + 1;
1662
1663 INSERT INTO PN_AE_LINES_ALL
1664 (
1665 AE_LINE_ID ,
1666 AE_HEADER_ID ,
1667 AE_LINE_NUMBER ,
1668 AE_LINE_TYPE_CODE ,
1669 CODE_COMBINATION_ID ,
1670 CURRENCY_CODE ,
1671 CURRENCY_CONVERSION_TYPE,
1672 CURRENCY_CONVERSION_DATE,
1673 CURRENCY_CONVERSION_RATE,
1674 ENTERED_DR ,
1675 ENTERED_CR ,
1676 ACCOUNTED_DR ,
1677 ACCOUNTED_CR ,
1678 SOURCE_TABLE ,
1679 SOURCE_ID ,
1680 DESCRIPTION ,
1681 ACCOUNTING_ERROR_CODE ,
1682 ORG_ID ,
1683 CREATION_DATE ,
1684 CREATED_BY ,
1685 LAST_UPDATE_DATE ,
1686 LAST_UPDATED_BY ,
1687 LAST_UPDATE_LOGIN ,
1688 PROGRAM_UPDATE_DATE ,
1689 PROGRAM_APPLICATION_ID ,
1690 PROGRAM_ID ,
1691 REQUEST_ID
1692 )
1693 VALUES (
1694 PN_AE_LINES_S.nextval,
1695 L_HEADER_ID,
1696 l_unearn_number,
1697 acc_acnt_tab(i).account_class ,
1698 acc_acnt_tab(i).account_id,
1699 v_pn_export_currency_code,
1700 l_conv_rate_type ,
1701 v_pn_accounted_date,
1702 v_pn_rate,
1703 l_amt,
1704 null,
1705 l_accounted_amt,
1706 null,
1707 'PN_PAYMENT_ITEMS',
1708 V_PN_PAYMENT_ITEM_ID,
1709 l_line_desc,
1710 null,
1711 pn_mo_cache_utils.get_current_org_id,
1712 l_creation_date,
1713 l_created_by,
1714 l_last_update_date,
1715 l_last_updated_by,
1716 l_last_update_login,
1717 SYSDATE,
1718 FND_GLOBAL.prog_appl_id,
1719 FND_GLOBAL.conc_program_id,
1720 FND_GLOBAL.conc_request_id
1721 );
1722
1723 pnp_debug_pkg.log('Inserted into lines for Accrued Asset');
1724
1725 END LOOP; /* for each UNEARN account, create AE line */
1726
1727 l_context := 'Updating Payment Items';
1728 pnp_debug_pkg.log('Updating payment items for payment item id : ' ||
1729 to_char(v_pn_payment_item_id) );
1730
1731 UPDATE pn_payment_items_all
1732 SET transferred_to_ar_flag = 'Y' ,
1733 ar_ref_code = v_pn_payment_item_id,
1734 last_updated_by = l_last_updated_by,
1735 last_update_login = l_last_update_login,
1736 last_update_date = l_last_update_date
1737 WHERE payment_item_id = v_pn_payment_item_id;
1738
1739
1740
1744 l_Prior_Payment_Schedule_Id := V_PN_Payment_Schedule_Id;
1741 IF ( V_PN_Payment_Schedule_Id <> l_Prior_Payment_Schedule_Id
1742 AND v_cash_actual_amount = 0 ) THEN
1743
1745 l_context := 'Updating Payment Schedules';
1746 pnp_debug_pkg.log('Updating billing schedules for billing sch id : ' ||
1747 to_char(V_PN_Payment_Schedule_Id) );
1748
1749 UPDATE PN_Payment_Schedules_all
1750 SET Transferred_By_User_Id = l_last_updated_by,
1751 Transfer_Date = l_last_update_date,
1752 last_updated_by = l_last_updated_by,
1753 last_update_login = l_last_update_login,
1754 last_update_date = l_last_update_date
1755 WHERE Payment_Schedule_Id = V_PN_Payment_Schedule_Id;
1756
1757 END IF;
1758
1759 s_count := s_count + 1;
1760
1761 END IF; /* for send_flag check */
1762
1763 END LOOP;
1764
1765 CLOSE c_term;
1766
1767 pnp_debug_pkg.put_log_msg('
1768 ================================================================================');
1769 fnd_message.set_name ('PN','PN_CRACC_TOTAL_ITEMS_PRCSD');
1770 fnd_message.set_token ('NUM', to_char(s_count));
1771 pnp_debug_pkg.put_log_msg(fnd_message.get);
1772 pnp_debug_pkg.put_log_msg('
1773 ================================================================================');
1774
1775 END CREATE_AR_ACC;
1776
1777 -------------------------------------------------------------------------------
1778 -- PROCEDURE : CREATE_AP_ACC
1779 -- DESCRIPTION: Create the accounting lines for Normalized Payment items
1780 -- History
1781 -- 17-Oct-2002 Ashish Kumar o Created
1782 -- 29-FEB-2004 Kiran o Added call to GET_ACCOUNTED_AMOUNT() to
1783 -- ensure accounted_Amount is NOT NULL.
1784 -- o Added code to split accounted_Amount per
1785 -- distributions
1786 -- o indented code - bug # 3446951
1787 -- 14-jul-2005 SatyaDeep o replaced pn_distributions,pn_payment_terms,
1788 -- pn_leases,pn__payment_items,pn_payment_schedules
1789 -- with their respective _ALL tables
1790 -- 01-DEC-05 Hareesha o Passed pn_mo_cache_utils.get_current_org_id to
1791 -- get_profile_value.
1792 -- Inserted pn_mo_cache_utils.get_current_org_id
1793 -- as org_id into interface tables.
1794 -- 25-DEC-06 acprakas o Bug#5739873. Modified procedure to form
1795 -- header and line description with lease number
1796 -- instead of lease id.
1797 -------------------------------------------------------------------------------
1798
1799 PROCEDURE CREATE_AP_ACC(
1800 P_journal_category IN VARCHAR2 ,
1801 p_default_gl_date IN VARCHAR2 ,
1802 p_default_period IN VARCHAR2 ,
1803 P_start_date IN VARCHAR2 ,
1804 P_end_date IN VARCHAR2 ,
1805 P_low_lease_id IN NUMBER ,
1806 P_high_lease_id IN NUMBER ,
1807 P_period_name IN VARCHAR2 ,
1808 p_vendor_id IN NUMBER ,
1809 P_Org_id IN NUMBER
1810 )
1811 AS
1812 v_pn_lease_id PN_LEASES.lease_id%TYPE;
1813 v_pn_period_name PN_PAYMENT_SCHEDULES.period_name%TYPE;
1814 v_pn_code_combination_id PN_PAYMENT_TERMS.code_combination_id%TYPE;
1815 v_pn_distribution_set_id pn_payment_terms.distribution_set_id%TYPE;
1816 v_pn_project_id pn_payment_terms.project_id%type;
1817 v_transaction_date PN_PAYMENT_ITEMS.due_date%TYPE;
1818 v_normalize PN_PAYMENT_TERMS.normalize%type;
1819 v_pn_payment_item_id PN_PAYMENT_ITEMS.payment_item_id%TYPE;
1820 v_pn_payment_term_id PN_PAYMENT_ITEMS.payment_term_id%TYPE;
1821 v_pn_currency_code PN_PAYMENT_ITEMS.currency_code%TYPE;
1822 v_pn_export_currency_code PN_PAYMENT_ITEMS.export_currency_code%TYPE;
1823 v_pn_export_currency_amount PN_PAYMENT_ITEMS.export_currency_amount%TYPE;
1824 v_pn_payment_schedule_id PN_PAYMENT_ITEMS.payment_schedule_id%TYPE;
1825 v_pn_accounted_date PN_PAYMENT_ITEMS.accounted_date%TYPE;
1826 v_pn_rate PN_PAYMENT_ITEMS.rate%TYPE;
1827 l_amt NUMBER;
1828 l_prior_payment_schedule_id NUMBER := -999;
1829 l_created_by NUMBER := FND_GLOBAL.user_id;
1830 l_last_updated_by NUMBER := FND_GLOBAL.USER_ID;
1831 l_last_update_login NUMBER := FND_GLOBAL.LOGIN_ID;
1832 l_last_update_date DATE := sysdate;
1833 l_creation_date DATE := SYSDATE; --ASH
1834 l_msgBuff VARCHAR2 (2000) := NULL;
1835 l_context VARCHAR2(2000);
1836 l_precision NUMBER;
1837 l_ext_precision NUMBER;
1838 l_min_acct_unit NUMBER;
1839 t_count NUMBER := 0;
1840 s_count NUMBER := 0;
1841 l_err_msg1 VARCHAR2(2000);
1842 l_err_msg2 VARCHAR2(2000);
1846 l_total_exp_percent number := 0;
1843 l_err_msg3 VARCHAR2(2000);
1844 l_err_msg4 VARCHAR2(2000);
1845 l_total_exp_amt number := 0;
1847 l_diff_amt number := 0;
1848 l_set_of_books_id NUMBER := TO_NUMBER(pn_mo_cache_utils.get_profile_value('PN_SET_OF_BOOKS_ID',
1849 pn_mo_cache_utils.get_current_org_id));
1850 l_func_curr_code gl_sets_of_books.currency_code%TYPE;
1851 l_conv_rate_type pn_currencies.conversion_type%TYPE;
1852 v_pn_EVENT_TYPE_CODE pn_payment_terms.EVENT_TYPE_CODE%type;
1853 l_header_ID PN_AE_HEADERS.AE_HEADER_ID%type;
1854 l_LINE_ID PN_AE_LINES.AE_LINE_ID%type;
1855 l_EVENT_ID PN_ACCOUNTING_EVENTS.ACCOUNTING_EVENT_ID%type;
1856 l_event_number number;
1857 l_exp_number number;
1858 l_acc_number number;
1859 v_pn_accounted_amount number;
1860 l_term_id number := 0;
1861 v_cash_actual_amount number := 0;
1862 l_start_date RA_CUST_TRX_LINE_GL_DIST.gl_date%type ;
1863 l_sch_start_date date ;
1864 l_sch_end_date date;
1865 l_low_lease_id PN_LEASES.lease_id%TYPE;
1866 l_high_lease_id PN_LEASES.lease_id%TYPE;
1867 l_total_acc_amt NUMBER := 0;
1868 l_total_acc_percent NUMBER := 0;
1869 l_header_desc varchar2(240);
1870 l_line_desc varchar2(240);
1871 l_message VARCHAR2(250);
1872 v_pn_lease_num PN_LEASES.lease_num%TYPE; --Bug#5739873
1873
1874 CURSOR get_func_curr_code(p_set_of_books_id IN NUMBER) IS
1875 SELECT currency_code ,chart_of_accounts_id
1876 FROM gl_sets_of_books
1877 where set_of_books_id = p_set_of_books_id;
1878
1879 CURSOR get_acnt_info(p_term_id NUMBER) IS
1880 SELECT account_id,
1881 account_class,
1882 percentage
1883 FROM pn_distributions_all
1884 WHERE payment_term_id = p_term_id;
1885
1886 TYPE acnt_type IS
1887 TABLE OF get_acnt_info%ROWTYPE
1888 INDEX BY BINARY_INTEGER;
1889
1890 exp_acnt_tab acnt_type;
1891 acc_acnt_tab acnt_type;
1892 l_exp_cnt NUMBER := 0;
1893 l_acc_cnt NUMBER := 0;
1894
1895 CURSOR get_send_flag(p_lease_id NUMBER) IS
1896 SELECT nvl(send_entries, 'Y')
1897 FROM pn_lease_details_all
1898 WHERE lease_id = p_lease_id;
1899
1900 CURSOR C_TERM IS
1901 SELECT pt.payment_term_id,
1902 pt.project_id,
1903 pt.distribution_set_id,
1904 le.lease_id,
1905 pt.normalize,
1906 PT.EVENT_TYPE_CODE,
1907 pi.payment_item_id,
1908 pi.currency_code,
1909 pi.export_currency_amount,
1910 pi.export_currency_code,
1911 pi.payment_schedule_id,
1912 ps.period_name,
1913 pi.due_date,
1914 pi.accounted_date,
1915 pi.rate,
1916 pi.accounted_amount,
1917 pi1.actual_amount,
1918 ps.schedule_date
1919 FROM pn_payment_terms pt,
1920 pn_leases_all le ,
1921 pn_payment_items_all pi,
1922 pn_payment_items_all pi1,
1923 pn_payment_schedules_all ps
1924 WHERE pt.lease_id = le.lease_id
1925 AND le.lease_class_code = 'DIRECT'
1926 and LE.LEASE_ID BETWEEN L_LOW_LEASE_ID AND L_HIGH_LEASE_ID
1927 and ps.lease_id = le.lease_id
1928 and pi.payment_schedule_id = ps.payment_schedule_id
1929 and pi.payment_term_id = pt.payment_term_id
1930 and ps.payment_Status_lookup_code ='APPROVED'
1931 and ps.schedule_date between l_sch_start_date and l_sch_end_date
1932 and ps.period_name = nvl(p_period_name ,ps.period_name)
1933 AND pi.payment_item_type_lookup_code = 'NORMALIZED'
1934 and pi.transferred_to_ap_flag is NULL
1935 and PT.NORMALIZE = 'Y'
1936 AND LE.STATUS ='F'
1937 AND LE.parent_lease_id is NULL
1938 and pt.vendor_id = nvl(p_vendor_id,pt.vendor_id)
1939 and pi1.payment_schedule_id = pi.payment_schedule_id
1940 and pi1.payment_term_id = pi.payment_term_id
1941 and pi1.payment_item_type_lookup_code = 'CASH'
1942 and ((pi1.transferred_to_ap_flag ='Y' and pi1.actual_Amount <>0 )
1943 or (pi.transferred_to_ap_flag is NULL and pi1.actual_Amount = 0 ))
1944 order by pt.payment_term_id;
1945
1946 CURSOR C_VALID_PERIOD IS
1947 SELECT 1
1948 FROM gl_period_statuses
1949 WHERE closing_status IN ('O', 'F')
1950 AND set_of_books_id = pn_mo_cache_utils.get_profile_value('PN_SET_OF_BOOKS_ID',
1951 pn_mo_cache_utils.get_current_org_id)
1952 AND application_id = 101
1953 AND adjustment_period_flag = 'N'
1954 AND period_name = v_pn_period_name;
1955
1959 v_schedule_date date ;
1956 l_valid_period number := 0;
1957 l_period_name varchar2(250);
1958 l_chart_of_id number;
1960
1961 l_send_flag pn_lease_details_all.send_entries%TYPE := 'Y';
1962 l_lease_id NUMBER := 0;
1963
1964 -- variables for accounted amt.
1965 l_item_accounted_amt NUMBER;
1966 l_accounted_amt NUMBER;
1967 l_total_exp_acc_amt NUMBER;
1968 l_diff_acc_amt NUMBER;
1969
1970 BEGIN
1971
1972 pnp_debug_pkg.log('at start of the Procedure CREATE_AP_ACC');
1973
1974 IF P_START_DATE IS NULL THEN
1975 l_sch_start_date := to_date('01/01/0001','mm/dd/yyyy');
1976 ELSE
1977 l_sch_start_date := fnd_date.canonical_to_date(p_start_date);
1978 END IF;
1979
1980 IF P_END_DATE IS NULL THEN
1981 l_sch_end_date := to_date('12/31/4712','mm/dd/yyyy');
1982 ELSE
1983 l_sch_end_date := fnd_date.canonical_to_date(p_end_date);
1984 END IF;
1985
1986 IF P_LOW_LEASE_ID IS NULL THEN
1987 l_low_lease_id := -1;
1988 ELSE
1989 l_low_lease_id := p_low_lease_id;
1990 END IF;
1991
1992 IF P_HIGH_LEASE_ID IS NULL THEN
1993 l_high_lease_id := 9999999999999;
1994 ELSE
1995 l_high_lease_id := p_high_lease_id;
1996 END IF;
1997
1998 pnp_debug_pkg.log('Before cursor c_term open');
1999
2000 OPEN C_TERM ;
2001 LOOP
2002
2003 l_context := 'Fetching from the cursor';
2004 FETCH c_term INTO
2005 v_pn_payment_term_id,
2006 v_pn_project_id,
2007 v_pn_distribution_set_id,
2008 v_pn_lease_id,
2009 v_normalize,
2010 v_pn_event_type_code,
2011 v_pn_payment_item_id,
2012 v_pn_currency_code,
2013 v_pn_export_currency_amount,
2014 v_pn_export_currency_code,
2015 v_pn_payment_schedule_id,
2016 v_pn_period_name,
2017 v_transaction_date,
2018 v_pn_accounted_date,
2019 v_pn_rate,
2020 v_pn_accounted_amount,
2021 v_cash_actual_amount,
2022 v_schedule_date;
2023 EXIT WHEN c_term%NOTFOUND ;
2024
2025
2026 /* Get send entries flag for the lease */
2027 IF l_lease_id <> v_pn_lease_id THEN
2028 OPEN get_send_flag(v_pn_lease_id);
2029 FETCH get_send_flag INTO l_send_flag;
2030 CLOSE get_send_flag;
2031 l_lease_id := v_pn_lease_id;
2032 fnd_message.set_name ('PN','PN_CRACC_LEASE_SEND');
2033 fnd_message.set_token ('NUM', l_lease_id);
2034 fnd_message.set_token ('FLAG', l_send_flag);
2035 pnp_debug_pkg.put_log_msg(fnd_message.get);
2036
2037 END IF;
2038
2039 /* Do processing only if send_flag is Yes */
2040 IF (nvl(l_send_flag,'Y') = 'Y') THEN
2041
2042
2043 pnp_debug_pkg.put_log_msg('
2044 ================================================================================');
2045 fnd_message.set_name ('PN','PN_LEASE_ID');
2046 fnd_message.set_token ('ID', v_pn_lease_id);
2047 l_message := fnd_message.get;
2048 fnd_message.set_name ('PN','PN_ITEM_ID');
2049 fnd_message.set_token ('ID', v_pn_payment_item_id);
2050 l_message := l_message||' - '||fnd_message.get;
2051 fnd_message.set_name ('PN','PN_SCHEDULED_DATE');
2052 fnd_message.set_token ('DATE', to_char(v_schedule_date,'mm/dd/yyyy'));
2053 l_message := l_message||' - '||fnd_message.get;
2054 pnp_debug_pkg.put_log_msg(l_message);
2055 pnp_debug_pkg.put_log_msg('
2056 ================================================================================');
2057
2058
2059 /* Check for Conversion Type and Conversion Rate for Currency Code */
2060 OPEN get_func_curr_code(l_set_of_books_id);
2061 FETCH get_func_curr_code INTO l_func_curr_code,l_chart_of_id;
2062 CLOSE get_func_curr_code;
2063
2064 l_conv_rate_type
2065 := PNP_UTIL_FUNC.check_conversion_type
2066 ( l_func_curr_code
2067 ,pn_mo_cache_utils.get_current_org_id);
2068
2069 fnd_message.set_name ('PN','PN_CRACC_CV_TYPE');
2070 fnd_message.set_token ('CT', l_conv_rate_type);
2071 pnp_debug_pkg.put_log_msg(fnd_message.get);
2072
2073
2074 fnd_message.set_name ('PN','PN_CRACC_CV_RATE');
2075 fnd_message.set_token ('CR', v_pn_rate);
2076 pnp_debug_pkg.put_log_msg(fnd_message.get);
2077
2078
2079 /* if the accounted amount is null, then, GET IT!
2080 Ensure we populate accounted_CR/DR in the AE Lines */
2081 IF v_pn_accounted_amount IS NULL THEN
2082 l_item_accounted_amt := GET_ACCOUNTED_AMOUNT
2083 (p_amount => v_pn_export_currency_amount,
2084 p_functional_currency => l_func_curr_code,
2085 p_currency => v_pn_currency_code,
2086 p_rate => v_pn_rate,
2087 p_conv_date => v_transaction_date,
2088 p_conv_type => l_conv_rate_type);
2089 ELSE
2090 l_item_accounted_amt := v_pn_accounted_amount;
2091 END IF;
2092
2093 /* Default the precision to 2 */
2094 l_precision := 2;
2095
2096 /* Get the correct precision for the currency so that the amount can be rounded off */
2100 FETCH c_valid_period INTO l_valid_period;
2097 fnd_currency.get_info(v_pn_export_currency_code, l_precision, l_ext_precision, l_min_acct_unit);
2098
2099 OPEN C_VALID_PERIOD;
2101 IF c_valid_period%NOTFOUND THEN
2102 l_start_date := fnd_date.canonical_to_date(p_default_gl_date);
2103 l_period_name := p_default_period;
2104 ELSE
2105 l_start_date
2106 := PNP_UTIL_FUNC.Get_Start_Date
2107 ( V_PN_PERIOD_NAME
2108 ,pn_mo_cache_utils.get_current_org_id);
2109 l_period_name := v_pn_period_name;
2110 END IF;
2111 CLOSE C_VALID_PERIOD;
2112
2113
2114
2115 fnd_message.set_name ('PN','PN_CRACC_ACC_DATE');
2116 fnd_message.set_token ('DATE', to_char(l_start_date,'mm/dd/yyyy'));
2117 pnp_debug_pkg.put_log_msg(fnd_message.get);
2118
2119 fnd_message.set_name ('PN','PN_CRACC_ACC_PRD');
2120 fnd_message.set_token ('PERIOD', l_period_name);
2121 pnp_debug_pkg.put_log_msg(fnd_message.get);
2122
2123 IF l_term_id <> v_pn_payment_term_id THEN
2124
2125 l_term_id := v_pn_payment_term_id ;
2126
2127 /* Initailize the tables */
2128 acc_acnt_tab.DELETE;
2129 exp_acnt_tab.DELETE;
2130
2131 l_acc_cnt := 0;
2132 l_exp_cnt := 0;
2133
2134 FOR acnt_rec IN get_acnt_info(v_pn_payment_term_id)
2135 LOOP
2136 IF acnt_rec.account_class = 'EXP' THEN
2137 l_exp_cnt := l_exp_cnt + 1;
2138 exp_acnt_tab(l_exp_cnt) := acnt_rec;
2139 ELSIF acnt_rec.account_class = 'ACC' THEN
2140 l_acc_cnt := l_acc_cnt + 1;
2141 acc_acnt_tab(l_acc_cnt) := acnt_rec;
2142 END IF;
2143 END LOOP;
2144
2145 SELECT nvl(max(event_number),0) + 1
2146 INTO l_event_number
2147 FROM pn_accounting_events_all
2148 WHERE source_table = 'PN_PAYMENT_TERMS'
2149 AND SOURCE_ID = v_pn_payment_term_id
2150 AND EVENT_TYPE_CODE = v_pn_event_type_code;
2151
2152 pnp_debug_pkg.log('Before event insert');
2153
2154
2155 l_context := 'Inserting into PN_ACCOUNTING_EVENTS';
2156
2157 INSERT INTO PN_ACCOUNTING_EVENTS_ALL
2158 (
2159 ACCOUNTING_EVENT_ID ,
2160 EVENT_TYPE_CODE ,
2161 ACCOUNTING_DATE ,
2162 EVENT_NUMBER ,
2163 EVENT_STATUS_CODE ,
2164 SOURCE_TABLE ,
2165 SOURCE_ID ,
2166 CREATION_DATE ,
2167 CREATED_BY ,
2168 LAST_UPDATE_DATE ,
2169 LAST_UPDATED_BY ,
2170 LAST_UPDATE_LOGIN ,
2171 PROGRAM_UPDATE_DATE ,
2172 PROGRAM_ID ,
2173 PROGRAM_APPLICATION_ID ,
2174 REQUEST_ID ,
2175 ORG_ID ,
2176 CANNOT_ACCOUNT_FLAG
2177 )
2178 VALUES
2179 (
2180 PN_ACCOUNTING_EVENTS_S.nextval,
2181 nvl(V_PN_EVENT_TYPE_CODE ,'ABS') ,
2182 SYSDATE,
2183 l_event_number,
2184 'ACCOUNTED',
2185 'PN_PAYMENT_TERMS',
2186 v_pn_payment_term_id,
2187 l_creation_date,
2188 l_created_by,
2189 l_last_update_date,
2190 l_last_updated_by,
2191 l_last_update_login,
2192 SYSDATE,
2193 FND_GLOBAL.conc_program_id,
2194 FND_GLOBAL.prog_appl_id,
2195 FND_GLOBAL.conc_request_id,
2196 pn_mo_cache_utils.get_current_org_id,
2197 NULL
2198 )
2199 RETURNING ACCOUNTING_EVENT_ID INTO l_EVENT_id ;
2200
2201 END IF;
2202
2203 pnp_debug_pkg.log('Before header insert');
2204
2205
2206 OPEN c_lease_num(v_pn_lease_id);
2207 FETCH c_lease_num INTO v_pn_lease_num;
2208 CLOSE c_lease_num;
2209
2210 l_header_desc := 'Property Manager - '|| 'Lease Number - ' ||v_pn_lease_num ; --Bug#5739873
2211
2212 INSERT INTO PN_AE_HEADERS_ALL
2213 (
2214 AE_HEADER_ID ,
2215 ACCOUNTING_EVENT_ID ,
2216 SET_OF_BOOKS_ID ,
2217 AE_CATEGORY ,
2218 CROSS_CURRENCY_FLAG ,
2219 PERIOD_NAME ,
2220 ACCOUNTING_DATE ,
2221 GL_TRANSFER_FLAG ,
2222 GL_TRANSFER_RUN_ID ,
2223 DESCRIPTION ,
2224 ORG_ID ,
2225 CREATION_DATE ,
2226 CREATED_BY ,
2227 LAST_UPDATE_DATE ,
2228 LAST_UPDATED_BY ,
2229 LAST_UPDATE_LOGIN ,
2230 PROGRAM_UPDATE_DATE ,
2231 PROGRAM_APPLICATION_ID ,
2232 PROGRAM_ID ,
2233 REQUEST_ID ,
2234 ACCOUNTING_ERROR_CODE
2235 )
2236 VALUES
2237 (
2238 PN_AE_HEADERS_S.nextval,
2242 'N',
2239 l_EVENT_ID,
2240 l_set_of_books_id ,
2241 'PM EXPENSE' ,
2243 l_period_name,
2244 l_start_date,
2245 'N',
2246 -1,
2247 l_header_desc,
2248 pn_mo_cache_utils.get_current_org_id,
2249 l_creation_date,
2250 l_created_by,
2251 l_last_update_date,
2252 l_last_updated_by,
2253 l_last_update_login,
2254 SYSDATE,
2255 FND_GLOBAL.prog_appl_id,
2256 FND_GLOBAL.conc_program_id,
2257 FND_GLOBAL.conc_request_id,
2258 NULL
2259 )
2260 RETURNING AE_HEADER_ID INTO l_header_id;
2261
2262 l_total_exp_amt := 0;
2263 l_total_exp_percent := 0;
2264 l_exp_number := 0;
2265
2266 l_accounted_amt := 0;
2267 l_diff_acc_amt := 0;
2268 l_total_exp_acc_amt := 0;
2269
2270 FOR I IN 1..EXP_ACNT_TAB.COUNT LOOP
2271 -- actual amount percentages
2272 l_amt := ROUND((v_pn_export_currency_amount * exp_acnt_tab(i).percentage)/100,l_precision);
2273 l_total_exp_amt := l_total_exp_amt + l_amt;
2274 -- accounted amount percentages
2275 l_accounted_amt :=
2276 ROUND((l_item_accounted_amt * exp_acnt_tab(i).percentage)/100,l_precision);
2277 l_total_exp_acc_amt := l_total_exp_acc_amt + l_accounted_amt;
2278 -- percentage
2279 l_total_exp_percent := l_total_exp_percent + nvl(exp_acnt_tab(i).percentage,100);
2280
2281 IF l_total_exp_percent = 100 THEN
2282 -- correction for actual amount
2283 l_diff_amt := l_total_exp_amt - v_pn_export_currency_amount ;
2284 l_amt := l_amt - l_diff_amt;
2285 -- correction for accounted amont
2286 l_diff_acc_amt := l_total_exp_acc_amt - l_item_accounted_amt;
2287 l_accounted_amt := l_accounted_amt - l_diff_acc_amt;
2288 END IF;
2289
2290 fnd_message.set_name ('PN','PN_CRACC_DBT_AMT');
2291 fnd_message.set_token ('AMT', to_char(round(l_amt,l_precision)));
2292 pnp_debug_pkg.put_log_msg(fnd_message.get);
2293
2294 fnd_message.set_name ('PN','PN_CRACC_EXP_ACC ');
2295 fnd_message.set_token ('ACC', FA_RX_FLEX_PKG.get_value(
2296 p_application_id => 101,
2297 p_id_flex_code => 'GL#',
2298 p_id_flex_num => l_chart_of_id,
2299 p_qualifier => 'ALL',
2300 p_ccid => exp_acnt_tab(i).account_id));
2301
2302 pnp_debug_pkg.put_log_msg(fnd_message.get);
2303
2304 pnp_debug_pkg.log('Inserting into lines for Expense');
2305
2306 l_line_desc := 'Property Manager - '|| 'Lease Number - ' ||v_pn_lease_num ; --Bug#5739873
2307 l_exp_number := l_exp_number +1;
2308
2309 INSERT INTO PN_AE_LINES_ALL
2310 (
2311 AE_LINE_ID ,
2312 AE_HEADER_ID ,
2313 AE_LINE_NUMBER ,
2314 AE_LINE_TYPE_CODE ,
2315 CODE_COMBINATION_ID ,
2316 CURRENCY_CODE ,
2317 CURRENCY_CONVERSION_TYPE,
2318 CURRENCY_CONVERSION_DATE,
2319 CURRENCY_CONVERSION_RATE,
2320 ENTERED_DR ,
2321 ENTERED_CR ,
2322 ACCOUNTED_DR ,
2323 ACCOUNTED_CR ,
2324 SOURCE_TABLE ,
2325 SOURCE_ID ,
2326 DESCRIPTION ,
2327 ACCOUNTING_ERROR_CODE ,
2328 ORG_ID ,
2329 CREATION_DATE ,
2330 CREATED_BY ,
2331 LAST_UPDATE_DATE ,
2332 LAST_UPDATED_BY ,
2333 LAST_UPDATE_LOGIN ,
2334 PROGRAM_UPDATE_DATE ,
2335 PROGRAM_APPLICATION_ID ,
2336 PROGRAM_ID ,
2337 REQUEST_ID
2338 )
2339 VALUES
2340 (
2341 PN_AE_LINES_S.nextval,
2342 L_HEADER_ID,
2343 l_exp_number,
2344 exp_acnt_tab(i).account_class,
2345 exp_acnt_tab(i).account_id,
2346 v_pn_export_currency_code,
2347 l_conv_rate_type ,
2348 v_pn_accounted_date,
2349 v_pn_rate,
2350 l_amt,
2351 null,
2352 l_accounted_amt,
2353 null,
2354 'PN_PAYMENT_ITEMS',
2355 V_PN_PAYMENT_ITEM_ID,
2356 l_line_desc,
2357 NULL,
2358 pn_mo_cache_utils.get_current_org_id,
2359 l_creation_date,
2360 l_created_by,
2361 l_last_update_date,
2362 l_last_updated_by,
2363 l_last_update_login,
2364 SYSDATE,
2365 FND_GLOBAL.prog_appl_id,
2366 FND_GLOBAL.conc_program_id,
2367 FND_GLOBAL.conc_request_id
2368 );
2369
2370 pnp_debug_pkg.log('Inserted into lines for Expense');
2371
2372 END LOOP;
2373
2374 l_amt := 0;
2375 l_diff_amt := 0;
2376 l_acc_number := 0;
2377 l_total_acc_amt := 0;
2378 l_total_acc_percent := 0;
2379
2380 l_accounted_amt := 0;
2381 l_diff_acc_amt := 0;
2385 -- actual amount percentages
2382 l_total_exp_acc_amt := 0;
2383
2384 FOR I IN 1..ACC_ACNT_TAB.COUNT LOOP
2386 l_amt := round((v_pn_export_currency_amount * acc_acnt_tab(i).percentage)/100,l_precision);
2387 l_total_acc_amt := l_total_acc_amt + l_amt;
2388 -- accounted amount percentages
2389 l_accounted_amt :=
2390 ROUND((l_item_accounted_amt * acc_acnt_tab(i).percentage)/100,l_precision);
2391 l_total_exp_acc_amt := l_total_exp_acc_amt + l_accounted_amt;
2392 -- percentage
2393 l_total_acc_percent := l_total_acc_percent + nvl(acc_acnt_tab(i).percentage,100);
2394
2395 IF l_total_acc_percent = 100 THEN
2396 -- correction for actual amount
2397 l_diff_amt := l_total_acc_amt - v_pn_export_currency_amount ;
2398 l_amt := l_amt - l_diff_amt;
2399 -- correction for accounted amont
2400 l_diff_acc_amt := l_total_exp_acc_amt - l_item_accounted_amt;
2401 l_accounted_amt := l_accounted_amt - l_diff_acc_amt;
2402 END IF;
2403
2404 fnd_message.set_name ('PN','PN_CRACC_CR_LIA_AMT');
2405 fnd_message.set_token ('AMT', to_char(round(l_amt,l_precision)));
2406 pnp_debug_pkg.put_log_msg(fnd_message.get);
2407
2408 fnd_message.set_name ('PN','PN_CRACC_LIA_ACC ');
2409 fnd_message.set_token ('ACC', FA_RX_FLEX_PKG.get_value(
2410 p_application_id => 101,
2411 p_id_flex_code => 'GL#',
2412 p_id_flex_num => l_chart_of_id,
2413 p_qualifier => 'ALL',
2414 p_ccid => acc_acnt_tab(i).account_id));
2415
2416 pnp_debug_pkg.put_log_msg(fnd_message.get);
2417
2418 pnp_debug_pkg.log('Inserting into lines for Accrued Liability');
2419 l_line_desc := 'Property Manager - '|| 'Lease Number - ' ||v_pn_lease_num ; --Bug#5739873
2420
2421
2422 l_acc_number := l_acc_number +1;
2423
2424 INSERT INTO PN_AE_LINES_ALL
2425 (
2426 AE_LINE_ID ,
2427 AE_HEADER_ID ,
2428 AE_LINE_NUMBER ,
2429 AE_LINE_TYPE_CODE ,
2430 CODE_COMBINATION_ID ,
2431 CURRENCY_CODE ,
2432 CURRENCY_CONVERSION_TYPE,
2433 CURRENCY_CONVERSION_DATE,
2434 CURRENCY_CONVERSION_RATE,
2435 ENTERED_DR ,
2436 ENTERED_CR ,
2437 ACCOUNTED_DR ,
2438 ACCOUNTED_CR ,
2439 SOURCE_TABLE ,
2440 SOURCE_ID ,
2441 DESCRIPTION ,
2442 ACCOUNTING_ERROR_CODE ,
2443 ORG_ID ,
2444 CREATION_DATE ,
2445 CREATED_BY ,
2446 LAST_UPDATE_DATE ,
2447 LAST_UPDATED_BY ,
2448 LAST_UPDATE_LOGIN ,
2449 PROGRAM_UPDATE_DATE ,
2450 PROGRAM_APPLICATION_ID ,
2451 PROGRAM_ID ,
2452 REQUEST_ID
2453 )
2454 VALUES
2455 (
2456 PN_AE_LINES_S.nextval,
2457 L_HEADER_ID,
2458 l_acc_number,
2459 acc_acnt_tab(i).account_class ,
2460 acc_acnt_tab(i).account_id,
2461 v_pn_export_currency_code,
2462 l_conv_rate_type ,
2463 v_pn_accounted_date,
2464 v_pn_rate,
2465 NULL,
2466 l_amt,
2467 NULL,
2468 l_accounted_amt,
2469 'PN_PAYMENT_ITEMS',
2470 V_PN_PAYMENT_ITEM_ID,
2471 l_line_desc,
2472 NULL,
2473 pn_mo_cache_utils.get_current_org_id,
2474 l_creation_date,
2475 l_created_by,
2476 l_last_update_date,
2477 l_last_updated_by,
2478 l_last_update_login,
2479 SYSDATE,
2480 FND_GLOBAL.prog_appl_id,
2481 FND_GLOBAL.conc_program_id,
2482 FND_GLOBAL.conc_request_id
2483 );
2484
2485 pnp_debug_pkg.log('Inserted into lines for Accrued Liability');
2486
2487 END LOOP;
2488
2489 l_context := 'Updating Payment Items';
2490 pnp_debug_pkg.log('Updating payment items for payment item id : ' ||
2491 to_char(v_pn_payment_item_id) );
2492
2493 UPDATE pn_payment_items_all
2494 SET transferred_to_ap_flag = 'Y' ,
2495 last_updated_by = l_last_updated_by,
2496 last_update_login = l_last_update_login,
2497 last_update_date = l_last_update_date
2498 WHERE payment_item_id = v_pn_payment_item_id;
2499
2500 IF ( V_PN_Payment_Schedule_Id <> l_Prior_Payment_Schedule_Id
2501 and v_cash_actual_amount = 0 ) THEN
2502
2503 l_Prior_Payment_Schedule_Id := V_PN_Payment_Schedule_Id;
2504
2505 l_context := 'Updating Payment Schedules';
2506
2507 pnp_debug_pkg.log('Updating Payment schedules for Payment sch id : ' ||
2508 to_char(V_PN_Payment_Schedule_Id) );
2509
2510 UPDATE PN_Payment_Schedules_all
2511 SET Transferred_By_User_Id = l_last_updated_by,
2512 Transfer_Date = l_last_update_date,
2513 last_updated_by = l_last_updated_by,
2514 last_update_login = l_last_update_login,
2515 last_update_date = l_last_update_date
2516 WHERE Payment_Schedule_Id = V_PN_Payment_Schedule_Id;
2517
2518 END IF;
2519 s_count := s_count + 1;
2520
2521 END IF; /* for send_flag check */
2522
2523 END LOOP;
2524
2525 CLOSE c_term;
2526
2527 pnp_debug_pkg.put_log_msg('
2528 ================================================================================');
2529 fnd_message.set_name ('PN','PN_CRACC_TOTAL_PAY_ITEMS_PRCSD');
2530 fnd_message.set_token ('NUM', to_char(s_count));
2531 pnp_debug_pkg.put_log_msg(fnd_message.get);
2532 pnp_debug_pkg.put_log_msg('
2533 ================================================================================');
2534 END CREATE_AP_ACC;
2535
2536 END PN_CREATE_ACC;