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