[Home] [Help]
PACKAGE BODY: APPS.PNT_PAYMENT_TERMS_PKG
Source
1 PACKAGE BODY pnt_payment_terms_pkg AS
2 -- $Header: PNTPYTRB.pls 120.16 2007/05/31 06:54:04 lbala ship $
3
4
5 -------------------------------------------------------------------
6 -- PROCEDURE UPDATE_PNT_ITEMS_AMT
7 -------------------------------------------------------------------
8 PROCEDURE UPDATE_PNT_ITEMS_AMT
9 (p_pnt_term_id IN pn_payment_items.payment_term_id%TYPE,
10 p_old_est_amt IN pn_payment_items.estimated_amount%TYPE,
11 p_new_est_amt IN pn_payment_items.estimated_amount%TYPE,
12 p_lease_id IN pn_payment_terms.lease_id%TYPE,
13 p_trm_str_dt IN DATE,
14 p_trm_end_dt IN DATE,
15 p_freq_code IN pn_payment_terms.frequency_code%TYPE);
16
17 -------------------------------------------------------------------
18 -- PROCEDURE UPDATE_PNT_ITEMS_RATE
19 -------------------------------------------------------------------
20 PROCEDURE UPDATE_PNT_ITEMS_RATE(p_pnt_term_id pn_payment_items.payment_item_id%TYPE,
21 p_new_rate pn_payment_items.rate%TYPE);
22
23 -------------------------------------------------------------------
24 -- PROCEDURE : CHECK_PAYMENT_AMOUNTS
25 -------------------------------------------------------------------
26 PROCEDURE CHECK_PAYMENT_AMOUNTS (
27 X_RETURN_STATUS IN OUT NOCOPY VARCHAR2
28 ,X_ACTUAL_AMOUNT IN NUMBER
29 ,X_ESTIMATED_AMOUNT IN NUMBER
30 )
31 IS
32 BEGIN
33 pnp_debug_pkg.debug('PNT_PAYMENT_TERMS_PKG.CHECK_PAYMENT_AMOUNTS (+)');
34 IF ((X_ACTUAL_AMOUNT IS NULL) AND
35 (X_ESTIMATED_AMOUNT IS NULL))
36 THEN
37 fnd_message.set_name ('PN','PN_PAYMENT_AMOUNT_NULL');
38 x_return_status := 'E';
39 END IF;
40
41 pnp_debug_pkg.debug('PNT_PAYMENT_TERMS_PKG.CHECK_PAYMENT_AMOUNTS (-) RetStat: '||x_return_status);
42 END CHECK_PAYMENT_AMOUNTS;
43
44 --------------------------------------------------------------------------------
45 -- PROCDURE : INSERT_ROW
46 -- INVOKED FROM : insert_row procedure
47 -- PURPOSE : inserts the row
48 -- HISTORY :
49 -- 04-DEC-03 ftanudja o Added parameter area_type_code, area. 3257508.
50 -- 17-DEC-03 ATUPPAD o Added parameter grouping_rule_id.
51 -- Part of Grouping Logic for Invoices
52 -- 05-JUL-05 sdmahesh o Bug 4284035 - Replaced pn_payment_terms with _ALL
53 -- table.
54 -- 15-JUL-05 ftanudja o R12 changes. #4495054, #4497295
55 -- 24-NOV-05 Kiran o rounding for amounts done based on currency precision
56 -- 17-APR-06 Hareesha o Bug 5115291 Passed norm_start_date while inserting
57 -- into pn_payment_terms_all
58 -- 14-AUG-06 Pikhar o Set update_nbp_flag to 'Y' on insert
59 -- 21-SEP-06 Hareesha o Removed populating pn_index_exclude_term basing on
60 -- profile option, since we are using a systemoption
61 -- 09-JAN-07 Lokesh o Removed call to CHECK_APPROVED_SCHEDULE_EXISTS which
62 -- checks for schedule day conflicts ,for M28 item #11
63 -- 02-JAn-07 Hareesha o M28#16 Added x_recur_bb_calc_date.
64 -- 06-MAR-07 Prabhakar o Added opex_agr_id, opex_recon_id and opex_type
65 -- for insert row handlers.
66 --------------------------------------------------------------------------------
67 PROCEDURE INSERT_ROW (
68 X_ROWID IN OUT NOCOPY VARCHAR2,
69 X_PAYMENT_TERM_ID IN OUT NOCOPY NUMBER,
70 X_PAYMENT_PURPOSE_CODE IN VARCHAR2,
71 X_PAYMENT_TERM_TYPE_CODE IN VARCHAR2,
72 X_FREQUENCY_CODE IN VARCHAR2,
73 X_LEASE_ID IN NUMBER,
74 X_LEASE_CHANGE_ID IN NUMBER,
75 X_START_DATE IN DATE,
76 X_END_DATE IN DATE,
77 X_VENDOR_ID IN NUMBER,
78 X_VENDOR_SITE_ID IN NUMBER,
79 X_CUSTOMER_ID IN NUMBER,
80 X_CUSTOMER_SITE_USE_ID IN NUMBER,
81 X_TARGET_DATE IN DATE,
82 X_ACTUAL_AMOUNT IN NUMBER,
83 X_ESTIMATED_AMOUNT IN NUMBER,
84 X_SET_OF_BOOKS_ID IN NUMBER,
85 X_CURRENCY_CODE IN VARCHAR2,
86 X_RATE IN NUMBER,
87 X_NORMALIZE IN VARCHAR2,
88 X_LOCATION_ID IN NUMBER,
89 X_SCHEDULE_DAY IN NUMBER,
90 X_CUST_SHIP_SITE_ID IN NUMBER,
91 X_AP_AR_TERM_ID IN NUMBER,
92 X_CUST_TRX_TYPE_ID IN NUMBER,
93 X_PROJECT_ID IN NUMBER,
94 X_TASK_ID IN NUMBER,
95 X_ORGANIZATION_ID IN NUMBER,
96 X_EXPENDITURE_TYPE IN VARCHAR2,
97 X_EXPENDITURE_ITEM_DATE IN DATE,
98 X_TAX_GROUP_ID IN NUMBER,
99 X_TAX_CODE_ID IN NUMBER,
100 X_TAX_CLASSIFICATION_CODE IN VARCHAR2,
101 X_TAX_INCLUDED IN VARCHAR2,
102 X_DISTRIBUTION_SET_ID IN NUMBER,
103 X_INV_RULE_ID IN NUMBER,
104 X_ACCOUNT_RULE_ID IN NUMBER,
105 X_SALESREP_ID IN NUMBER,
106 X_APPROVED_BY IN NUMBER,
107 X_STATUS IN VARCHAR2,
108 X_INDEX_PERIOD_ID IN NUMBER,
109 X_INDEX_TERM_INDICATOR IN VARCHAR2,
110 X_PO_HEADER_ID IN NUMBER,
111 X_CUST_PO_NUMBER IN VARCHAR2,
112 X_RECEIPT_METHOD_ID IN NUMBER,
113 X_VAR_RENT_INV_ID IN NUMBER ,
114 X_VAR_RENT_TYPE IN VARCHAR2,
115 X_PERIOD_BILLREC_ID IN NUMBER,
116 X_REC_AGR_LINE_ID IN NUMBER,
117 X_AMOUNT_TYPE IN VARCHAR2,
118 X_CHANGED_FLAG IN VARCHAR2,
119 X_TERM_TEMPLATE_ID IN NUMBER,
120 X_ATTRIBUTE_CATEGORY IN VARCHAR2,
121 X_ATTRIBUTE1 IN VARCHAR2,
122 X_ATTRIBUTE2 IN VARCHAR2,
123 X_ATTRIBUTE3 IN VARCHAR2,
124 X_ATTRIBUTE4 IN VARCHAR2,
125 X_ATTRIBUTE5 IN VARCHAR2,
126 X_ATTRIBUTE6 IN VARCHAR2,
127 X_ATTRIBUTE7 IN VARCHAR2,
128 X_ATTRIBUTE8 IN VARCHAR2,
129 X_ATTRIBUTE9 IN VARCHAR2,
130 X_ATTRIBUTE10 IN VARCHAR2,
131 X_ATTRIBUTE11 IN VARCHAR2,
132 X_ATTRIBUTE12 IN VARCHAR2,
133 X_ATTRIBUTE13 IN VARCHAR2,
134 X_ATTRIBUTE14 IN VARCHAR2,
135 X_ATTRIBUTE15 IN VARCHAR2,
136 X_PROJECT_ATTRIBUTE_CATEGORY IN VARCHAR2,
137 X_PROJECT_ATTRIBUTE1 IN VARCHAR2,
138 X_PROJECT_ATTRIBUTE2 IN VARCHAR2,
139 X_PROJECT_ATTRIBUTE3 IN VARCHAR2,
140 X_PROJECT_ATTRIBUTE4 IN VARCHAR2,
141 X_PROJECT_ATTRIBUTE5 IN VARCHAR2,
142 X_PROJECT_ATTRIBUTE6 IN VARCHAR2,
143 X_PROJECT_ATTRIBUTE7 IN VARCHAR2,
144 X_PROJECT_ATTRIBUTE8 IN VARCHAR2,
145 X_PROJECT_ATTRIBUTE9 IN VARCHAR2,
146 X_PROJECT_ATTRIBUTE10 IN VARCHAR2,
147 X_PROJECT_ATTRIBUTE11 IN VARCHAR2,
148 X_PROJECT_ATTRIBUTE12 IN VARCHAR2,
149 X_PROJECT_ATTRIBUTE13 IN VARCHAR2,
150 X_PROJECT_ATTRIBUTE14 IN VARCHAR2,
151 X_PROJECT_ATTRIBUTE15 IN VARCHAR2,
152 X_CREATION_DATE IN DATE,
153 X_CREATED_BY IN NUMBER,
154 X_LAST_UPDATE_DATE IN DATE,
155 X_LAST_UPDATED_BY IN NUMBER,
156 X_LAST_UPDATE_LOGIN IN NUMBER,
157 X_CALLING_FORM IN VARCHAR2 ,
158 x_org_id IN NUMBER,
159 x_lease_status IN VARCHAR2,
160 x_recoverable_flag IN VARCHAR2,
161 x_area_type_code IN VARCHAR2,
162 x_area IN NUMBER,
163 x_grouping_rule_id IN NUMBER,
164 x_term_altered_flag IN VARCHAR2,
165 x_source_code IN VARCHAR2,
166 x_term_comments IN VARCHAR2,
167 x_norm_start_date IN DATE,
168 x_parent_term_id IN NUMBER ,
169 x_index_norm_flag IN VARCHAR2,
170 x_include_in_var_rent IN VARCHAR2 DEFAULT NULL,
171 x_recur_bb_calc_date IN DATE,
172 x_opex_agr_id IN NUMBER,
173 x_opex_recon_id IN NUMBER,
174 x_opex_type IN VARCHAR2
175
176 )
177 IS
178 CURSOR c IS
179 SELECT ROWID
180 FROM pn_payment_terms_all
181 WHERE payment_term_id = x_payment_term_id;
182
183 l_legal_entity_id pn_payment_terms.legal_entity_id%TYPE;
184 l_return_status VARCHAR2(2) := NULL;
185
186 CURSOR org_cur IS
187 SELECT org_id FROM pn_leases_all WHERE lease_id = X_LEASE_ID;
188 l_org_id NUMBER;
189
190 l_precision NUMBER;
191 l_ext_precision NUMBER;
192 l_min_acct_unit NUMBER;
193
194
195 l_update_nbp_flag VARCHAR2(1);
196
197 CURSOR index_rents_csr IS
198 SELECT index_lease_id
199 ,lease_id
200 FROM pn_index_leases_all
201 WHERE lease_id = x_lease_id
202 AND GROSS_FLAG = 'Y';
203
204
205 BEGIN
206 pnp_debug_pkg.debug ('PNT_PAYMENT_TERMS_PKG.INSERT_ROW (+)');
207
208 -- Check IF both Estimated AND actual amount are NULL
209 l_return_status := NULL;
210 PNT_PAYMENT_TERMS_PKG.CHECK_PAYMENT_AMOUNTS (
211 l_RETURN_STATUS
212 ,X_ACTUAL_AMOUNT
213 ,X_ESTIMATED_AMOUNT);
214 IF (l_return_status IS NOT NULL) THEN
215 app_exception.Raise_Exception;
216 END IF;
217
218 /* checking if updation of Natural breakpoints is required */
219 IF x_include_in_var_rent IN ('BASETERM','INCLUDE_RI') THEN
220 l_update_nbp_flag := 'Y';
221 ELSE
222 l_update_nbp_flag := NULL;
223 END IF;
224
225 IF pn_r12_util_pkg.is_r12 THEN
226 IF x_vendor_site_id IS NOT NULL THEN /* PAYABLES */
227 l_legal_entity_id :=
228 pn_r12_util_pkg.get_le_for_ap(
229 p_code_combination_id => null
230 ,p_location_id => x_vendor_site_id
231 ,p_org_id => x_org_id
232 );
233 ELSE
234 l_legal_entity_id :=
235 pn_r12_util_pkg.get_le_for_ar(
236 p_customer_id => x_customer_id
237 ,p_transaction_type_id => x_cust_trx_type_id
238 ,p_org_id => x_org_id
239 );
240 END IF;
241 END IF;
242
243 IF x_org_id IS NOT NULL THEN
244 l_org_id := x_org_id;
245 ELSIF x_org_id IS NULL THEN
246 FOR rec IN org_cur LOOP
247 l_org_id := rec.org_id;
248 END LOOP;
249 END IF;
250
251 /* get currency info for rounding */
252 fnd_currency.get_info( currency_code => x_currency_code
253 ,precision => l_precision
254 ,ext_precision => l_ext_precision
255 ,min_acct_unit => l_min_acct_unit);
256
257 BEGIN
258 INSERT INTO pn_payment_terms_all
259 (
260 PAYMENT_TERM_ID,
261 LAST_UPDATE_DATE,
262 LAST_UPDATED_BY,
263 CREATION_DATE,
264 CREATED_BY,
265 LAST_UPDATE_LOGIN,
266 PAYMENT_PURPOSE_CODE,
267 PAYMENT_TERM_TYPE_CODE,
268 FREQUENCY_CODE,
269 LEASE_ID,
270 LEASE_CHANGE_ID,
271 START_DATE,
272 END_DATE,
273 VENDOR_ID,
274 VENDOR_SITE_ID,
275 CUSTOMER_ID,
276 CUSTOMER_SITE_USE_ID,
277 TARGET_DATE,
278 ACTUAL_AMOUNT,
279 ESTIMATED_AMOUNT,
280 SET_OF_BOOKS_ID,
281 CURRENCY_CODE,
282 RATE,
283 NORMALIZE,
284 LOCATION_ID,
285 SCHEDULE_DAY,
286 CUST_SHIP_SITE_ID,
287 AP_AR_TERM_ID,
288 CUST_TRX_TYPE_ID,
289 PROJECT_ID,
290 TASK_ID,
291 ORGANIZATION_ID,
292 EXPENDITURE_TYPE,
293 EXPENDITURE_ITEM_DATE,
294 TAX_GROUP_ID,
295 TAX_CODE_ID,
296 TAX_CLASSIFICATION_CODE,
297 TAX_INCLUDED,
298 DISTRIBUTION_SET_ID,
299 INV_RULE_ID,
300 ACCOUNT_RULE_ID,
301 SALESREP_ID,
302 APPROVED_BY,
303 STATUS,
304 INDEX_PERIOD_ID,
305 INDEX_TERM_INDICATOR,
306 PO_HEADER_ID,
307 CUST_PO_NUMBER,
308 RECEIPT_METHOD_ID,
309 VAR_RENT_INV_ID ,
310 VAR_RENT_TYPE,
311 PERIOD_BILLREC_ID,
312 REC_AGR_LINE_ID,
313 AMOUNT_TYPE,
314 CHANGED_FLAG,
315 TERM_TEMPLATE_ID,
316 LEGAL_ENTITY_ID,
317 ATTRIBUTE_CATEGORY,
318 ATTRIBUTE1,
319 ATTRIBUTE2,
320 ATTRIBUTE3,
321 ATTRIBUTE4,
322 ATTRIBUTE5,
323 ATTRIBUTE6,
324 ATTRIBUTE7,
325 ATTRIBUTE8,
326 ATTRIBUTE9,
327 ATTRIBUTE10,
328 ATTRIBUTE11,
329 ATTRIBUTE12,
330 ATTRIBUTE13,
331 ATTRIBUTE14,
332 ATTRIBUTE15,
333 PROJECT_ATTRIBUTE_CATEGORY,
334 PROJECT_ATTRIBUTE1,
335 PROJECT_ATTRIBUTE2,
336 PROJECT_ATTRIBUTE3,
337 PROJECT_ATTRIBUTE4,
338 PROJECT_ATTRIBUTE5,
339 PROJECT_ATTRIBUTE6,
340 PROJECT_ATTRIBUTE7,
341 PROJECT_ATTRIBUTE8,
342 PROJECT_ATTRIBUTE9,
343 PROJECT_ATTRIBUTE10,
344 PROJECT_ATTRIBUTE11,
345 PROJECT_ATTRIBUTE12,
346 PROJECT_ATTRIBUTE13,
347 PROJECT_ATTRIBUTE14,
348 PROJECT_ATTRIBUTE15,
349 ORG_ID,
350 LEASE_STATUS,
351 RECOVERABLE_FLAG,
352 AREA_TYPE_CODE,
353 AREA,
354 GROUPING_RULE_ID,
355 TERM_ALTERED_FLAG,
356 SOURCE_CODE,
357 TERM_COMMENTS,
358 NORM_START_DATE,
359 PARENT_TERM_ID,
360 INDEX_NORM_FLAG,
361 INCLUDE_IN_VAR_RENT,
362 UPDATE_NBP_FLAG,
363 RECUR_BB_CALC_DATE,
364 OPEX_AGR_ID,
365 OPEX_RECON_ID,
366 OPEX_TYPE
367 )
368 VALUES
369 (
370 NVL(X_PAYMENT_TERM_ID,pn_payment_terms_s.NEXTVAL),
371 X_LAST_UPDATE_DATE,
372 X_LAST_UPDATED_BY,
373 X_CREATION_DATE,
374 X_CREATED_BY,
375 X_LAST_UPDATE_LOGIN,
376 X_PAYMENT_PURPOSE_CODE,
377 X_PAYMENT_TERM_TYPE_CODE,
378 X_FREQUENCY_CODE,
379 X_LEASE_ID,
380 X_LEASE_CHANGE_ID,
381 X_START_DATE,
382 X_END_DATE,
383 X_VENDOR_ID,
384 X_VENDOR_SITE_ID,
385 X_CUSTOMER_ID,
386 X_CUSTOMER_SITE_USE_ID,
387 X_TARGET_DATE,
388 ROUND(X_ACTUAL_AMOUNT, l_precision),
389 ROUND(X_ESTIMATED_AMOUNT, l_precision),
390 X_SET_OF_BOOKS_ID,
391 X_CURRENCY_CODE,
392 X_RATE,
393 X_NORMALIZE,
394 X_LOCATION_ID,
395 X_SCHEDULE_DAY,
396 X_CUST_SHIP_SITE_ID,
397 X_AP_AR_TERM_ID,
398 X_CUST_TRX_TYPE_ID,
399 X_PROJECT_ID,
400 X_TASK_ID,
401 X_ORGANIZATION_ID,
402 X_EXPENDITURE_TYPE,
403 X_EXPENDITURE_ITEM_DATE,
404 X_TAX_GROUP_ID,
405 X_TAX_CODE_ID,
406 X_TAX_CLASSIFICATION_CODE,
407 X_TAX_INCLUDED,
408 X_DISTRIBUTION_SET_ID,
409 X_INV_RULE_ID,
410 X_ACCOUNT_RULE_ID,
411 X_SALESREP_ID,
412 X_APPROVED_BY,
413 X_STATUS,
414 X_INDEX_PERIOD_ID,
415 X_INDEX_TERM_INDICATOR,
416 X_PO_HEADER_ID,
417 X_CUST_PO_NUMBER,
418 X_RECEIPT_METHOD_ID,
419 X_VAR_RENT_INV_ID,
420 X_VAR_RENT_TYPE,
421 X_PERIOD_BILLREC_ID,
422 X_REC_AGR_LINE_ID,
423 X_AMOUNT_TYPE,
424 X_CHANGED_FLAG,
425 X_TERM_TEMPLATE_ID,
426 l_legal_entity_id,
427 X_ATTRIBUTE_CATEGORY,
428 X_ATTRIBUTE1,
429 X_ATTRIBUTE2,
430 X_ATTRIBUTE3,
431 X_ATTRIBUTE4,
432 X_ATTRIBUTE5,
433 X_ATTRIBUTE6,
434 X_ATTRIBUTE7,
435 X_ATTRIBUTE8,
436 X_ATTRIBUTE9,
437 X_ATTRIBUTE10,
438 X_ATTRIBUTE11,
439 X_ATTRIBUTE12,
440 X_ATTRIBUTE13,
441 X_ATTRIBUTE14,
442 X_ATTRIBUTE15,
443 X_PROJECT_ATTRIBUTE_CATEGORY,
444 X_PROJECT_ATTRIBUTE1,
445 X_PROJECT_ATTRIBUTE2,
446 X_PROJECT_ATTRIBUTE3,
447 X_PROJECT_ATTRIBUTE4,
448 X_PROJECT_ATTRIBUTE5,
449 X_PROJECT_ATTRIBUTE6,
450 X_PROJECT_ATTRIBUTE7,
451 X_PROJECT_ATTRIBUTE8,
452 X_PROJECT_ATTRIBUTE9,
453 X_PROJECT_ATTRIBUTE10,
454 X_PROJECT_ATTRIBUTE11,
455 X_PROJECT_ATTRIBUTE12,
456 X_PROJECT_ATTRIBUTE13,
457 X_PROJECT_ATTRIBUTE14,
458 X_PROJECT_ATTRIBUTE15,
459 L_ORG_ID,
460 X_LEASE_STATUS,
461 X_RECOVERABLE_FLAG,
462 X_AREA_TYPE_CODE,
463 X_AREA,
464 X_GROUPING_RULE_ID,
465 X_TERM_ALTERED_FLAG,
466 X_SOURCE_CODE,
467 X_TERM_COMMENTS,
468 X_NORM_START_DATE,
469 X_PARENT_TERM_ID,
470 X_INDEX_NORM_FLAG,
471 X_INCLUDE_IN_VAR_RENT,
472 l_update_nbp_flag,
473 X_RECUR_BB_CALC_DATE,
474 X_OPEX_AGR_ID,
475 X_OPEX_RECON_ID,
476 X_OPEX_TYPE
477 )
478 RETURNING payment_term_id INTO X_PAYMENT_TERM_ID;
479
480 EXCEPTION
481 WHEN OTHERS THEN
482 NULL;
483 END;
484
485 OPEN c;
486 FETCH c INTO x_rowid;
487 IF (c%NOTFOUND) THEN
488 CLOSE c;
489 RAISE NO_DATA_FOUND;
490 END IF;
491 CLOSE c;
492
493 pnp_debug_pkg.debug ('PNT_PAYMENT_TERMS_PKG.INSERT_ROW (-)');
494 END insert_row;
495
496 -------------------------------------------------------------------------------
497 -- PROCDURE : LOCK_ROW
498 -- INVOKED FROM : LOCK_ROW procedure
499 -- PURPOSE : locks the row
500 -- HISTORY :
501 -- 04-DEC-03 ftanudja o Added parameter area_type_code, area. 3257508.
502 -- 15-JUL-05 ftanudja o R12 changes. #4495054, #4497295
503 -- 14-SEP-06 sdmahesh o Bug # 5525354
504 -- Modified the check for tax_included for expense side
505 -- upgraded terms
506 -- 02-JAn-07 Hareesha o M28#16 Added X_RECUR_BB_CALC_DATE
507 -- 06-MAR-07 Prabhakar o Added opex_agr_id, opex_recon_id and opex_type
508 -- for lock row handlers.
509 -------------------------------------------------------------------------------
510 PROCEDURE LOCK_ROW (
511 X_PAYMENT_TERM_ID IN NUMBER,
512 X_PAYMENT_PURPOSE_CODE IN VARCHAR2,
513 X_PAYMENT_TERM_TYPE_CODE IN VARCHAR2,
514 X_FREQUENCY_CODE IN VARCHAR2,
515 X_LEASE_ID IN NUMBER,
516 X_LEASE_CHANGE_ID IN NUMBER,
517 X_START_DATE IN DATE,
518 X_END_DATE IN DATE,
519 X_VENDOR_ID IN NUMBER,
520 X_VENDOR_SITE_ID IN NUMBER,
521 X_CUSTOMER_ID IN NUMBER,
522 X_CUSTOMER_SITE_USE_ID IN NUMBER,
523 X_TARGET_DATE IN DATE,
524 X_ACTUAL_AMOUNT IN NUMBER,
525 X_ESTIMATED_AMOUNT IN NUMBER,
526 X_SET_OF_BOOKS_ID IN NUMBER,
527 X_CURRENCY_CODE IN VARCHAR2,
528 X_RATE IN NUMBER,
529 X_NORMALIZE IN VARCHAR2,
530 X_LOCATION_ID IN NUMBER,
531 X_SCHEDULE_DAY IN NUMBER,
532 X_CUST_SHIP_SITE_ID IN NUMBER,
533 X_AP_AR_TERM_ID IN NUMBER,
534 X_CUST_TRX_TYPE_ID IN NUMBER,
535 X_PROJECT_ID IN NUMBER,
536 X_TASK_ID IN NUMBER,
537 X_ORGANIZATION_ID IN NUMBER,
538 X_EXPENDITURE_TYPE IN VARCHAR2,
539 X_EXPENDITURE_ITEM_DATE IN DATE,
540 X_TAX_GROUP_ID IN NUMBER,
541 X_TAX_CODE_ID IN NUMBER,
542 X_TAX_CLASSIFICATION_CODE IN VARCHAR2,
543 X_TAX_INCLUDED IN VARCHAR2,
544 X_DISTRIBUTION_SET_ID IN NUMBER,
545 X_INV_RULE_ID IN NUMBER,
546 X_ACCOUNT_RULE_ID IN NUMBER,
547 X_SALESREP_ID IN NUMBER,
548 X_APPROVED_BY IN NUMBER,
549 X_STATUS IN VARCHAR2,
550 X_INDEX_PERIOD_ID IN NUMBER,
551 X_INDEX_TERM_INDICATOR IN VARCHAR2,
552 X_PO_HEADER_ID IN NUMBER,
553 X_CUST_PO_NUMBER IN VARCHAR2,
554 X_RECEIPT_METHOD_ID IN NUMBER,
555 X_VAR_RENT_INV_ID IN NUMBER ,
556 X_VAR_RENT_TYPE IN VARCHAR2,
557 X_CHANGED_FLAG IN VARCHAR2,
558 X_ATTRIBUTE_CATEGORY IN VARCHAR2,
559 X_ATTRIBUTE1 IN VARCHAR2,
560 X_ATTRIBUTE2 IN VARCHAR2,
561 X_ATTRIBUTE3 IN VARCHAR2,
562 X_ATTRIBUTE4 IN VARCHAR2,
563 X_ATTRIBUTE5 IN VARCHAR2,
564 X_ATTRIBUTE6 IN VARCHAR2,
565 X_ATTRIBUTE7 IN VARCHAR2,
566 X_ATTRIBUTE8 IN VARCHAR2,
567 X_ATTRIBUTE9 IN VARCHAR2,
568 X_ATTRIBUTE10 IN VARCHAR2,
569 X_ATTRIBUTE11 IN VARCHAR2,
570 X_ATTRIBUTE12 IN VARCHAR2,
571 X_ATTRIBUTE13 IN VARCHAR2,
572 X_ATTRIBUTE14 IN VARCHAR2,
573 X_ATTRIBUTE15 IN VARCHAR2,
574 X_PROJECT_ATTRIBUTE_CATEGORY IN VARCHAR2,
575 X_PROJECT_ATTRIBUTE1 IN VARCHAR2,
576 X_PROJECT_ATTRIBUTE2 IN VARCHAR2,
577 X_PROJECT_ATTRIBUTE3 IN VARCHAR2,
578 X_PROJECT_ATTRIBUTE4 IN VARCHAR2,
579 X_PROJECT_ATTRIBUTE5 IN VARCHAR2,
580 X_PROJECT_ATTRIBUTE6 IN VARCHAR2,
581 X_PROJECT_ATTRIBUTE7 IN VARCHAR2,
582 X_PROJECT_ATTRIBUTE8 IN VARCHAR2,
583 X_PROJECT_ATTRIBUTE9 IN VARCHAR2,
584 X_PROJECT_ATTRIBUTE10 IN VARCHAR2,
585 X_PROJECT_ATTRIBUTE11 IN VARCHAR2,
586 X_PROJECT_ATTRIBUTE12 IN VARCHAR2,
587 X_PROJECT_ATTRIBUTE13 IN VARCHAR2,
588 X_PROJECT_ATTRIBUTE14 IN VARCHAR2,
589 X_PROJECT_ATTRIBUTE15 IN VARCHAR2,
590 x_recoverable_flag IN VARCHAR2,
591 x_area_type_code IN VARCHAR2,
592 x_area IN NUMBER,
593 x_grouping_rule_id IN NUMBER,
594 x_term_comments IN VARCHAR2,
595 X_TERM_TEMPLATE_ID IN NUMBER,
596 x_include_in_var_rent IN VARCHAR2 DEFAULT NULL,
597 X_RECUR_BB_CALC_DATE IN DATE,
598 x_opex_agr_id IN NUMBER,
599 x_opex_recon_id IN NUMBER,
600 x_opex_type IN VARCHAR2
601
602 )
603 IS
604 CURSOR c1 IS
605 SELECT *
606 FROM PN_PAYMENT_TERMS_ALL
607 WHERE PAYMENT_TERM_ID = X_PAYMENT_TERM_ID
608 FOR UPDATE OF payment_term_id NOWAIT;
609
610 --tlinfo c1%rowtype;
611
612 BEGIN
613 pnp_debug_pkg.debug ('PNT_PAYMENT_TERMS_PKG.LOCK_ROW (+)');
614
615 OPEN c1;
616 FETCH c1 INTO tlinfo;
617 IF (c1%NOTFOUND) THEN
618 CLOSE c1;
619 RETURN;
620 END IF;
621 CLOSE c1;
622
623 IF NOT (tlinfo.PAYMENT_TERM_ID = X_PAYMENT_TERM_ID) THEN
624 pn_var_rent_pkg.lock_row_exception('PAYMENT_TERM_ID',tlinfo.PAYMENT_TERM_ID);
625 END IF;
626
627 IF NOT (tlinfo.PAYMENT_PURPOSE_CODE = X_PAYMENT_PURPOSE_CODE) THEN
628 pn_var_rent_pkg.lock_row_exception('PAYMENT_PURPOSE_CODE',tlinfo.PAYMENT_PURPOSE_CODE);
629 END IF;
630
631 IF NOT (tlinfo.PAYMENT_TERM_TYPE_CODE = X_PAYMENT_TERM_TYPE_CODE) THEN
632 pn_var_rent_pkg.lock_row_exception('PAYMENT_TERM_TYPE_CODE',tlinfo.PAYMENT_TERM_TYPE_CODE);
633 END IF;
634
635 IF NOT (tlinfo.FREQUENCY_CODE = X_FREQUENCY_CODE) THEN
636 pn_var_rent_pkg.lock_row_exception('FREQUENCY_CODE',tlinfo.FREQUENCY_CODE);
637 END IF;
638
639 IF NOT (tlinfo.LEASE_ID = X_LEASE_ID) THEN
640 pn_var_rent_pkg.lock_row_exception('LEASE_ID',tlinfo.LEASE_ID);
641 END IF;
642
643 IF NOT (tlinfo.LEASE_CHANGE_ID = X_LEASE_CHANGE_ID) THEN
644 pn_var_rent_pkg.lock_row_exception('LEASE_CHANGE_ID',tlinfo.LEASE_CHANGE_ID);
645 END IF;
646
647 IF NOT (tlinfo.START_DATE = X_START_DATE) THEN
648 pn_var_rent_pkg.lock_row_exception('START_DATE',tlinfo.START_DATE);
649 END IF;
650
651 IF NOT (tlinfo.END_DATE = X_END_DATE) THEN
652 pn_var_rent_pkg.lock_row_exception('END_DATE',tlinfo.END_DATE);
653 END IF;
654
655 IF NOT ((tlinfo.VENDOR_ID = X_VENDOR_ID)
656 OR ((tlinfo.VENDOR_ID IS NULL) AND (X_VENDOR_ID IS NULL))) THEN
657 pn_var_rent_pkg.lock_row_exception('VENDOR_ID',tlinfo.VENDOR_ID);
658 END IF;
659
660 IF NOT ((tlinfo.VENDOR_SITE_ID = X_VENDOR_SITE_ID)
661 OR ((tlinfo.VENDOR_SITE_ID IS NULL) AND (X_VENDOR_SITE_ID IS NULL))) THEN
662 pn_var_rent_pkg.lock_row_exception('VENDOR_SITE_ID',tlinfo.VENDOR_SITE_ID);
663 END IF;
664
665 IF NOT ((tlinfo.CUSTOMER_ID = X_CUSTOMER_ID)
666 OR ((tlinfo.CUSTOMER_ID IS NULL) AND (X_CUSTOMER_ID IS NULL))) THEN
667 pn_var_rent_pkg.lock_row_exception('CUSTOMER_ID',tlinfo.CUSTOMER_ID);
668 END IF;
669
670 IF NOT ((tlinfo.CUSTOMER_SITE_USE_ID = X_CUSTOMER_SITE_USE_ID)
671 OR ((tlinfo.CUSTOMER_SITE_USE_ID IS NULL) AND (X_CUSTOMER_SITE_USE_ID IS NULL))) THEN
672 pn_var_rent_pkg.lock_row_exception('CUSTOMER_SITE_USE_ID',tlinfo.CUSTOMER_SITE_USE_ID);
673 END IF;
674
675 IF NOT ((tlinfo.TARGET_DATE = X_TARGET_DATE)
676 OR ((tlinfo.TARGET_DATE IS NULL) AND (X_TARGET_DATE IS NULL))) THEN
677 pn_var_rent_pkg.lock_row_exception('TARGET_DATE',tlinfo.TARGET_DATE);
678 END IF;
679
680 IF NOT ((tlinfo.ACTUAL_AMOUNT = X_ACTUAL_AMOUNT)
681 OR ((tlinfo.ACTUAL_AMOUNT IS NULL) AND (X_ACTUAL_AMOUNT IS NULL))) THEN
682 pn_var_rent_pkg.lock_row_exception('ACTUAL_AMOUNT',tlinfo.ACTUAL_AMOUNT);
683 END IF;
684
685 IF NOT ((tlinfo.ESTIMATED_AMOUNT = X_ESTIMATED_AMOUNT)
686 OR ((tlinfo.ESTIMATED_AMOUNT IS NULL) AND (X_ESTIMATED_AMOUNT IS NULL))) THEN
687 pn_var_rent_pkg.lock_row_exception('ESTIMATED_AMOUNT',tlinfo.ESTIMATED_AMOUNT);
688 END IF;
689
690 IF NOT (tlinfo.SET_OF_BOOKS_ID = X_SET_OF_BOOKS_ID) THEN
691 pn_var_rent_pkg.lock_row_exception('SET_OF_BOOKS_ID',tlinfo.SET_OF_BOOKS_ID);
692 END IF;
693
694 IF NOT (tlinfo.CURRENCY_CODE = X_CURRENCY_CODE) THEN
695 pn_var_rent_pkg.lock_row_exception('CURRENCY_CODE',tlinfo.CURRENCY_CODE);
696 END IF;
697
698 IF NOT ((tlinfo.RATE = X_RATE)
699 OR ((tlinfo.RATE IS NULL) AND (X_RATE IS NULL))) THEN
700 pn_var_rent_pkg.lock_row_exception('RATE',tlinfo.RATE);
701 END IF;
702
703 IF NOT ((tlinfo.NORMALIZE = X_NORMALIZE)
704 OR ((tlinfo.NORMALIZE IS NULL) AND (X_NORMALIZE IS NULL))) THEN
705 pn_var_rent_pkg.lock_row_exception('NORMALIZE',tlinfo.NORMALIZE);
706 END IF;
707
708 IF NOT ((tlinfo.LOCATION_ID = X_LOCATION_ID)
709 OR ((tlinfo.LOCATION_ID IS NULL) AND (X_LOCATION_ID IS NULL))) THEN
710 pn_var_rent_pkg.lock_row_exception('LOCATION_ID',tlinfo.LOCATION_ID);
711 END IF;
712
713 IF NOT ((tlinfo.SCHEDULE_DAY = X_SCHEDULE_DAY)
714 OR ((tlinfo.SCHEDULE_DAY IS NULL) AND (X_SCHEDULE_DAY IS NULL))) THEN
715 pn_var_rent_pkg.lock_row_exception('SCHEDULE_DAY',tlinfo.SCHEDULE_DAY);
716 END IF;
717
718 IF NOT ((tlinfo.CUST_SHIP_SITE_ID = X_CUST_SHIP_SITE_ID)
719 OR ((tlinfo.CUST_SHIP_SITE_ID IS NULL) AND (X_CUST_SHIP_SITE_ID IS NULL))) THEN
720 pn_var_rent_pkg.lock_row_exception('CUST_SHIP_SITE_ID',tlinfo.CUST_SHIP_SITE_ID);
721 END IF;
722
723 IF NOT ((tlinfo.AP_AR_TERM_ID = X_AP_AR_TERM_ID)
724 OR ((tlinfo.AP_AR_TERM_ID IS NULL) AND (X_AP_AR_TERM_ID IS NULL))) THEN
725 pn_var_rent_pkg.lock_row_exception('AP_AR_TERM_ID',tlinfo.AP_AR_TERM_ID);
726 END IF;
727
728 IF NOT ((tlinfo.CUST_TRX_TYPE_ID = X_CUST_TRX_TYPE_ID)
729 OR ((tlinfo.CUST_TRX_TYPE_ID IS NULL) AND (X_CUST_TRX_TYPE_ID IS NULL))) THEN
730 pn_var_rent_pkg.lock_row_exception('CUST_TRX_TYPE_ID',tlinfo.CUST_TRX_TYPE_ID);
731 END IF;
732
733 IF NOT ((tlinfo.PROJECT_ID = X_PROJECT_ID)
734 OR ((tlinfo.PROJECT_ID IS NULL) AND (X_PROJECT_ID IS NULL))) THEN
735 pn_var_rent_pkg.lock_row_exception('PROJECT_ID',tlinfo.PROJECT_ID);
736 END IF;
737
738 IF NOT ((tlinfo.TASK_ID = X_TASK_ID)
739 OR ((tlinfo.TASK_ID IS NULL) AND (X_TASK_ID IS NULL))) THEN
740 pn_var_rent_pkg.lock_row_exception('TASK_ID',tlinfo.TASK_ID);
741 END IF;
742
743 IF NOT ((tlinfo.ORGANIZATION_ID = X_ORGANIZATION_ID)
744 OR ((tlinfo.ORGANIZATION_ID IS NULL) AND (X_ORGANIZATION_ID IS NULL))) THEN
745 pn_var_rent_pkg.lock_row_exception('ORGANIZATION_ID',tlinfo.ORGANIZATION_ID);
746 END IF;
747
748 IF NOT ((tlinfo.EXPENDITURE_TYPE = X_EXPENDITURE_TYPE)
749 OR ((tlinfo.EXPENDITURE_TYPE IS NULL) AND (X_EXPENDITURE_TYPE IS NULL))) THEN
750 pn_var_rent_pkg.lock_row_exception('EXPENDITURE_TYPE',tlinfo.EXPENDITURE_TYPE);
751 END IF;
752
753 IF NOT ((tlinfo.EXPENDITURE_ITEM_DATE = X_EXPENDITURE_ITEM_DATE)
754 OR ((tlinfo.EXPENDITURE_ITEM_DATE IS NULL) AND (X_EXPENDITURE_ITEM_DATE IS NULL))) THEN
755 pn_var_rent_pkg.lock_row_exception('EXPENDITURE_ITEM_DATE',tlinfo.EXPENDITURE_ITEM_DATE);
756 END IF;
757
758 IF pn_r12_util_pkg.is_r12 AND
759 NOT ((tlinfo.TAX_CLASSIFICATION_CODE = X_TAX_CLASSIFICATION_CODE)
760 OR ((tlinfo.TAX_CLASSIFICATION_CODE IS NULL) AND (X_TAX_CLASSIFICATION_CODE IS NULL))) THEN
761 pn_var_rent_pkg.lock_row_exception('TAX_GROUP_ID',tlinfo.TAX_CLASSIFICATION_CODE);
762 END IF;
763
764 IF NOT pn_r12_util_pkg.is_r12 AND
765 NOT ((tlinfo.TAX_GROUP_ID = X_TAX_GROUP_ID)
766 OR ((tlinfo.TAX_GROUP_ID IS NULL) AND (X_TAX_GROUP_ID IS NULL))) THEN
767 pn_var_rent_pkg.lock_row_exception('TAX_GROUP_ID',tlinfo.TAX_GROUP_ID);
768 END IF;
769
770 IF NOT pn_r12_util_pkg.is_r12 AND
771 NOT ((tlinfo.TAX_CODE_ID = X_TAX_CODE_ID)
772 OR ((tlinfo.TAX_CODE_ID IS NULL) AND (X_TAX_CODE_ID IS NULL))) THEN
773 pn_var_rent_pkg.lock_row_exception('TAX_CODE_ID',tlinfo.TAX_CODE_ID);
774 END IF;
775
776 IF ((tlinfo.TAX_INCLUDED = 'Y') AND
777 (tlinfo.VENDOR_SITE_ID IS NOT NULL) AND
778 (pn_r12_util_pkg.is_r12)) THEN
779
780 IF X_TAX_INCLUDED <> 'A' THEN
781
782 pn_var_rent_pkg.lock_row_exception('TAX_INCLUDED',tlinfo.TAX_INCLUDED);
783
784 END IF;
785
786 ELSIF NOT ((tlinfo.TAX_INCLUDED = X_TAX_INCLUDED)
787 OR ((tlinfo.TAX_INCLUDED IS NULL) AND (X_TAX_INCLUDED IS NULL))) THEN
788 pn_var_rent_pkg.lock_row_exception('TAX_INCLUDED',tlinfo.TAX_INCLUDED);
789
790 END IF;
791
792 IF NOT ((tlinfo.DISTRIBUTION_SET_ID = X_DISTRIBUTION_SET_ID)
793 OR ((tlinfo.DISTRIBUTION_SET_ID IS NULL) AND (X_DISTRIBUTION_SET_ID IS NULL))) THEN
794 pn_var_rent_pkg.lock_row_exception('DISTRIBUTION_SET_ID',tlinfo.DISTRIBUTION_SET_ID);
795 END IF;
796
797 IF NOT ((tlinfo.INV_RULE_ID = X_INV_RULE_ID)
798 OR ((tlinfo.INV_RULE_ID IS NULL) AND (X_INV_RULE_ID IS NULL))) THEN
799 pn_var_rent_pkg.lock_row_exception('INV_RULE_ID',tlinfo.INV_RULE_ID);
800 END IF;
801
802 IF NOT ((tlinfo.ACCOUNT_RULE_ID = X_ACCOUNT_RULE_ID)
803 OR ((tlinfo.ACCOUNT_RULE_ID IS NULL) AND (X_ACCOUNT_RULE_ID IS NULL))) THEN
804 pn_var_rent_pkg.lock_row_exception('ACCOUNT_RULE_ID',tlinfo.ACCOUNT_RULE_ID);
805 END IF;
806
807 IF NOT ((tlinfo.SALESREP_ID = X_SALESREP_ID)
808 OR ((tlinfo.SALESREP_ID IS NULL) AND (X_SALESREP_ID IS NULL))) THEN
809 pn_var_rent_pkg.lock_row_exception('SALESREP_ID',tlinfo.SALESREP_ID);
810 END IF;
811
812 IF NOT ((tlinfo.APPROVED_BY = X_APPROVED_BY)
813 OR ((tlinfo.APPROVED_BY IS NULL) AND (X_APPROVED_BY IS NULL))) THEN
814 pn_var_rent_pkg.lock_row_exception('APPROVED_BY',tlinfo.APPROVED_BY);
815 END IF;
816
817 IF NOT ((tlinfo.STATUS = X_STATUS)
818 OR ((tlinfo.STATUS IS NULL) AND (X_STATUS IS NULL))) THEN
819 pn_var_rent_pkg.lock_row_exception('STATUS',tlinfo.STATUS);
820 END IF;
821
822 IF NOT ((tlinfo.INDEX_PERIOD_ID = X_INDEX_PERIOD_ID)
823 OR ((tlinfo.INDEX_PERIOD_ID IS NULL) AND (X_INDEX_PERIOD_ID IS NULL))) THEN
824 pn_var_rent_pkg.lock_row_exception('INDEX_PERIOD_ID',tlinfo.INDEX_PERIOD_ID);
825 END IF;
826
827 IF NOT ((tlinfo.INDEX_TERM_INDICATOR = X_INDEX_TERM_INDICATOR)
828 OR ((tlinfo.INDEX_TERM_INDICATOR IS NULL) AND (X_INDEX_TERM_INDICATOR IS NULL))) THEN
829 pn_var_rent_pkg.lock_row_exception('INDEX_TERM_INDICATOR',tlinfo.INDEX_TERM_INDICATOR);
830 END IF;
831
832 IF NOT ((tlinfo.PO_HEADER_ID = X_PO_HEADER_ID)
833 OR ((tlinfo.PO_HEADER_ID IS NULL) AND (X_PO_HEADER_ID IS NULL))) THEN
834 pn_var_rent_pkg.lock_row_exception('PO_HEADER_ID',tlinfo.PO_HEADER_ID);
835 END IF;
836
837 IF NOT ((tlinfo.CUST_PO_NUMBER = X_CUST_PO_NUMBER)
838 OR ((tlinfo.CUST_PO_NUMBER IS NULL) AND (X_CUST_PO_NUMBER IS NULL))) THEN
839 pn_var_rent_pkg.lock_row_exception('CUST_PO_NUMBER',tlinfo.CUST_PO_NUMBER);
840 END IF;
841
842 IF NOT ((tlinfo.RECEIPT_METHOD_ID = X_RECEIPT_METHOD_ID)
843 OR ((tlinfo.RECEIPT_METHOD_ID IS NULL) AND (X_RECEIPT_METHOD_ID IS NULL))) THEN
844 pn_var_rent_pkg.lock_row_exception('RECEIPT_METHOD_ID',tlinfo.RECEIPT_METHOD_ID);
845 END IF;
846
847 IF NOT ((tlinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
848 OR ((tlinfo.ATTRIBUTE_CATEGORY IS NULL) AND (X_ATTRIBUTE_CATEGORY IS NULL))) THEN
849 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE_CATEGORY',tlinfo.ATTRIBUTE_CATEGORY);
850 END IF;
851
852 IF NOT ((tlinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
853 OR ((tlinfo.ATTRIBUTE1 IS NULL) AND (X_ATTRIBUTE1 IS NULL))) THEN
854 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE1',tlinfo.ATTRIBUTE1);
855 END IF;
856
857 IF NOT ((tlinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
858 OR ((tlinfo.ATTRIBUTE2 IS NULL) AND (X_ATTRIBUTE2 IS NULL))) THEN
859 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE2',tlinfo.ATTRIBUTE2);
860 END IF;
861
862 IF NOT ((tlinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
863 OR ((tlinfo.ATTRIBUTE3 IS NULL) AND (X_ATTRIBUTE3 IS NULL))) THEN
864 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE3',tlinfo.ATTRIBUTE3);
865 END IF;
866
867 IF NOT ((tlinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
868 OR ((tlinfo.ATTRIBUTE4 IS NULL) AND (X_ATTRIBUTE4 IS NULL))) THEN
869 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE4',tlinfo.ATTRIBUTE4);
870 END IF;
871
872 IF NOT ((tlinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
873 OR ((tlinfo.ATTRIBUTE5 IS NULL) AND (X_ATTRIBUTE5 IS NULL))) THEN
874 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE5',tlinfo.ATTRIBUTE5);
875 END IF;
876
877 IF NOT ((tlinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
878 OR ((tlinfo.ATTRIBUTE6 IS NULL) AND (X_ATTRIBUTE6 IS NULL))) THEN
879 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE6',tlinfo.ATTRIBUTE6);
880 END IF;
881
882 IF NOT ((tlinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
883 OR ((tlinfo.ATTRIBUTE7 IS NULL) AND (X_ATTRIBUTE7 IS NULL))) THEN
884 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE7',tlinfo.ATTRIBUTE7);
885 END IF;
886
887 IF NOT ((tlinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
888 OR ((tlinfo.ATTRIBUTE8 IS NULL) AND (X_ATTRIBUTE8 IS NULL))) THEN
889 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE8',tlinfo.ATTRIBUTE8);
890 END IF;
891
892 IF NOT ((tlinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
893 OR ((tlinfo.ATTRIBUTE9 IS NULL) AND (X_ATTRIBUTE9 IS NULL))) THEN
894 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE9',tlinfo.ATTRIBUTE9);
895 END IF;
896
897 IF NOT ((tlinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
898 OR ((tlinfo.ATTRIBUTE10 IS NULL) AND (X_ATTRIBUTE10 IS NULL))) THEN
899 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE10',tlinfo.ATTRIBUTE10);
900 END IF;
901
902 IF NOT ((tlinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
903 OR ((tlinfo.ATTRIBUTE11 IS NULL) AND (X_ATTRIBUTE11 IS NULL))) THEN
904 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE11',tlinfo.ATTRIBUTE11);
905 END IF;
906
907 IF NOT ((tlinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
908 OR ((tlinfo.ATTRIBUTE12 IS NULL) AND (X_ATTRIBUTE12 IS NULL))) THEN
909 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE12',tlinfo.ATTRIBUTE12);
910 END IF;
911
912 IF NOT ((tlinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
913 OR ((tlinfo.ATTRIBUTE13 IS NULL) AND (X_ATTRIBUTE13 IS NULL))) THEN
914 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE13',tlinfo.ATTRIBUTE13);
915 END IF;
916
917 IF NOT ((tlinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
918 OR ((tlinfo.ATTRIBUTE14 IS NULL) AND (X_ATTRIBUTE14 IS NULL))) THEN
919 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE14',tlinfo.ATTRIBUTE14);
920 END IF;
921
922 IF NOT ((tlinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
923 OR ((tlinfo.ATTRIBUTE15 IS NULL) AND (X_ATTRIBUTE15 IS NULL))) THEN
924 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE15',tlinfo.ATTRIBUTE15);
925 END IF;
926
927 IF NOT ((tlinfo.PROJECT_ATTRIBUTE_CATEGORY = X_PROJECT_ATTRIBUTE_CATEGORY)
928 OR ((tlinfo.PROJECT_ATTRIBUTE_CATEGORY IS NULL) AND (X_PROJECT_ATTRIBUTE_CATEGORY IS NULL))) THEN
929 pn_var_rent_pkg.lock_row_exception('PROJECT_ATTRIBUTE_CATEGORY',tlinfo.PROJECT_ATTRIBUTE_CATEGORY);
930 END IF;
931
932 IF NOT ((tlinfo.PROJECT_ATTRIBUTE1 = X_PROJECT_ATTRIBUTE1)
933 OR ((tlinfo.PROJECT_ATTRIBUTE1 IS NULL) AND (X_PROJECT_ATTRIBUTE1 IS NULL))) THEN
934 pn_var_rent_pkg.lock_row_exception('PROJECT_ATTRIBUTE1',tlinfo.PROJECT_ATTRIBUTE1);
935 END IF;
936
937 IF NOT ((tlinfo.PROJECT_ATTRIBUTE2 = X_PROJECT_ATTRIBUTE2)
938 OR ((tlinfo.PROJECT_ATTRIBUTE2 IS NULL) AND (X_PROJECT_ATTRIBUTE2 IS NULL))) THEN
939 pn_var_rent_pkg.lock_row_exception('PROJECT_ATTRIBUTE2',tlinfo.PROJECT_ATTRIBUTE2);
940 END IF;
941
942 IF NOT ((tlinfo.PROJECT_ATTRIBUTE3 = X_PROJECT_ATTRIBUTE3)
943 OR ((tlinfo.PROJECT_ATTRIBUTE3 IS NULL) AND (X_PROJECT_ATTRIBUTE3 IS NULL))) THEN
944 pn_var_rent_pkg.lock_row_exception('PROJECT_ATTRIBUTE3',tlinfo.PROJECT_ATTRIBUTE3);
945 END IF;
946
947 IF NOT ((tlinfo.PROJECT_ATTRIBUTE4 = X_PROJECT_ATTRIBUTE4)
948 OR ((tlinfo.PROJECT_ATTRIBUTE4 IS NULL) AND (X_PROJECT_ATTRIBUTE4 IS NULL))) THEN
949 pn_var_rent_pkg.lock_row_exception('PROJECT_ATTRIBUTE4',tlinfo.PROJECT_ATTRIBUTE4);
950 END IF;
951
952 IF NOT ((tlinfo.PROJECT_ATTRIBUTE5 = X_PROJECT_ATTRIBUTE5)
953 OR ((tlinfo.PROJECT_ATTRIBUTE5 IS NULL) AND (X_PROJECT_ATTRIBUTE5 IS NULL))) THEN
954 pn_var_rent_pkg.lock_row_exception('PROJECT_ATTRIBUTE5',tlinfo.PROJECT_ATTRIBUTE5);
955 END IF;
956
957 IF NOT ((tlinfo.PROJECT_ATTRIBUTE6 = X_PROJECT_ATTRIBUTE6)
958 OR ((tlinfo.PROJECT_ATTRIBUTE6 IS NULL) AND (X_PROJECT_ATTRIBUTE6 IS NULL))) THEN
959 pn_var_rent_pkg.lock_row_exception('PROJECT_ATTRIBUTE6',tlinfo.PROJECT_ATTRIBUTE6);
960 END IF;
961
962 IF NOT ((tlinfo.PROJECT_ATTRIBUTE7 = X_PROJECT_ATTRIBUTE7)
963 OR ((tlinfo.PROJECT_ATTRIBUTE7 IS NULL) AND (X_PROJECT_ATTRIBUTE7 IS NULL))) THEN
964 pn_var_rent_pkg.lock_row_exception('PROJECT_ATTRIBUTE7',tlinfo.PROJECT_ATTRIBUTE7);
965 END IF;
966
967 IF NOT ((tlinfo.PROJECT_ATTRIBUTE8 = X_PROJECT_ATTRIBUTE8)
968 OR ((tlinfo.PROJECT_ATTRIBUTE8 IS NULL) AND (X_PROJECT_ATTRIBUTE8 IS NULL))) THEN
969 pn_var_rent_pkg.lock_row_exception('PROJECT_ATTRIBUTE8',tlinfo.PROJECT_ATTRIBUTE8);
970 END IF;
971
972 IF NOT ((tlinfo.PROJECT_ATTRIBUTE9 = X_PROJECT_ATTRIBUTE9)
973 OR ((tlinfo.PROJECT_ATTRIBUTE9 IS NULL) AND (X_PROJECT_ATTRIBUTE9 IS NULL))) THEN
974 pn_var_rent_pkg.lock_row_exception('PROJECT_ATTRIBUTE9',tlinfo.PROJECT_ATTRIBUTE9);
975 END IF;
976
977 IF NOT ((tlinfo.PROJECT_ATTRIBUTE10 = X_PROJECT_ATTRIBUTE10)
978 OR ((tlinfo.PROJECT_ATTRIBUTE10 IS NULL) AND (X_PROJECT_ATTRIBUTE10 IS NULL))) THEN
979 pn_var_rent_pkg.lock_row_exception('PROJECT_ATTRIBUTE10',tlinfo.PROJECT_ATTRIBUTE10);
980 END IF;
981
982 IF NOT ((tlinfo.PROJECT_ATTRIBUTE11 = X_PROJECT_ATTRIBUTE11)
983 OR ((tlinfo.PROJECT_ATTRIBUTE11 IS NULL) AND (X_PROJECT_ATTRIBUTE11 IS NULL))) THEN
984 pn_var_rent_pkg.lock_row_exception('PROJECT_ATTRIBUTE11',tlinfo.PROJECT_ATTRIBUTE11);
985 END IF;
986
987 IF NOT ((tlinfo.PROJECT_ATTRIBUTE12 = X_PROJECT_ATTRIBUTE12)
988 OR ((tlinfo.PROJECT_ATTRIBUTE12 IS NULL) AND (X_PROJECT_ATTRIBUTE12 IS NULL))) THEN
989 pn_var_rent_pkg.lock_row_exception('PROJECT_ATTRIBUTE12',tlinfo.PROJECT_ATTRIBUTE12);
990 END IF;
991
992 IF NOT ((tlinfo.PROJECT_ATTRIBUTE13 = X_PROJECT_ATTRIBUTE13)
993 OR ((tlinfo.PROJECT_ATTRIBUTE13 IS NULL) AND (X_PROJECT_ATTRIBUTE13 IS NULL))) THEN
994 pn_var_rent_pkg.lock_row_exception('PROJECT_ATTRIBUTE13',tlinfo.PROJECT_ATTRIBUTE13);
995 END IF;
996
997 IF NOT ((tlinfo.PROJECT_ATTRIBUTE14 = X_PROJECT_ATTRIBUTE14)
998 OR ((tlinfo.PROJECT_ATTRIBUTE14 IS NULL) AND (X_PROJECT_ATTRIBUTE14 IS NULL))) THEN
999 pn_var_rent_pkg.lock_row_exception('PROJECT_ATTRIBUTE14',tlinfo.PROJECT_ATTRIBUTE14);
1000 END IF;
1001
1002 IF NOT ((tlinfo.PROJECT_ATTRIBUTE15 = X_PROJECT_ATTRIBUTE15)
1003 OR ((tlinfo.PROJECT_ATTRIBUTE15 IS NULL) AND (X_PROJECT_ATTRIBUTE15 IS NULL))) THEN
1004 pn_var_rent_pkg.lock_row_exception('PROJECT_ATTRIBUTE15',tlinfo.PROJECT_ATTRIBUTE15);
1005 END IF;
1006
1007 IF NOT ((tlinfo.VAR_RENT_INV_ID = X_VAR_RENT_INV_ID)
1008 OR ((tlinfo.VAR_RENT_INV_ID IS NULL) AND (X_VAR_RENT_INV_ID IS NULL))) THEN
1009 pn_var_rent_pkg.lock_row_exception('VAR_RENT_INV_ID',tlinfo.VAR_RENT_INV_ID);
1010 END IF;
1011
1012 IF NOT ((tlinfo.VAR_RENT_TYPE = X_VAR_RENT_TYPE)
1013 OR ((tlinfo.VAR_RENT_TYPE IS NULL) AND (X_VAR_RENT_TYPE IS NULL))) THEN
1014 pn_var_rent_pkg.lock_row_exception('VAR_RENT_TYPE',tlinfo.VAR_RENT_TYPE);
1015 END IF;
1016
1017 IF NOT ((tlinfo.recoverable_flag = x_recoverable_flag)
1018 OR ((tlinfo.recoverable_flag IS NULL) AND (x_recoverable_flag IS NULL))) THEN
1019 pn_var_rent_pkg.lock_row_exception('RECOVERABLE_FLAG',tlinfo.recoverable_flag);
1020 END IF;
1021
1022 IF NOT ((tlinfo.area_type_code = x_area_type_code)
1023 OR ((tlinfo.area_type_code IS NULL) AND (x_area_type_code IS NULL))) THEN
1024 pn_var_rent_pkg.lock_row_exception('AREA_TYPE_CODE',tlinfo.area_type_code);
1025 END IF;
1026
1027 IF NOT ((tlinfo.area = x_area)
1028 OR ((tlinfo.area IS NULL) AND (x_area IS NULL))) THEN
1029 pn_var_rent_pkg.lock_row_exception('AREA',tlinfo.area);
1030 END IF;
1031
1032 IF NOT ((tlinfo.grouping_rule_id = x_grouping_rule_id)
1033 OR ((tlinfo.grouping_rule_id IS NULL) AND (x_grouping_rule_id IS NULL))) THEN
1034 pn_var_rent_pkg.lock_row_exception('GROUPING_RULE_ID',tlinfo.grouping_rule_id);
1035 END IF;
1036
1037 IF NOT ((tlinfo.term_comments = x_term_comments)
1038 OR ((tlinfo.term_comments IS NULL) AND (x_term_comments IS NULL))) THEN
1039 pn_var_rent_pkg.lock_row_exception('TERM_COMMENTS',tlinfo.term_comments);
1040 END IF;
1041
1042 IF NOT ((tlinfo.TERM_TEMPLATE_ID = X_TERM_TEMPLATE_ID)
1043 OR ((tlinfo.TERM_TEMPLATE_ID IS NULL) AND (X_TERM_TEMPLATE_ID IS NULL))) THEN
1044 pn_var_rent_pkg.lock_row_exception('TERM_TEMPLATE_ID',tlinfo.TERM_TEMPLATE_ID);
1045 END IF;
1046
1047 IF NOT ((tlinfo.include_in_var_rent = x_include_in_var_rent)
1048 OR ((tlinfo.include_in_var_rent IS NULL) AND (x_include_in_var_rent IS NULL))) THEN
1049 pn_var_rent_pkg.lock_row_exception('INCLUDE_IN_VAR_RENT',tlinfo.include_in_var_rent);
1050 END IF;
1051
1052 IF NOT ((tlinfo.recur_bb_calc_date = x_recur_bb_calc_date)
1053 OR ((tlinfo.recur_bb_calc_date IS NULL) AND (x_recur_bb_calc_date IS NULL))) THEN
1054 pn_var_rent_pkg.lock_row_exception('RECUR_BB_CALC_DATE',tlinfo.recur_bb_calc_date);
1055 END IF;
1056
1057 IF NOT ((tlinfo.opex_agr_id = x_opex_agr_id)
1058 OR ((tlinfo.opex_agr_id IS NULL) AND (x_opex_agr_id IS NULL))) THEN
1059 pn_var_rent_pkg.lock_row_exception('OPEX_AGR_ID',tlinfo.opex_agr_id);
1060 END IF;
1061
1062 IF NOT ((tlinfo.opex_recon_id = x_opex_recon_id)
1063 OR ((tlinfo.opex_recon_id IS NULL) AND (x_opex_recon_id IS NULL))) THEN
1064 pn_var_rent_pkg.lock_row_exception('OPEX_RECON_ID',tlinfo.opex_recon_id);
1065 END IF;
1066
1067 IF NOT ((tlinfo.opex_type = x_opex_type)
1068 OR ((tlinfo.opex_type IS NULL) AND (x_opex_type IS NULL))) THEN
1069 pn_var_rent_pkg.lock_row_exception('RECUR_BB_CALC_DATE',tlinfo.opex_type);
1070 END IF;
1071
1072 pnp_debug_pkg.debug ('PNT_PAYMENT_TERMS_PKG.LOCK_ROW (-)');
1073
1074 END LOCK_ROW;
1075
1076 -------------------------------------------------------------------------------=
1077 -- PROCDURE : UPDATE_ROW
1078 -- INVOKED FROM : UPDATE_ROW procedure
1079 -- PURPOSE : updates a term
1080 -- HISTORY :
1081 -- 04-DEC-03 ftanudja o Added parameter area_type_code, area. 3257508.
1082 -- 15-JUL-05 ftanudja o R12 changes. #4495054, #4497295
1083 -- 25-JUL-05 sdmahesh o Bug 4284035 - Replaced pn_payment_terms, pn_leases
1084 -- with _ALL table.
1085 -- 24-NOV-05 Kiran o rounding for amounts done based on currency precision
1086 -- 01-DEC-05 Hareesha o Fetched legal_entity_id into l_legal_entity_id
1087 -- for handling incase it has distributions.
1088 -- 14-AUG-06 Pikhar o Set update_nbp_flag to 'Y' on update
1089 -- 27-SEP-06 Pikhar o Bug 5550641. Added NVL to include_in_var_rent
1090 -- 02-JAN-07 Hareesha o M28#16 Added X_RECUR_BB_CALC_DATE
1091 -- 06-MAR-07 Prabhakar o Added opex_agr_id, opex_recon_id and opex_type
1092 -- for update row handlers.
1093 --------------------------------------------------------------------------------
1094 PROCEDURE UPDATE_ROW (
1095 X_PAYMENT_TERM_ID IN NUMBER,
1096 X_PAYMENT_PURPOSE_CODE IN VARCHAR2,
1097 X_PAYMENT_TERM_TYPE_CODE IN VARCHAR2,
1098 X_FREQUENCY_CODE IN VARCHAR2,
1099 X_LEASE_ID IN NUMBER,
1100 X_LEASE_CHANGE_ID IN NUMBER,
1101 X_START_DATE IN DATE,
1102 X_END_DATE IN DATE,
1103 X_VENDOR_ID IN NUMBER,
1104 X_VENDOR_SITE_ID IN NUMBER,
1105 X_CUSTOMER_ID IN NUMBER,
1106 X_CUSTOMER_SITE_USE_ID IN NUMBER,
1107 X_TARGET_DATE IN DATE,
1108 X_ACTUAL_AMOUNT IN NUMBER,
1109 X_ESTIMATED_AMOUNT IN NUMBER,
1110 X_SET_OF_BOOKS_ID IN NUMBER,
1111 X_CURRENCY_CODE IN VARCHAR2,
1112 X_RATE IN NUMBER,
1113 X_NORMALIZE IN VARCHAR2,
1114 X_LOCATION_ID IN NUMBER,
1115 X_SCHEDULE_DAY IN NUMBER,
1116 X_CUST_SHIP_SITE_ID IN NUMBER,
1117 X_AP_AR_TERM_ID IN NUMBER,
1118 X_CUST_TRX_TYPE_ID IN NUMBER,
1119 X_PROJECT_ID IN NUMBER,
1120 X_TASK_ID IN NUMBER,
1121 X_ORGANIZATION_ID IN NUMBER,
1122 X_EXPENDITURE_TYPE IN VARCHAR2,
1123 X_EXPENDITURE_ITEM_DATE IN DATE,
1124 X_TAX_GROUP_ID IN NUMBER,
1125 X_TAX_CODE_ID IN NUMBER,
1126 X_TAX_CLASSIFICATION_CODE IN VARCHAR2,
1127 X_TAX_INCLUDED IN VARCHAR2,
1128 X_DISTRIBUTION_SET_ID IN NUMBER,
1129 X_INV_RULE_ID IN NUMBER,
1130 X_ACCOUNT_RULE_ID IN NUMBER,
1131 X_SALESREP_ID IN NUMBER,
1132 X_APPROVED_BY IN NUMBER,
1133 X_STATUS IN VARCHAR2,
1134 X_INDEX_PERIOD_ID IN NUMBER,
1135 X_INDEX_TERM_INDICATOR IN VARCHAR2,
1136 X_PO_HEADER_ID IN NUMBER,
1137 X_CUST_PO_NUMBER IN VARCHAR2,
1138 X_RECEIPT_METHOD_ID IN NUMBER,
1139 X_VAR_RENT_INV_ID IN NUMBER,
1140 X_VAR_RENT_TYPE IN VARCHAR2,
1141 X_CHANGED_FLAG IN VARCHAR2,
1142 X_ATTRIBUTE_CATEGORY IN VARCHAR2,
1143 X_ATTRIBUTE1 IN VARCHAR2,
1144 X_ATTRIBUTE2 IN VARCHAR2,
1145 X_ATTRIBUTE3 IN VARCHAR2,
1146 X_ATTRIBUTE4 IN VARCHAR2,
1147 X_ATTRIBUTE5 IN VARCHAR2,
1148 X_ATTRIBUTE6 IN VARCHAR2,
1149 X_ATTRIBUTE7 IN VARCHAR2,
1150 X_ATTRIBUTE8 IN VARCHAR2,
1151 X_ATTRIBUTE9 IN VARCHAR2,
1152 X_ATTRIBUTE10 IN VARCHAR2,
1153 X_ATTRIBUTE11 IN VARCHAR2,
1154 X_ATTRIBUTE12 IN VARCHAR2,
1155 X_ATTRIBUTE13 IN VARCHAR2,
1156 X_ATTRIBUTE14 IN VARCHAR2,
1157 X_ATTRIBUTE15 IN VARCHAR2,
1158 X_PROJECT_ATTRIBUTE_CATEGORY IN VARCHAR2,
1159 X_PROJECT_ATTRIBUTE1 IN VARCHAR2,
1160 X_PROJECT_ATTRIBUTE2 IN VARCHAR2,
1161 X_PROJECT_ATTRIBUTE3 IN VARCHAR2,
1162 X_PROJECT_ATTRIBUTE4 IN VARCHAR2,
1163 X_PROJECT_ATTRIBUTE5 IN VARCHAR2,
1164 X_PROJECT_ATTRIBUTE6 IN VARCHAR2,
1165 X_PROJECT_ATTRIBUTE7 IN VARCHAR2,
1166 X_PROJECT_ATTRIBUTE8 IN VARCHAR2,
1167 X_PROJECT_ATTRIBUTE9 IN VARCHAR2,
1168 X_PROJECT_ATTRIBUTE10 IN VARCHAR2,
1169 X_PROJECT_ATTRIBUTE11 IN VARCHAR2,
1170 X_PROJECT_ATTRIBUTE12 IN VARCHAR2,
1171 X_PROJECT_ATTRIBUTE13 IN VARCHAR2,
1172 X_PROJECT_ATTRIBUTE14 IN VARCHAR2,
1173 X_PROJECT_ATTRIBUTE15 IN VARCHAR2,
1174 X_LAST_UPDATE_DATE IN DATE,
1175 X_LAST_UPDATED_BY IN NUMBER,
1176 X_LAST_UPDATE_LOGIN IN NUMBER,
1177 x_recoverable_flag IN VARCHAR2,
1178 x_area_type_code IN VARCHAR2,
1179 x_area IN NUMBER,
1180 x_grouping_rule_id IN NUMBER,
1181 x_term_altered_flag IN VARCHAR2,
1182 x_source_code IN VARCHAR2,
1183 x_term_comments IN VARCHAR2,
1184 X_TERM_TEMPLATE_ID IN NUMBER,
1185 x_include_in_var_rent IN VARCHAR2 DEFAULT NULL,
1186 X_RECUR_BB_CALC_DATE IN DATE,
1187 x_opex_agr_id IN NUMBER,
1188 x_opex_recon_id IN NUMBER,
1189 x_opex_type IN VARCHAR2
1190 )
1191 IS
1192 CURSOR has_distributions IS
1193 SELECT 'Y'
1194 FROM dual
1195 WHERE EXISTS (SELECT 'Y' FROM pn_distributions_all
1196 WHERE payment_term_id = x_payment_term_id);
1197
1198 l_legal_entity_id pn_payment_terms.legal_entity_id%TYPE;
1199 l_org_id pn_payment_terms.org_id%TYPE;
1200 l_find_le BOOLEAN;
1201 l_return_status VARCHAR2 (2) := NULL;
1202 l_old_est NUMBER;
1203 l_status PN_LEASES.status%TYPE;
1204 l_context varchar2(240);
1205 l_rate pn_payment_terms.rate%TYPE;
1206 l_precision NUMBER;
1207 l_ext_precision NUMBER;
1208 l_min_acct_unit NUMBER;
1209 l_update_nbp_flag VARCHAR2(1);
1210
1211 BEGIN
1212
1213 pnp_debug_pkg.debug ('PNT_PAYMENT_TERMS_PKG.UPDATE_ROW (+)');
1214
1215 -- Check IF both Estimated AND actual amount are NULL
1216 l_context := 'checking est AND actual amt';
1217 l_return_status := NULL;
1218 PNT_PAYMENT_TERMS_PKG.CHECK_PAYMENT_AMOUNTS (
1219 l_RETURN_STATUS
1220 ,X_ACTUAL_AMOUNT
1221 ,X_ESTIMATED_AMOUNT
1222 );
1223
1224 IF (l_return_status IS NOT NULL) THEN
1225 app_exception.Raise_Exception;
1226 END IF;
1227
1228
1229 IF ( tlinfo.start_date <> X_START_DATE
1230 OR tlinfo.end_date <> X_END_DATE
1231 OR tlinfo.actual_amount <> X_ACTUAL_AMOUNT
1232 OR NVL(tlinfo.include_in_var_rent,'N') <> NVL(X_INCLUDE_IN_VAR_RENT,'N')) THEN
1233
1234 l_update_nbp_flag := 'Y';
1235 ELSE
1236 l_update_nbp_flag := NULL;
1237 END IF;
1238
1239 -----------------------------------------------------------------------------
1240 -- Bug Fix : 1668495.
1241 -- Removed the call to the
1242 -- following PROCEDURE
1243 -- PNT_PAYMENT_TERMS_PKG.CHECK_APPROVED_SCHEDULE_EXISTS
1244 -- form the UPDATE TABLE HANDLER
1245 -- to allow the user to change the
1246 -- Vendor Information , Customer Information
1247 -- AND also the GL Account even when the
1248 -- approved payment schedules exists.
1249 -----------------------------------------------------------------------------
1250
1251 /*
1252 Added the following IF statement logic in response to bug ID #1845607
1253 Please refer to PROCEDURE UPDATE_PNT_ITEMS_AMT
1254 */
1255
1256
1257 IF nvl(X_NORMALIZE,'N') = 'N' THEN
1258
1259 l_context := 'SELECTing lease status';
1260
1261 SELECT status
1262 INTO l_status
1263 FROM pn_leases_all
1264 WHERE lease_id = X_LEASE_ID;
1265
1266 l_context := 'SELECTing estimated amt';
1267
1268 SELECT estimated_amount
1269 INTO l_old_est
1270 FROM pn_payment_terms_all
1271 WHERE payment_term_id = X_PAYMENT_TERM_ID;
1272
1273 IF (l_status = 'F' AND l_old_est <> X_ESTIMATED_AMOUNT) THEN
1274
1275 l_context := 'updating payment items amount';
1276 UPDATE_PNT_ITEMS_AMT(X_PAYMENT_TERM_ID,
1277 l_old_est,
1278 X_ESTIMATED_AMOUNT,
1279 X_LEASE_ID,
1280 X_START_DATE,
1281 X_END_DATE,
1282 PN_SCHEDULES_ITEMS.get_frequency(X_FREQUENCY_CODE));
1283 END IF;
1284
1285 END IF;
1286
1287 l_context := 'Selecting old rate and org_id';
1288
1289 SELECT rate, org_id,legal_entity_id
1290 INTO l_rate, l_org_id,l_legal_entity_id
1291 FROM pn_payment_terms_all
1292 WHERE payment_term_id = X_PAYMENT_TERM_ID;
1293
1294 l_context := 'updating payment items rate IF rate IS changed';
1295
1296 -- use the fact that rate must be greater than zero
1297 IF (nvl(l_rate,0) <> nvl(X_RATE,-1)) THEN
1298 UPDATE_PNT_ITEMS_RATE(X_PAYMENT_TERM_ID, X_RATE);
1299 END IF;
1300
1301 l_context := 'updating vendor AND customer info';
1302
1303 PNT_PAYMENT_TERMS_PKG.UPDATE_VENDOR_AND_CUST (
1304 X_PAYMENT_TERM_ID
1305 ,X_VENDOR_ID
1306 ,X_VENDOR_SITE_ID
1307 ,X_LAST_UPDATE_DATE
1308 ,X_LAST_UPDATED_BY
1309 ,X_LAST_UPDATE_LOGIN
1310 ,X_CUSTOMER_ID
1311 ,X_CUSTOMER_SITE_USE_ID
1312 ,X_CUST_SHIP_SITE_ID
1313 );
1314
1315 l_context := 'checking if legal entity changed';
1316
1317 IF pn_r12_util_pkg.is_r12 THEN
1318 IF x_vendor_site_id IS NOT NULL THEN -- PAYABLES
1319 l_find_le := TRUE;
1320 FOR chk_distributions IN has_distributions LOOP
1321 l_find_le := FALSE;
1322 END LOOP;
1323
1324 IF l_find_le THEN
1325 l_legal_entity_id :=
1326 pn_r12_util_pkg.get_le_for_ap(
1327 p_code_combination_id => null
1328 ,p_location_id => x_vendor_site_id
1329 ,p_org_id => l_org_id
1330 );
1331 END IF;
1332 ELSE -- RECEIVABLES
1333 l_legal_entity_id :=
1334 pn_r12_util_pkg.get_le_for_ar(
1335 p_customer_id => x_customer_id
1336 ,p_transaction_type_id => x_cust_trx_type_id
1337 ,p_org_id => l_org_id
1338 );
1339 END IF;
1340 END IF;
1341
1342 /* get currency info for rounding */
1343 fnd_currency.get_info( currency_code => x_currency_code
1344 ,precision => l_precision
1345 ,ext_precision => l_ext_precision
1346 ,min_acct_unit => l_min_acct_unit);
1347
1348 l_context := 'updating payment terms';
1349
1350 UPDATE PN_PAYMENT_TERMS_ALL
1351 SET PAYMENT_TERM_ID = X_PAYMENT_TERM_ID,
1352 PAYMENT_PURPOSE_CODE = X_PAYMENT_PURPOSE_CODE,
1353 PAYMENT_TERM_TYPE_CODE = X_PAYMENT_TERM_TYPE_CODE,
1354 FREQUENCY_CODE = X_FREQUENCY_CODE,
1355 LEASE_ID = X_LEASE_ID,
1356 LEASE_CHANGE_ID = X_LEASE_CHANGE_ID,
1357 START_DATE = X_START_DATE,
1358 END_DATE = X_END_DATE,
1359 VENDOR_ID = X_VENDOR_ID,
1360 VENDOR_SITE_ID = X_VENDOR_SITE_ID,
1361 CUSTOMER_ID = X_CUSTOMER_ID,
1362 CUSTOMER_SITE_USE_ID = X_CUSTOMER_SITE_USE_ID,
1363 TARGET_DATE = X_TARGET_DATE,
1364 ACTUAL_AMOUNT = ROUND(X_ACTUAL_AMOUNT, l_precision),
1365 ESTIMATED_AMOUNT = ROUND(X_ESTIMATED_AMOUNT, l_precision),
1366 SET_OF_BOOKS_ID = X_SET_OF_BOOKS_ID,
1367 CURRENCY_CODE = X_CURRENCY_CODE,
1368 RATE = X_RATE,
1369 NORMALIZE = X_NORMALIZE,
1370 LOCATION_ID = X_LOCATION_ID,
1371 SCHEDULE_DAY = X_SCHEDULE_DAY,
1372 CUST_SHIP_SITE_ID = X_CUST_SHIP_SITE_ID,
1373 AP_AR_TERM_ID = X_AP_AR_TERM_ID,
1374 CUST_TRX_TYPE_ID = X_CUST_TRX_TYPE_ID,
1375 PROJECT_ID = X_PROJECT_ID,
1376 TASK_ID = X_TASK_ID,
1377 ORGANIZATION_ID = X_ORGANIZATION_ID,
1378 EXPENDITURE_TYPE = X_EXPENDITURE_TYPE,
1379 EXPENDITURE_ITEM_DATE = X_EXPENDITURE_ITEM_DATE,
1380 TAX_GROUP_ID = X_TAX_GROUP_ID,
1381 TAX_CODE_ID = X_TAX_CODE_ID,
1382 TAX_CLASSIFICATION_CODE = X_TAX_CLASSIFICATION_CODE,
1383 TAX_INCLUDED = X_TAX_INCLUDED,
1384 DISTRIBUTION_SET_ID = X_DISTRIBUTION_SET_ID,
1385 INV_RULE_ID = X_INV_RULE_ID,
1386 ACCOUNT_RULE_ID = X_ACCOUNT_RULE_ID,
1387 SALESREP_ID = X_SALESREP_ID,
1388 APPROVED_BY = X_APPROVED_BY,
1389 STATUS = X_STATUS,
1390 INDEX_PERIOD_ID = X_INDEX_PERIOD_ID,
1391 INDEX_TERM_INDICATOR = X_INDEX_TERM_INDICATOR,
1392 PO_HEADER_ID = X_PO_HEADER_ID,
1393 CUST_PO_NUMBER = X_CUST_PO_NUMBER,
1394 RECEIPT_METHOD_ID = X_RECEIPT_METHOD_ID,
1395 VAR_RENT_INV_ID = X_VAR_RENT_INV_ID,
1396 VAR_RENT_TYPE = X_VAR_RENT_TYPE,
1397 CHANGED_FLAG = X_CHANGED_FLAG,
1398 LEGAL_ENTITY_ID = L_LEGAL_ENTITY_ID,
1399 ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
1400 ATTRIBUTE1 = X_ATTRIBUTE1,
1401 ATTRIBUTE2 = X_ATTRIBUTE2,
1402 ATTRIBUTE3 = X_ATTRIBUTE3,
1403 ATTRIBUTE4 = X_ATTRIBUTE4,
1404 ATTRIBUTE5 = X_ATTRIBUTE5,
1405 ATTRIBUTE6 = X_ATTRIBUTE6,
1406 ATTRIBUTE7 = X_ATTRIBUTE7,
1407 ATTRIBUTE8 = X_ATTRIBUTE8,
1408 ATTRIBUTE9 = X_ATTRIBUTE9,
1409 ATTRIBUTE10 = X_ATTRIBUTE10,
1410 ATTRIBUTE11 = X_ATTRIBUTE11,
1411 ATTRIBUTE12 = X_ATTRIBUTE12,
1412 ATTRIBUTE13 = X_ATTRIBUTE13,
1413 ATTRIBUTE14 = X_ATTRIBUTE14,
1414 ATTRIBUTE15 = X_ATTRIBUTE15,
1415 PROJECT_ATTRIBUTE_CATEGORY = X_PROJECT_ATTRIBUTE_CATEGORY,
1416 PROJECT_ATTRIBUTE1 = X_PROJECT_ATTRIBUTE1,
1417 PROJECT_ATTRIBUTE2 = X_PROJECT_ATTRIBUTE2,
1418 PROJECT_ATTRIBUTE3 = X_PROJECT_ATTRIBUTE3,
1419 PROJECT_ATTRIBUTE4 = X_PROJECT_ATTRIBUTE4,
1420 PROJECT_ATTRIBUTE5 = X_PROJECT_ATTRIBUTE5,
1421 PROJECT_ATTRIBUTE6 = X_PROJECT_ATTRIBUTE6,
1422 PROJECT_ATTRIBUTE7 = X_PROJECT_ATTRIBUTE7,
1423 PROJECT_ATTRIBUTE8 = X_PROJECT_ATTRIBUTE8,
1424 PROJECT_ATTRIBUTE9 = X_PROJECT_ATTRIBUTE9,
1425 PROJECT_ATTRIBUTE10 = X_PROJECT_ATTRIBUTE10,
1426 PROJECT_ATTRIBUTE11 = X_PROJECT_ATTRIBUTE11,
1427 PROJECT_ATTRIBUTE12 = X_PROJECT_ATTRIBUTE12,
1428 PROJECT_ATTRIBUTE13 = X_PROJECT_ATTRIBUTE13,
1429 PROJECT_ATTRIBUTE14 = X_PROJECT_ATTRIBUTE14,
1430 PROJECT_ATTRIBUTE15 = X_PROJECT_ATTRIBUTE15,
1431 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
1432 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
1433 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
1434 RECOVERABLE_FLAG = X_RECOVERABLE_FLAG,
1435 AREA_TYPE_CODE = X_AREA_TYPE_CODE,
1436 AREA = X_AREA,
1437 GROUPING_RULE_ID = X_GROUPING_RULE_ID,
1438 TERM_ALTERED_FLAG = X_TERM_ALTERED_FLAG,
1439 SOURCE_CODE = X_SOURCE_CODE,
1440 TERM_COMMENTS = X_TERM_COMMENTS,
1441 TERM_TEMPLATE_ID = X_TERM_TEMPLATE_ID,
1442 INCLUDE_IN_VAR_RENT = X_INCLUDE_IN_VAR_RENT,
1443 UPDATE_NBP_FLAG = l_update_nbp_flag,
1444 RECUR_BB_CALC_DATE = X_RECUR_BB_CALC_DATE,
1445 OPEX_AGR_ID = X_OPEX_AGR_ID,
1446 OPEX_RECON_ID = X_OPEX_RECON_ID,
1447 OPEX_TYPE = X_OPEX_TYPE
1448 WHERE PAYMENT_TERM_ID = X_PAYMENT_TERM_ID;
1449
1450 IF (SQL%NOTFOUND) THEN
1451 RAISE NO_DATA_FOUND;
1452 END IF;
1453
1454
1455 pnp_debug_pkg.debug ('PNT_PAYMENT_TERMS_PKG.UPDATE_ROW (-)');
1456
1457 EXCEPTION
1458 WHEN OTHERS THEN
1459 RAISE_APPLICATION_ERROR(-20001,'Error while ' || l_context || to_char(sqlcode));
1460 app_exception.Raise_Exception;
1461
1462 END UPDATE_ROW;
1463
1464 -------------------------------------------------------------------------------
1465 -- PROCDURE : DELETE_ROW
1466 -- INVOKED FROM : DELETE_ROW procedure
1467 -- PURPOSE : deletes the row
1468 -- HISTORY :
1469 -- 05-JUL-05 sdmahesh o Bug 4284035 - Replaced pn_payment_terms with _ALL
1470 -- table.
1471 -------------------------------------------------------------------------------
1472 PROCEDURE DELETE_ROW (
1473 X_PAYMENT_TERM_ID IN NUMBER
1474 )
1475 IS
1476 BEGIN
1477
1478 pnp_debug_pkg.debug ('PNT_PAYMENT_TERMS_PKG.DELETE_ROW (+)');
1479
1480 DELETE FROM pn_payment_terms_all
1481 WHERE payment_term_id = x_payment_term_id;
1482
1483 IF (SQL%NOTFOUND) THEN
1484 RAISE NO_DATA_FOUND;
1485 END IF;
1486
1487 pnp_debug_pkg.debug ('PNT_PAYMENT_TERMS_PKG.DELETE_ROW (-)');
1488 END DELETE_ROW;
1489
1490 -------------------------------------------------------------------------------
1491 -- PROCDURE : UPDATE_VENDOR_AND_CUST
1492 -- INVOKED FROM :
1493 -- PURPOSE : UPDATE each item with the new vendor AND vendor site
1494 -- HISTORY :
1495 -- 05-JUL-05 sdmahesh o Bug 4284035 - Replaced pn_payment_items with _ALL
1496 -- table.
1497 -------------------------------------------------------------------------------
1498 PROCEDURE UPDATE_VENDOR_AND_CUST
1499 (
1500 X_PAYMENT_TERM_ID IN NUMBER
1501 ,X_VENDOR_ID IN NUMBER
1502 ,X_VENDOR_SITE_ID IN NUMBER
1503 ,X_LAST_UPDATE_DATE IN DATE
1504 ,X_LAST_UPDATED_BY IN NUMBER
1505 ,X_LAST_UPDATE_LOGIN IN NUMBER
1506 ,X_CUSTOMER_ID IN NUMBER
1507 ,X_CUSTOMER_SITE_USE_ID IN NUMBER
1508 ,X_CUST_SHIP_SITE_ID IN NUMBER
1509 )
1510 IS
1511 CURSOR c1 IS
1512 SELECT ppi.payment_item_id
1513 FROM pn_payment_items_all ppi
1514 WHERE ppi.payment_term_id = X_PAYMENT_TERM_ID
1515 AND ppi.transferred_to_ap_flag IS NULL
1516 AND ppi.transferred_to_ar_flag IS NULL
1517 FOR UPDATE OF ppi.payment_item_id NOWAIT;
1518
1519 BEGIN
1520 pnp_debug_pkg.debug('PNT_PAYMENT_TERMS_PKG.UPDATE_VENDOR_AND_CUST (+)');
1521 -- UPDATE each item with the new vendor AND vendor site
1522 FOR eachItem IN c1 LOOP
1523
1524 UPDATE pn_payment_items_all
1525 SET VENDOR_ID = X_VENDOR_ID
1526 ,VENDOR_SITE_ID = X_VENDOR_SITE_ID
1527 ,LAST_UPDATE_DATE = X_LAST_UPDATE_DATE
1528 ,LAST_UPDATED_BY = X_LAST_UPDATED_BY
1529 ,LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
1530 ,CUSTOMER_ID = X_CUSTOMER_ID
1531 ,CUSTOMER_SITE_USE_ID = X_CUSTOMER_SITE_USE_ID
1532 ,CUST_SHIP_SITE_ID = X_CUST_SHIP_SITE_ID
1533 WHERE payment_item_id = eachItem.payment_item_id;
1534
1535 END LOOP;
1536
1537 pnp_debug_pkg.debug('PNT_PAYMENT_TERMS_PKG.UPDATE_VENDOR_AND_CUST (-)');
1538 END UPDATE_VENDOR_AND_CUST;
1539
1540 -------------------------------------------------------------------------------
1541 -- PROCEDURE : CHECK_APPROVED_SCHEDULE_EXISTS
1542 -- DESCRIPTION: This PROCEDURE will check IF an approved payment
1543 -- schedule exists BETWEEN the given start AND end
1544 -- date OF the payment term we are trying to create.
1545 -- HISTORY :
1546 -- 23-SEP-03 ATUPPAD o Changed the procedure to handle the One Time terms as in
1547 -- this case start and end dates are the same.
1548 -- Also, the query now executes through Cursor.
1549 -- Also, optmized the query by not joining with tables
1550 -- pn_payment_items and pn_leases. (bug#3140238)
1551 -- 05-JUL-05 sdmahesh o Bug 4284035 - Replaced pn_leases with _ALL table.
1552 -------------------------------------------------------------------------------
1553 PROCEDURE Check_Approved_Schedule_Exists (
1554 x_return_status IN OUT NOCOPY VARCHAR2
1555 ,x_lease_id IN NUMBER
1556 ,x_start_date IN DATE
1557 ,x_end_date IN DATE
1558 ,x_schedule_day IN NUMBER
1559 )
1560 IS
1561 l_leaseNumber VARCHAR2(30) := NULL;
1562 l_startDate DATE := NULL;
1563 l_endDate DATE := NULL;
1564
1565 -- Get the details of Approved Schedules
1566 CURSOR c_chk_app_sch IS
1567 SELECT lease_num
1568 FROM pn_leases_all
1569 WHERE lease_id = x_lease_id
1570 AND EXISTS
1571 (
1572 SELECT pps.lease_id
1573 FROM pn_payment_schedules_all pps
1574 WHERE pps.lease_id = x_lease_id
1575 AND pps.payment_status_lookup_code = 'APPROVED'
1576 AND TO_CHAR(pps.schedule_date, 'DD') = x_schedule_day
1577 AND pps.schedule_date BETWEEN l_startDate AND l_endDate
1578 );
1579
1580 BEGIN
1581 pnp_debug_pkg.debug('PNT_PAYMENT_TERMS_PKG.CHECK_APPROVED_SCHEDULE_EXISTS (+)');
1582
1583 SELECT TRUNC(x_start_date, 'MM')
1584 ,LAST_DAY(x_end_date)
1585 INTO l_startDate
1586 ,l_endDate
1587 FROM DUAL;
1588
1589 OPEN c_chk_app_sch;
1590 FETCH c_chk_app_sch INTO l_leaseNumber;
1591 IF (c_chk_app_sch%NOTFOUND) THEN
1592 CLOSE c_chk_app_sch;
1593 RETURN;
1594 END IF;
1595
1596 CLOSE c_chk_app_sch;
1597
1598 x_return_status := 'E';
1599 fnd_message.set_name('PN','PN_LEASES_APPROVED_PMT_EXISTS');
1600 fnd_message.set_token('LEASE_NUMBER', l_leaseNumber);
1601 fnd_message.set_token('START_DATE', l_startDate);
1602 fnd_message.set_token('END_DATE', l_endDate);
1603
1604 pnp_debug_pkg.debug('PNT_PAYMENT_TERMS_PKG.CHECK_APPROVED_SCHEDULE_EXISTS (-) RetStat: '||x_return_status);
1605 END Check_Approved_Schedule_Exists;
1606
1607 --------------------------------------------------------------------------------
1608 -- PROCEDURE : UPDATE_PNT_ITEMS_AMT
1609 -- PURPOSE : updates scheduled items FOR non-normalized payment terms
1610 -- OTHER REF : bug ID # 1845607
1611 -- SCOPE : local / private
1612 -- NOTE : Logic FOR PROCEDURE UPDATE_PNT_ITEMS_AMT:
1613 -- FOR each payment item with status 'draft'
1614 -- - do UPDATE OF estimated amount
1615 -- - IF the actual amount equals the old estimated amount
1616 -- (which means that it hasn't been changed)
1617 -- THEN set actual amount equal to new estimated amount
1618 -- taking INTO account proration rules
1619 --
1620 -- HISTORY:
1621 -- 28-DEC-01 FTANUDJA o Created
1622 -- 11-OCT-04 STripathi o Fixed for BUG# 3942284. Changed paramater p_freq
1623 -- to p_freq_code. For OT terms, update amount with
1624 -- the p_new_est_amt.
1625 -- 05-JUL-05 sdmahesh o Bug 4284035 - Replaced pn_payment_items,
1626 -- pn_payment_schedules, pn_leases with _ALL table.
1627 -- 24-NOV-05 Kiran o rounding for amounts done based on currency precision
1628 --------------------------------------------------------------------------------
1629
1630 PROCEDURE UPDATE_PNT_ITEMS_AMT (
1631 p_pnt_term_id IN pn_payment_items.payment_term_id%TYPE,
1632 p_old_est_amt IN pn_payment_items.estimated_amount%TYPE,
1633 p_new_est_amt IN pn_payment_items.estimated_amount%TYPE,
1634 p_lease_id IN pn_payment_terms.lease_id%TYPE,
1635 p_trm_str_dt IN DATE,
1636 p_trm_end_dt IN DATE,
1637 p_freq_code IN pn_payment_terms.frequency_code%TYPE
1638 )
1639 IS
1640
1641 l_prorate_rule NUMBER;
1642 l_act_amt NUMBER;
1643 l_est_amt NUMBER;
1644 l_sch_str_dt DATE;
1645 l_sch_end_dt DATE;
1646 l_last_sch_dt DATE;
1647 l_context VARCHAR2(240);
1648 l_first_last VARCHAR2(1);
1649 l_freq NUMBER;
1650
1651 CURSOR pnt_cursor IS
1652 SELECT ppi.payment_item_id
1653 , pps.payment_schedule_id
1654 , ppi.actual_amount
1655 , ppi.estimated_amount
1656 , pps.schedule_date
1657 , ppi.currency_code
1658 FROM pn_payment_items_all ppi,
1659 pn_payment_schedules_all pps
1660 WHERE ppi.payment_term_id = p_pnt_term_id
1661 AND ppi.payment_item_type_lookup_code = 'CASH'
1662 AND pps.payment_schedule_id = ppi.payment_schedule_id
1663 AND pps.payment_status_lookup_code = 'DRAFT';
1664
1665 l_precision NUMBER;
1666 l_ext_precision NUMBER;
1667 l_min_acct_unit NUMBER;
1668 l_first BOOLEAN;
1669 BEGIN
1670
1671 pnp_debug_pkg.debug('PNT_PAYMENT_TERMS_PKG.UPDATE_PNT_ITEMS_AMT (+) TermId: '
1672 ||p_pnt_term_id||', OldEstAmt: '||p_old_est_amt
1673 ||', NewEstAmt: '||p_new_est_amt||', Freq: '||p_freq_code);
1674 l_context:= 'Updating for OneTime term';
1675
1676 IF p_freq_code = 'OT' THEN
1677 l_est_amt := p_new_est_amt;
1678 l_act_amt := p_new_est_amt;
1679
1680 l_first := TRUE;
1681
1682 FOR pnt_rec IN pnt_cursor LOOP
1683 IF l_first THEN
1684 fnd_currency.get_info ( currency_code => pnt_rec.currency_code
1685 ,precision => l_precision
1686 ,ext_precision => l_ext_precision
1687 ,min_acct_unit => l_min_acct_unit);
1688 l_first := FALSE;
1689 END IF;
1690
1691 UPDATE pn_payment_items_all
1692 SET estimated_amount = ROUND(l_est_amt, l_precision)
1693 ,actual_amount = ROUND(l_act_amt, l_precision)
1694 ,export_currency_amount = ROUND(l_act_amt, l_precision)
1695 ,last_update_date = SYSDATE
1696 ,last_updated_by = NVL(fnd_profile.value('USER_ID'),0)
1697 ,last_update_login= NVL(fnd_profile.value('LOGIN_ID'),0)
1698 WHERE payment_item_id = pnt_rec.payment_item_id;
1699
1700 EXIT;
1701 END LOOP;
1702
1703 ELSE
1704
1705 l_context:= 'getting proration rule';
1706 l_freq := pn_schedules_items.get_frequency(p_freq_code);
1707
1708 SELECT payment_term_proration_rule
1709 INTO l_prorate_rule
1710 FROM pn_leases_all
1711 WHERE lease_id = p_lease_id;
1712
1713 l_context:= 'entering UPDATE loop';
1714
1715 l_first := TRUE;
1716
1717 FOR pnt_rec IN pnt_cursor LOOP
1718
1719 /* get currency info the first time */
1720 IF l_first THEN
1721 fnd_currency.get_info ( currency_code => pnt_rec.currency_code
1722 ,precision => l_precision
1723 ,ext_precision => l_ext_precision
1724 ,min_acct_unit => l_min_acct_unit);
1725 l_first := FALSE;
1726 END IF;
1727
1728 /** pro-rating part*/
1729
1730 l_sch_str_dt := TO_DATE('01/'||TO_CHAR(pnt_rec.schedule_date,'MM/YYYY'),'DD/MM/YYYY');
1731 -- the above copied FROM FIRST_DAY in PNSCHITB.pls as OF 01/02/2002
1732
1733 l_sch_end_dt := least(LAST_DAY(p_trm_end_dt),
1734 LAST_DAY(ADD_MONTHS(l_sch_str_dt, (l_freq-1))));
1735
1736 IF l_sch_end_dt = LAST_DAY(p_trm_end_dt) THEN
1737
1738 l_last_sch_dt := PN_SCHEDULES_ITEMS.FIRST_DAY(ADD_MONTHS(p_trm_end_dt,((l_freq -1) * -1)));
1739
1740 END IF;
1741
1742 /* We would need to do the pro-ration only IF the schedule IS the first or the last
1743 schedule */
1744
1745 l_context:= 'taking into account various dates';
1746
1747 IF to_char(pnt_rec.schedule_date,'MM/YYYY') = to_char(p_trm_str_dt,'MM/YYYY') OR
1748 to_char(pnt_rec.schedule_date,'MM/YYYY') = to_char(l_last_sch_dt,'MM/YYYY') THEN
1749
1750 PN_SCHEDULES_ITEMS.GET_AMOUNT(
1751 l_sch_str_dt,
1752 l_sch_end_dt,
1753 p_trm_str_dt,
1754 p_trm_end_dt,
1755 pnt_rec.actual_amount,
1756 p_new_est_amt,
1757 l_freq,
1758 TO_CHAR(l_prorate_rule),
1759 l_act_amt,
1760 l_est_amt
1761 );
1762 pnp_debug_pkg.debug('Get Amt; SchStrDt: '||l_sch_str_dt||', SchEndDt: '||l_sch_end_dt
1763 ||', ActAmt: '||l_act_amt||', EstAmt: '||l_est_amt);
1764
1765 l_first_last := 'Y';
1766
1767 ELSE
1768
1769 l_est_amt := p_new_est_amt;
1770 l_first_last := 'N';
1771
1772 END IF;
1773
1774 l_context := 'figuring out NOCOPY amounts';
1775
1776 IF (l_first_last = 'Y' AND nvl(pnt_rec.actual_amount,0) <> nvl(pnt_rec.estimated_amount,0)) OR
1777 (l_first_last = 'N' AND nvl(pnt_rec.actual_amount,0) <> nvl(p_old_est_amt,0)) THEN
1778
1779 l_act_amt := pnt_rec.actual_amount;
1780
1781 ELSE
1782
1783 l_act_amt := l_est_amt;
1784
1785 END IF;
1786
1787 /** updates estimated amount AND actual amount accordingly in the items table */
1788 l_context := 'updates items table';
1789
1790 UPDATE pn_payment_items_all
1791 SET estimated_amount = ROUND(l_est_amt, l_precision)
1792 ,actual_amount = ROUND(l_act_amt, l_precision)
1793 ,export_currency_amount = ROUND(l_act_amt, l_precision)
1794 ,last_update_date = SYSDATE
1795 ,last_updated_by = NVL(fnd_profile.value('USER_ID'),0)
1796 ,last_update_login= NVL(fnd_profile.value('LOGIN_ID'),0)
1797 WHERE payment_item_id = pnt_rec.payment_item_id;
1798
1799 END LOOP;
1800
1801 END IF; -- p_freq_code = 'OT'
1802
1803 pnp_debug_pkg.debug('PNT_PAYMENT_TERMS_PKG.UPDATE_PNT_ITEMS_AMT (-)');
1804 EXCEPTION
1805 WHEN OTHERS THEN
1806 RAISE_APPLICATION_ERROR(-20001,'Error while ' || l_context || TO_CHAR(sqlcode));
1807 app_exception.Raise_Exception;
1808
1809 pnp_debug_pkg.debug('PNT_PAYMENT_TERMS_PKG.UPDATE_PNT_ITEMS_AMT (-)');
1810 END UPDATE_PNT_ITEMS_AMT;
1811
1812 -------------------------------------------------------------------------------
1813 -- PROCEDURE : UPDATE_PNT_ITEMS_RATE
1814 -- PURPOSE : cascade changed rate FROM term level to item level
1815 -- SCOPE : local / private
1816 -- DESCRIPTION : The logic IS as follows:
1817 -- o Given a particular payment term
1818 -- o UPDATE items in schedules still in DRAFT
1819 -- o Implicit: item IS not exported
1820 -- o Implicit: term has been finalized
1821 -- HISTORY:
1822 -- 15-MAY-02 ftanudja o Created
1823 -- 05-JUL-05 sdmahesh o Bug 4284035 - Replaced pn_payment_items with _ALL
1824 -- table.
1825 -------------------------------------------------------------------------------
1826
1827 PROCEDURE UPDATE_PNT_ITEMS_RATE(p_pnt_term_id pn_payment_items.payment_item_id%TYPE,
1828 p_new_rate pn_payment_items.rate%TYPE)
1829 IS
1830 l_context VARCHAR2(240);
1831
1832 CURSOR pnt_cursor IS
1833 SELECT payment_item_id
1834 FROM pn_payment_items_all ppi
1835 WHERE ppi.payment_term_id = p_pnt_term_id
1836 AND ppi.export_to_ap_flag IS NULL
1837 AND ppi.export_to_ar_flag IS NULL;
1838
1839 BEGIN
1840
1841 pnp_debug_pkg.debug('PNT_PAYMENT_TERMS_PKG.UPDATE_PNT_ITEMS_RATE (+)');
1842 l_context := 'opening payment rate cursor';
1843
1844 FOR pnt_rec IN pnt_cursor LOOP
1845
1846 l_context := 'updating payment item ID: '||pnt_rec.payment_item_id||' with new rate';
1847 UPDATE pn_payment_items_all
1848 SET rate = p_new_rate
1849 WHERE payment_item_id = pnt_rec.payment_item_id;
1850
1851 END LOOP;
1852
1853 pnp_debug_pkg.debug('PNT_PAYMENT_TERMS_PKG.UPDATE_PNT_ITEMS_RATE (-)');
1854 EXCEPTION
1855 WHEN OTHERS THEN
1856 RAISE_APPLICATION_ERROR(-20001,'Error while ' || l_context || TO_CHAR(sqlcode));
1857 app_exception.Raise_Exception;
1858 pnp_debug_pkg.debug('PNT_PAYMENT_TERMS_PKG.UPDATE_PNT_ITEMS_RATE (-)');
1859 END UPDATE_PNT_ITEMS_RATE;
1860
1861 -------------------------------------------------------------------------------
1862 -- PROCEDURE : create_hist_corr_upd
1863 -- DESCRIPTION: This procedure creates history for the term record when the
1864 -- term record is 'corrected' or 'updated' in the Leases UI.
1865 -- HISTORY
1866 -- 15-OCT-04 Mrinal o Created.
1867 -- 15-JUL-05 ftanudja o R12 changes - #4495054, #4497295
1868 -- 25-JUL-05 sdmahesh o Bug 4284035 - Replaced pn_payment_terms with _ALL
1869 -- table.
1870 -------------------------------------------------------------------------------
1871 PROCEDURE create_hist_corr_upd(p_term_id IN NUMBER,
1872 p_dist_changed IN NUMBER,
1873 p_hist_dist_tab IN dist_type,
1874 p_change_mode IN VARCHAR2,
1875 p_eff_str_dt IN DATE,
1876 p_eff_end_dt IN DATE) IS
1877
1878 term_rec PN_PAYMENT_TERMS_ALL%ROWTYPE;
1879 n NUMBER(1);
1880 l_dist_change_id NUMBER(15);
1881 l_prev_term_hist_id NUMBER(15);
1882 l_new_term_id NUMBER(15);
1883 l_new_dist_id NUMBER(15);
1884 l_term_id NUMBER(15);
1885 l_dist_id NUMBER(15);
1886 l_str_dt DATE;
1887 l_end_dt DATE;
1888 l_last_update_date DATE;
1889 l_last_updated_by NUMBER;
1890 l_creation_date DATE;
1891 l_created_by NUMBER;
1892 l_last_update_login NUMBER;
1893 l_insert_dist_hist VARCHAR2(1);
1894 l_adj_type_code pn_payment_terms_history.adjustment_type_code%TYPE;
1895 l_term_comments pn_payment_terms_history.term_comments%TYPE;
1896 l_changed_flag pn_payment_terms.changed_flag%TYPE;
1897
1898 CURSOR get_term_cur(p_term_id IN NUMBER) IS
1899 SELECT *
1900 FROM pn_payment_terms_all
1901 WHERE payment_term_id = p_term_id;
1902
1903 CURSOR get_dist_cur(p_term_id IN NUMBER) IS
1904 SELECT *
1905 FROM pn_distributions_all
1906 WHERE payment_term_id = p_term_id;
1907
1908 CURSOR get_max_dist_change_id_cur(p_term_id IN NUMBER) IS
1909 SELECT MAX(pdh.distribution_change_id)
1910 FROM pn_distributions_history pdh,
1911 pn_distributions_all pd
1912 WHERE pdh.distribution_id = pd.distribution_id
1913 AND pd.payment_term_id = p_term_id;
1914
1915 CURSOR get_max_term_hist_id_cur(p_term_id IN NUMBER) IS
1916 SELECT MAX(term_history_id)
1917 FROM pn_payment_terms_history
1918 WHERE payment_term_id = p_term_id;
1919 BEGIN
1920
1921 pnp_debug_pkg.debug('PNT_PAYMENT_TERMS_PKG.create_hist_corr_upd (+)');
1922 pnp_debug_pkg.debug('create_hist_corr_upd - IN : p_term_id :'||p_term_id);
1923 pnp_debug_pkg.debug('create_hist_corr_upd - IN : p_dist_changed :'||p_dist_changed);
1924 pnp_debug_pkg.debug('create_hist_corr_upd - IN : p_hist_dist_tab.count :'||p_hist_dist_tab.count);
1925 pnp_debug_pkg.debug('create_hist_corr_upd - IN : p_change_mode :'||p_change_mode);
1926 pnp_debug_pkg.debug('create_hist_corr_upd - IN : p_eff_str_dt :'||p_eff_str_dt);
1927 pnp_debug_pkg.debug('create_hist_corr_upd - IN : p_eff_end_dt :'||p_eff_end_dt);
1928
1929 l_dist_change_id := NULL;
1930 l_prev_term_hist_id := NULL;
1931 l_new_term_id := NULL;
1932 l_new_dist_id := NULL;
1933 l_term_id := NULL;
1934 l_dist_id := NULL;
1935 l_adj_type_code := NULL;
1936 l_changed_flag := NULL;
1937
1938 OPEN get_term_cur(p_term_id);
1939 FETCH get_term_cur INTO term_rec;
1940 CLOSE get_term_cur;
1941
1942 IF pn_r12_util_pkg.is_r12 THEN
1943 term_rec.tax_group_id := null;
1944 term_rec.tax_code_id := null;
1945 tlinfo.tax_group_id := null;
1946 tlinfo.tax_code_id := null;
1947
1948 ELSE
1949 term_rec.tax_classification_code := null;
1950 tlinfo.tax_classification_code := null;
1951 END IF;
1952
1953 IF (NVL(term_rec.location_id, -9999) <> NVL(tlinfo.location_id, -9999) OR
1954 term_rec.start_date <> tlinfo.start_date OR
1955 term_rec.end_date <> tlinfo.end_date OR
1956 NVL(term_rec.estimated_amount, -9999) <> NVL(tlinfo.estimated_amount, -9999) OR
1957 NVL(term_rec.actual_amount, -9999) <> NVL(tlinfo.actual_amount, -9999) OR
1958 NVL(term_rec.vendor_id, -9999) <> NVL(tlinfo.vendor_id, -9999) OR
1959 NVL(term_rec.vendor_site_id, -9999) <> NVL(tlinfo.vendor_site_id, -9999) OR
1960 NVL(term_rec.ap_ar_term_id, -9999) <> NVL(tlinfo.ap_ar_term_id, -9999) OR
1961 NVL(term_rec.tax_group_id, -9999) <> NVL(tlinfo.tax_group_id, -9999) OR
1962 NVL(term_rec.tax_code_id, -9999) <> NVL(tlinfo.tax_code_id, -9999) OR
1963 NVL(term_rec.tax_classification_code, 'X') <> NVL(tlinfo.tax_classification_code, 'X') OR
1964 NVL(term_rec.tax_included, 'N') <> NVL(tlinfo.tax_included, 'N') OR
1965 NVL(term_rec.distribution_set_id, -9999) <> NVL(tlinfo.distribution_set_id, -9999) OR
1966 NVL(term_rec.project_id, -9999) <> NVL(tlinfo.project_id, -9999) OR
1967 NVL(term_rec.task_id, -9999) <> NVL(tlinfo.task_id, -9999) OR
1968 term_rec.expenditure_type <> tlinfo.expenditure_type OR
1969 term_rec.expenditure_item_date <> tlinfo.expenditure_item_date OR
1970 NVL(term_rec.organization_id, -9999) <> NVL(tlinfo.organization_id, -9999) OR
1971 NVL(term_rec.customer_id, -9999) <> NVL(tlinfo.customer_id, -9999) OR
1972 NVL(term_rec.customer_site_use_id, -9999) <> NVL(tlinfo.customer_site_use_id, -9999) OR
1973 NVL(term_rec.cust_ship_site_id, -9999) <> NVL(tlinfo.cust_ship_site_id, -9999) OR
1974 NVL(term_rec.cust_trx_type_id, -9999) <> NVL(tlinfo.cust_trx_type_id, -9999) OR
1975 NVL(term_rec.receipt_method_id, -9999) <> NVL(tlinfo.receipt_method_id, -9999) OR
1976 NVL(term_rec.cust_po_number, -9999) <> NVL(tlinfo.cust_po_number, -9999) OR
1977 NVL(term_rec.inv_rule_id, -9999) <> NVL(tlinfo.inv_rule_id, -9999) OR
1978 NVL(term_rec.account_rule_id, -9999) <> NVL(tlinfo.account_rule_id, -9999) OR
1979 NVL(term_rec.salesrep_id, -9999) <> NVL(tlinfo.salesrep_id, -9999)) OR
1980 NVL(p_dist_changed, 0) > 0
1981 THEN
1982
1983 pnp_debug_pkg.debug('create_hist_corr_upd : p_dist_changed :'||p_dist_changed);
1984 pnp_debug_pkg.debug('create_hist_corr_upd : term_altered_flag :'||tlinfo.term_altered_flag);
1985
1986 IF NVL(p_dist_changed, 0) >= 0 THEN
1987
1988 IF NVL(p_hist_dist_tab.count,0) <> 0 AND
1989 NVL(tlinfo.term_altered_flag, 'N') = 'N' THEN
1990
1991 SELECT pn_distributions_history_s1.NEXTVAL
1992 INTO l_dist_change_id
1993 FROM DUAL;
1994
1995 pnp_debug_pkg.debug('create_hist_corr_upd : l_dist_change_id :'||l_dist_change_id);
1996
1997 FOR i in 1..p_hist_dist_tab.count LOOP
1998
1999 pnp_debug_pkg.debug('create_hist_corr_upd : Inserting in Dist. Hist. for very first time');
2000
2001 INSERT INTO pn_distributions_history
2002 (
2003 DISTRIBUTION_HISTORY_ID
2004 ,LAST_UPDATE_DATE
2005 ,LAST_UPDATED_BY
2006 ,CREATION_DATE
2007 ,CREATED_BY
2008 ,LAST_UPDATE_LOGIN
2009 ,DISTRIBUTION_CHANGE_ID
2010 ,DISTRIBUTION_ID
2011 ,ACCOUNT_ID
2012 ,ACCOUNT_CLASS
2013 ,PERCENTAGE
2014 ,LINE_NUMBER
2015 ,ATTRIBUTE_CATEGORY
2016 ,ATTRIBUTE1
2017 ,ATTRIBUTE2
2018 ,ATTRIBUTE3
2019 ,ATTRIBUTE4
2020 ,ATTRIBUTE5
2021 ,ATTRIBUTE6
2022 ,ATTRIBUTE7
2023 ,ATTRIBUTE8
2024 ,ATTRIBUTE9
2025 ,ATTRIBUTE10
2026 ,ATTRIBUTE11
2027 ,ATTRIBUTE12
2028 ,ATTRIBUTE13
2029 ,ATTRIBUTE14
2030 ,ATTRIBUTE15)
2031 VALUES
2032 (
2033 pn_distributions_history_s.NEXTVAL
2034 ,SYSDATE
2035 ,FND_GLOBAL.USER_ID
2036 ,SYSDATE
2037 ,FND_GLOBAL.USER_ID
2038 ,FND_GLOBAL.LOGIN_ID
2039 ,l_dist_change_id
2040 ,p_hist_dist_tab(i).DISTRIBUTION_ID
2041 ,p_hist_dist_tab(i).ACCOUNT_ID
2042 ,p_hist_dist_tab(i).ACCOUNT_CLASS
2043 ,p_hist_dist_tab(i).PERCENTAGE
2044 ,p_hist_dist_tab(i).LINE_NUMBER
2045 ,p_hist_dist_tab(i).ATTRIBUTE_CATEGORY
2046 ,p_hist_dist_tab(i).ATTRIBUTE1
2047 ,p_hist_dist_tab(i).ATTRIBUTE2
2048 ,p_hist_dist_tab(i).ATTRIBUTE3
2049 ,p_hist_dist_tab(i).ATTRIBUTE4
2050 ,p_hist_dist_tab(i).ATTRIBUTE5
2051 ,p_hist_dist_tab(i).ATTRIBUTE6
2052 ,p_hist_dist_tab(i).ATTRIBUTE7
2053 ,p_hist_dist_tab(i).ATTRIBUTE8
2054 ,p_hist_dist_tab(i).ATTRIBUTE9
2055 ,p_hist_dist_tab(i).ATTRIBUTE10
2056 ,p_hist_dist_tab(i).ATTRIBUTE11
2057 ,p_hist_dist_tab(i).ATTRIBUTE12
2058 ,p_hist_dist_tab(i).ATTRIBUTE13
2059 ,p_hist_dist_tab(i).ATTRIBUTE14
2060 ,p_hist_dist_tab(i).ATTRIBUTE15
2061 );
2062 END LOOP;
2063
2064 pnp_debug_pkg.debug('create_hist_corr_upd : Inserting in Term Hist. for very first time');
2065
2066 INSERT INTO pn_payment_terms_history
2067 (
2068 TERM_HISTORY_ID
2069 ,LAST_UPDATE_DATE
2070 ,LAST_UPDATED_BY
2071 ,CREATION_DATE
2072 ,CREATED_BY
2073 ,LAST_UPDATE_LOGIN
2074 ,PAYMENT_TERM_ID
2075 ,DISTRIBUTION_CHANGE_ID
2076 ,PREV_TERM_HISTORY_ID
2077 ,ADJUSTMENT_TYPE_CODE
2078 ,TOTAL_ADJ_AMOUNT
2079 ,PAYMENT_PURPOSE_CODE
2080 ,PAYMENT_TERM_TYPE_CODE
2081 ,FREQUENCY_CODE
2082 ,LEASE_ID
2083 ,LEASE_CHANGE_ID
2084 ,START_DATE
2085 ,END_DATE
2086 ,VENDOR_ID
2087 ,VENDOR_SITE_ID
2088 ,TARGET_DATE
2089 ,ACTUAL_AMOUNT
2090 ,ESTIMATED_AMOUNT
2091 ,SET_OF_BOOKS_ID
2092 ,CURRENCY_CODE
2093 ,RATE
2094 ,ATTRIBUTE_CATEGORY
2095 ,ATTRIBUTE1
2096 ,ATTRIBUTE2
2097 ,ATTRIBUTE3
2098 ,ATTRIBUTE4
2099 ,ATTRIBUTE5
2100 ,ATTRIBUTE6
2101 ,ATTRIBUTE7
2102 ,ATTRIBUTE8
2103 ,ATTRIBUTE9
2104 ,ATTRIBUTE10
2105 ,ATTRIBUTE11
2106 ,ATTRIBUTE12
2107 ,ATTRIBUTE13
2108 ,ATTRIBUTE14
2109 ,ATTRIBUTE15
2110 ,CUSTOMER_ID
2111 ,CUSTOMER_SITE_USE_ID
2112 ,NORMALIZE
2113 ,LOCATION_ID
2114 ,SCHEDULE_DAY
2115 ,CUST_SHIP_SITE_ID
2116 ,AP_AR_TERM_ID
2117 ,CUST_TRX_TYPE_ID
2118 ,PROJECT_ID
2119 ,TASK_ID
2120 ,ORGANIZATION_ID
2121 ,EXPENDITURE_TYPE
2122 ,EXPENDITURE_ITEM_DATE
2123 ,TAX_GROUP_ID
2124 ,TAX_CODE_ID
2125 ,TAX_CLASSIFICATION_CODE
2126 ,TAX_INCLUDED
2127 ,DISTRIBUTION_SET_ID
2128 ,INV_RULE_ID
2129 ,ACCOUNT_RULE_ID
2130 ,SALESREP_ID
2131 ,APPROVED_BY
2132 ,STATUS
2133 ,INDEX_PERIOD_ID
2134 ,INDEX_TERM_INDICATOR
2135 ,PO_HEADER_ID
2136 ,CUST_PO_NUMBER
2137 ,RECEIPT_METHOD_ID
2138 ,LEGAL_ENTITY_ID
2139 ,PROJECT_ATTRIBUTE_CATEGORY
2140 ,PROJECT_ATTRIBUTE1
2141 ,PROJECT_ATTRIBUTE2
2142 ,PROJECT_ATTRIBUTE3
2143 ,PROJECT_ATTRIBUTE4
2144 ,PROJECT_ATTRIBUTE5
2145 ,PROJECT_ATTRIBUTE6
2146 ,PROJECT_ATTRIBUTE7
2147 ,PROJECT_ATTRIBUTE8
2148 ,PROJECT_ATTRIBUTE9
2149 ,PROJECT_ATTRIBUTE10
2150 ,PROJECT_ATTRIBUTE11
2151 ,PROJECT_ATTRIBUTE12
2152 ,PROJECT_ATTRIBUTE13
2153 ,PROJECT_ATTRIBUTE14
2154 ,PROJECT_ATTRIBUTE15
2155 ,VAR_RENT_INV_ID
2156 ,VAR_RENT_TYPE
2157 ,CHANGED_FLAG
2158 ,NORM_START_DATE
2159 ,TERM_TEMPLATE_ID
2160 ,EVENT_TYPE_CODE
2161 ,LEASE_STATUS
2162 ,NORM_END_DATE
2163 ,RECOVERABLE_FLAG
2164 ,PERIOD_BILLREC_ID
2165 ,AMOUNT_TYPE
2166 ,REC_AGR_LINE_ID
2167 ,GROUPING_RULE_ID
2168 ,AREA_TYPE_CODE
2169 ,AREA
2170 ,TERM_COMMENTS)
2171 VALUES
2172 (PN_PAYMENT_TERMS_HISTORY_S.NEXTVAL
2173 ,SYSDATE
2174 ,FND_GLOBAL.USER_ID
2175 ,SYSDATE
2176 ,FND_GLOBAL.USER_ID
2177 ,FND_GLOBAL.LOGIN_ID
2178 ,tlinfo.PAYMENT_TERM_ID
2179 ,l_dist_change_id
2180 ,NULL -- PREV_TERM_HISTORY_ID
2181 ,NULL -- ADJUSTMENT_TYPE_CODE
2182 ,NULL -- TOTAL_ADJ_AMOUNT
2183 ,tlinfo.PAYMENT_PURPOSE_CODE
2184 ,tlinfo.PAYMENT_TERM_TYPE_CODE
2185 ,tlinfo.FREQUENCY_CODE
2186 ,tlinfo.LEASE_ID
2187 ,tlinfo.LEASE_CHANGE_ID
2188 ,tlinfo.START_DATE
2189 ,tlinfo.END_DATE
2190 ,tlinfo.VENDOR_ID
2191 ,tlinfo.VENDOR_SITE_ID
2192 ,tlinfo.TARGET_DATE
2193 ,tlinfo.ACTUAL_AMOUNT
2194 ,tlinfo.ESTIMATED_AMOUNT
2195 ,tlinfo.SET_OF_BOOKS_ID
2196 ,tlinfo.CURRENCY_CODE
2197 ,tlinfo.RATE
2198 ,tlinfo.ATTRIBUTE_CATEGORY
2199 ,tlinfo.ATTRIBUTE1
2200 ,tlinfo.ATTRIBUTE2
2201 ,tlinfo.ATTRIBUTE3
2202 ,tlinfo.ATTRIBUTE4
2203 ,tlinfo.ATTRIBUTE5
2204 ,tlinfo.ATTRIBUTE6
2205 ,tlinfo.ATTRIBUTE7
2206 ,tlinfo.ATTRIBUTE8
2207 ,tlinfo.ATTRIBUTE9
2208 ,tlinfo.ATTRIBUTE10
2209 ,tlinfo.ATTRIBUTE11
2210 ,tlinfo.ATTRIBUTE12
2211 ,tlinfo.ATTRIBUTE13
2212 ,tlinfo.ATTRIBUTE14
2213 ,tlinfo.ATTRIBUTE15
2214 ,tlinfo.CUSTOMER_ID
2215 ,tlinfo.CUSTOMER_SITE_USE_ID
2216 ,tlinfo.NORMALIZE
2217 ,tlinfo.LOCATION_ID
2218 ,tlinfo.SCHEDULE_DAY
2219 ,tlinfo.CUST_SHIP_SITE_ID
2220 ,tlinfo.AP_AR_TERM_ID
2221 ,tlinfo.CUST_TRX_TYPE_ID
2222 ,tlinfo.PROJECT_ID
2223 ,tlinfo.TASK_ID
2224 ,tlinfo.ORGANIZATION_ID
2225 ,tlinfo.EXPENDITURE_TYPE
2226 ,tlinfo.EXPENDITURE_ITEM_DATE
2227 ,tlinfo.TAX_GROUP_ID
2228 ,tlinfo.TAX_CODE_ID
2229 ,tlinfo.TAX_CLASSIFICATION_CODE
2230 ,tlinfo.TAX_INCLUDED
2231 ,tlinfo.DISTRIBUTION_SET_ID
2232 ,tlinfo.INV_RULE_ID
2233 ,tlinfo.ACCOUNT_RULE_ID
2234 ,tlinfo.SALESREP_ID
2235 ,tlinfo.APPROVED_BY
2236 ,tlinfo.STATUS
2237 ,tlinfo.INDEX_PERIOD_ID
2238 ,tlinfo.INDEX_TERM_INDICATOR
2239 ,tlinfo.PO_HEADER_ID
2240 ,tlinfo.CUST_PO_NUMBER
2241 ,tlinfo.RECEIPT_METHOD_ID
2242 ,tlinfo.LEGAL_ENTITY_ID
2243 ,tlinfo.PROJECT_ATTRIBUTE_CATEGORY
2244 ,tlinfo.PROJECT_ATTRIBUTE1
2245 ,tlinfo.PROJECT_ATTRIBUTE2
2246 ,tlinfo.PROJECT_ATTRIBUTE3
2247 ,tlinfo.PROJECT_ATTRIBUTE4
2248 ,tlinfo.PROJECT_ATTRIBUTE5
2249 ,tlinfo.PROJECT_ATTRIBUTE6
2250 ,tlinfo.PROJECT_ATTRIBUTE7
2251 ,tlinfo.PROJECT_ATTRIBUTE8
2252 ,tlinfo.PROJECT_ATTRIBUTE9
2253 ,tlinfo.PROJECT_ATTRIBUTE10
2254 ,tlinfo.PROJECT_ATTRIBUTE11
2255 ,tlinfo.PROJECT_ATTRIBUTE12
2256 ,tlinfo.PROJECT_ATTRIBUTE13
2257 ,tlinfo.PROJECT_ATTRIBUTE14
2258 ,tlinfo.PROJECT_ATTRIBUTE15
2259 ,tlinfo.VAR_RENT_INV_ID
2260 ,tlinfo.VAR_RENT_TYPE
2261 ,tlinfo.CHANGED_FLAG
2262 ,tlinfo.NORM_START_DATE
2263 ,tlinfo.TERM_TEMPLATE_ID
2264 ,tlinfo.EVENT_TYPE_CODE
2265 ,tlinfo.LEASE_STATUS
2266 ,tlinfo.NORM_END_DATE
2267 ,tlinfo.RECOVERABLE_FLAG
2268 ,tlinfo.PERIOD_BILLREC_ID
2269 ,tlinfo.AMOUNT_TYPE
2270 ,tlinfo.REC_AGR_LINE_ID
2271 ,tlinfo.GROUPING_RULE_ID
2272 ,tlinfo.AREA_TYPE_CODE
2273 ,tlinfo.AREA
2274 ,NULL);
2275
2276 pnp_debug_pkg.debug('create_hist_corr_upd : Updating Term Altered Flag in pn_payment_terms_all');
2277
2278 UPDATE pn_payment_terms_all
2279 SET term_altered_flag = 'Y'
2280 WHERE payment_term_id = p_term_id;
2281 END IF;
2282
2283 IF NVL(p_dist_changed, 0) > 0 AND
2284 p_change_mode = 'CORRECT' THEN
2285
2286 SELECT pn_distributions_history_s1.NEXTVAL
2287 INTO l_dist_change_id
2288 FROM DUAL;
2289
2290 pnp_debug_pkg.debug('create_hist_corr_upd : '||p_change_mode|| '- l_dist_change_id : '||l_dist_change_id);
2291
2292 FOR dist_rec IN get_dist_cur(p_term_id) LOOP
2293
2294 pnp_debug_pkg.debug('create_hist_corr_upd : '||p_change_mode|| '- Inserting into Dist. Hist.');
2295
2296 INSERT INTO pn_distributions_history
2297 (
2298 DISTRIBUTION_HISTORY_ID
2299 ,LAST_UPDATE_DATE
2300 ,LAST_UPDATED_BY
2301 ,CREATION_DATE
2302 ,CREATED_BY
2303 ,LAST_UPDATE_LOGIN
2304 ,DISTRIBUTION_CHANGE_ID
2305 ,DISTRIBUTION_ID
2306 ,ACCOUNT_ID
2307 ,ACCOUNT_CLASS
2308 ,PERCENTAGE
2309 ,LINE_NUMBER
2310 ,ATTRIBUTE_CATEGORY
2311 ,ATTRIBUTE1
2312 ,ATTRIBUTE2
2313 ,ATTRIBUTE3
2314 ,ATTRIBUTE4
2315 ,ATTRIBUTE5
2316 ,ATTRIBUTE6
2317 ,ATTRIBUTE7
2318 ,ATTRIBUTE8
2319 ,ATTRIBUTE9
2320 ,ATTRIBUTE10
2321 ,ATTRIBUTE11
2322 ,ATTRIBUTE12
2323 ,ATTRIBUTE13
2324 ,ATTRIBUTE14
2325 ,ATTRIBUTE15)
2326 VALUES
2327 (
2328 pn_distributions_history_s.NEXTVAL
2329 ,SYSDATE
2330 ,FND_GLOBAL.USER_ID
2331 ,SYSDATE
2332 ,FND_GLOBAL.USER_ID
2333 ,FND_GLOBAL.LOGIN_ID
2334 ,l_dist_change_id
2335 ,dist_rec.DISTRIBUTION_ID
2336 ,dist_rec.ACCOUNT_ID
2337 ,dist_rec.ACCOUNT_CLASS
2338 ,dist_rec.PERCENTAGE
2339 ,dist_rec.LINE_NUMBER
2340 ,dist_rec.ATTRIBUTE_CATEGORY
2341 ,dist_rec.ATTRIBUTE1
2342 ,dist_rec.ATTRIBUTE2
2343 ,dist_rec.ATTRIBUTE3
2344 ,dist_rec.ATTRIBUTE4
2345 ,dist_rec.ATTRIBUTE5
2346 ,dist_rec.ATTRIBUTE6
2347 ,dist_rec.ATTRIBUTE7
2348 ,dist_rec.ATTRIBUTE8
2349 ,dist_rec.ATTRIBUTE9
2350 ,dist_rec.ATTRIBUTE10
2351 ,dist_rec.ATTRIBUTE11
2352 ,dist_rec.ATTRIBUTE12
2353 ,dist_rec.ATTRIBUTE13
2354 ,dist_rec.ATTRIBUTE14
2355 ,dist_rec.ATTRIBUTE15);
2356
2357 END LOOP;
2358 END IF;
2359 END IF;
2360
2361 OPEN get_max_dist_change_id_cur(p_term_id);
2362 FETCH get_max_dist_change_id_cur INTO l_dist_change_id;
2363 CLOSE get_max_dist_change_id_cur;
2364
2365 pnp_debug_pkg.debug('create_hist_corr_upd : '||p_change_mode|| '- get_max_dist_change_id : '||l_dist_change_id);
2366
2367 OPEN get_max_term_hist_id_cur(p_term_id);
2368 FETCH get_max_term_hist_id_cur INTO l_prev_term_hist_id;
2369 CLOSE get_max_term_hist_id_cur;
2370
2371 pnp_debug_pkg.debug('create_hist_corr_upd : '||p_change_mode|| '- get_max_term_hist_id : '||l_prev_term_hist_id);
2372
2373 IF p_change_mode = 'CORRECT' THEN
2374
2375 IF (term_rec.start_date > tlinfo.start_date AND
2376 term_rec.end_date = tlinfo.end_date AND
2377 term_rec.actual_amount = tlinfo.actual_amount) OR
2378 (term_rec.start_date = tlinfo.start_date AND
2379 term_rec.end_date < tlinfo.end_date AND
2380 term_rec.actual_amount = tlinfo.actual_amount) OR
2381 (term_rec.start_date > tlinfo.start_date AND
2382 term_rec.end_date < tlinfo.end_date AND
2383 term_rec.actual_amount = tlinfo.actual_amount) THEN
2384
2385 l_adj_type_code := 'ADJ-PCON';
2386
2387 ELSIF (term_rec.start_date < tlinfo.start_date AND
2388 term_rec.end_date = tlinfo.end_date AND
2389 term_rec.actual_amount = tlinfo.actual_amount) OR
2390 (term_rec.start_date = tlinfo.start_date AND
2391 term_rec.end_date > tlinfo.end_date AND
2392 term_rec.actual_amount = tlinfo.actual_amount) OR
2393 (term_rec.start_date < tlinfo.start_date AND
2394 term_rec.end_date > tlinfo.end_date AND
2395 term_rec.actual_amount = tlinfo.actual_amount) THEN
2396
2397 l_adj_type_code := 'ADJ-PEXP';
2398
2399 ELSIF (term_rec.start_date = tlinfo.start_date AND
2400 term_rec.end_date = tlinfo.end_date AND
2401 term_rec.actual_amount <> tlinfo.actual_amount) THEN
2402
2403 l_adj_type_code := 'ADJ-AMT';
2404
2405 ELSE
2406 l_adj_type_code := 'ADJ-MUL';
2407 END IF;
2408
2409 pnp_debug_pkg.debug('create_hist_corr_upd : l_adj_type_code :'||l_adj_type_code);
2410 pnp_debug_pkg.debug('create_hist_corr_upd : '||p_change_mode|| '- Inserting into Term Hist.');
2411
2412 INSERT INTO pn_payment_terms_history
2413 (TERM_HISTORY_ID
2414 ,LAST_UPDATE_DATE
2415 ,LAST_UPDATED_BY
2416 ,CREATION_DATE
2417 ,CREATED_BY
2418 ,LAST_UPDATE_LOGIN
2419 ,PAYMENT_TERM_ID
2420 ,DISTRIBUTION_CHANGE_ID
2421 ,PREV_TERM_HISTORY_ID
2422 ,ADJUSTMENT_TYPE_CODE
2423 ,TOTAL_ADJ_AMOUNT
2424 ,PAYMENT_PURPOSE_CODE
2425 ,PAYMENT_TERM_TYPE_CODE
2426 ,FREQUENCY_CODE
2427 ,LEASE_ID
2428 ,LEASE_CHANGE_ID
2429 ,START_DATE
2430 ,END_DATE
2431 ,VENDOR_ID
2432 ,VENDOR_SITE_ID
2433 ,TARGET_DATE
2434 ,ACTUAL_AMOUNT
2435 ,ESTIMATED_AMOUNT
2436 ,SET_OF_BOOKS_ID
2437 ,CURRENCY_CODE
2438 ,RATE
2439 ,ATTRIBUTE_CATEGORY
2440 ,ATTRIBUTE1
2441 ,ATTRIBUTE2
2442 ,ATTRIBUTE3
2443 ,ATTRIBUTE4
2444 ,ATTRIBUTE5
2445 ,ATTRIBUTE6
2446 ,ATTRIBUTE7
2447 ,ATTRIBUTE8
2448 ,ATTRIBUTE9
2449 ,ATTRIBUTE10
2450 ,ATTRIBUTE11
2451 ,ATTRIBUTE12
2452 ,ATTRIBUTE13
2453 ,ATTRIBUTE14
2454 ,ATTRIBUTE15
2455 ,CUSTOMER_ID
2456 ,CUSTOMER_SITE_USE_ID
2457 ,NORMALIZE
2458 ,LOCATION_ID
2459 ,SCHEDULE_DAY
2460 ,CUST_SHIP_SITE_ID
2461 ,AP_AR_TERM_ID
2462 ,CUST_TRX_TYPE_ID
2463 ,PROJECT_ID
2464 ,TASK_ID
2465 ,ORGANIZATION_ID
2466 ,EXPENDITURE_TYPE
2467 ,EXPENDITURE_ITEM_DATE
2468 ,TAX_GROUP_ID
2469 ,TAX_CODE_ID
2470 ,TAX_CLASSIFICATION_CODE
2471 ,TAX_INCLUDED
2472 ,DISTRIBUTION_SET_ID
2473 ,INV_RULE_ID
2474 ,ACCOUNT_RULE_ID
2475 ,SALESREP_ID
2476 ,APPROVED_BY
2477 ,STATUS
2478 ,INDEX_PERIOD_ID
2479 ,INDEX_TERM_INDICATOR
2480 ,PO_HEADER_ID
2481 ,CUST_PO_NUMBER
2482 ,RECEIPT_METHOD_ID
2483 ,LEGAL_ENTITY_ID
2484 ,PROJECT_ATTRIBUTE_CATEGORY
2485 ,PROJECT_ATTRIBUTE1
2486 ,PROJECT_ATTRIBUTE2
2487 ,PROJECT_ATTRIBUTE3
2488 ,PROJECT_ATTRIBUTE4
2489 ,PROJECT_ATTRIBUTE5
2490 ,PROJECT_ATTRIBUTE6
2491 ,PROJECT_ATTRIBUTE7
2492 ,PROJECT_ATTRIBUTE8
2493 ,PROJECT_ATTRIBUTE9
2494 ,PROJECT_ATTRIBUTE10
2495 ,PROJECT_ATTRIBUTE11
2496 ,PROJECT_ATTRIBUTE12
2497 ,PROJECT_ATTRIBUTE13
2498 ,PROJECT_ATTRIBUTE14
2499 ,PROJECT_ATTRIBUTE15
2500 ,VAR_RENT_INV_ID
2501 ,VAR_RENT_TYPE
2502 ,CHANGED_FLAG
2503 ,NORM_START_DATE
2504 ,TERM_TEMPLATE_ID
2505 ,EVENT_TYPE_CODE
2506 ,LEASE_STATUS
2507 ,NORM_END_DATE
2508 ,RECOVERABLE_FLAG
2509 ,PERIOD_BILLREC_ID
2510 ,AMOUNT_TYPE
2511 ,REC_AGR_LINE_ID
2512 ,GROUPING_RULE_ID
2513 ,AREA_TYPE_CODE
2514 ,AREA
2515 ,TERM_COMMENTS)
2516 VALUES
2517 (PN_PAYMENT_TERMS_HISTORY_S.NEXTVAL
2518 ,SYSDATE
2519 ,FND_GLOBAL.USER_ID
2520 ,SYSDATE
2521 ,FND_GLOBAL.USER_ID
2522 ,FND_GLOBAL.LOGIN_ID
2523 ,tlinfo.payment_term_id
2524 ,l_dist_change_id
2525 ,l_prev_term_hist_id
2526 ,l_adj_type_code
2527 ,NULL -- TOTAL_ADJ_AMOUNT
2528 ,term_rec.PAYMENT_PURPOSE_CODE
2529 ,term_rec.PAYMENT_TERM_TYPE_CODE
2530 ,term_rec.FREQUENCY_CODE
2531 ,term_rec.LEASE_ID
2532 ,term_rec.LEASE_CHANGE_ID
2533 ,term_rec.START_DATE
2534 ,term_rec.END_DATE
2535 ,term_rec.VENDOR_ID
2536 ,term_rec.VENDOR_SITE_ID
2537 ,term_rec.TARGET_DATE
2538 ,term_rec.ACTUAL_AMOUNT
2539 ,term_rec.ESTIMATED_AMOUNT
2540 ,term_rec.SET_OF_BOOKS_ID
2541 ,term_rec.CURRENCY_CODE
2542 ,term_rec.RATE
2543 ,term_rec.ATTRIBUTE_CATEGORY
2544 ,term_rec.ATTRIBUTE1
2545 ,term_rec.ATTRIBUTE2
2546 ,term_rec.ATTRIBUTE3
2547 ,term_rec.ATTRIBUTE4
2548 ,term_rec.ATTRIBUTE5
2549 ,term_rec.ATTRIBUTE6
2550 ,term_rec.ATTRIBUTE7
2551 ,term_rec.ATTRIBUTE8
2552 ,term_rec.ATTRIBUTE9
2553 ,term_rec.ATTRIBUTE10
2554 ,term_rec.ATTRIBUTE11
2555 ,term_rec.ATTRIBUTE12
2556 ,term_rec.ATTRIBUTE13
2557 ,term_rec.ATTRIBUTE14
2558 ,term_rec.ATTRIBUTE15
2559 ,term_rec.CUSTOMER_ID
2560 ,term_rec.CUSTOMER_SITE_USE_ID
2561 ,term_rec.NORMALIZE
2562 ,term_rec.LOCATION_ID
2563 ,term_rec.SCHEDULE_DAY
2564 ,term_rec.CUST_SHIP_SITE_ID
2565 ,term_rec.AP_AR_TERM_ID
2566 ,term_rec.CUST_TRX_TYPE_ID
2567 ,term_rec.PROJECT_ID
2568 ,term_rec.TASK_ID
2569 ,term_rec.ORGANIZATION_ID
2570 ,term_rec.EXPENDITURE_TYPE
2571 ,term_rec.EXPENDITURE_ITEM_DATE
2572 ,term_rec.TAX_GROUP_ID
2573 ,term_rec.TAX_CODE_ID
2574 ,term_rec.TAX_CLASSIFICATION_CODE
2575 ,term_rec.TAX_INCLUDED
2576 ,term_rec.DISTRIBUTION_SET_ID
2577 ,term_rec.INV_RULE_ID
2578 ,term_rec.ACCOUNT_RULE_ID
2579 ,term_rec.SALESREP_ID
2580 ,term_rec.APPROVED_BY
2581 ,term_rec.STATUS
2582 ,term_rec.INDEX_PERIOD_ID
2583 ,term_rec.INDEX_TERM_INDICATOR
2584 ,term_rec.PO_HEADER_ID
2585 ,term_rec.CUST_PO_NUMBER
2586 ,term_rec.RECEIPT_METHOD_ID
2587 ,term_rec.LEGAL_ENTITY_ID
2588 ,term_rec.PROJECT_ATTRIBUTE_CATEGORY
2589 ,term_rec.PROJECT_ATTRIBUTE1
2590 ,term_rec.PROJECT_ATTRIBUTE2
2591 ,term_rec.PROJECT_ATTRIBUTE3
2592 ,term_rec.PROJECT_ATTRIBUTE4
2593 ,term_rec.PROJECT_ATTRIBUTE5
2594 ,term_rec.PROJECT_ATTRIBUTE6
2595 ,term_rec.PROJECT_ATTRIBUTE7
2596 ,term_rec.PROJECT_ATTRIBUTE8
2597 ,term_rec.PROJECT_ATTRIBUTE9
2598 ,term_rec.PROJECT_ATTRIBUTE10
2599 ,term_rec.PROJECT_ATTRIBUTE11
2600 ,term_rec.PROJECT_ATTRIBUTE12
2601 ,term_rec.PROJECT_ATTRIBUTE13
2602 ,term_rec.PROJECT_ATTRIBUTE14
2603 ,term_rec.PROJECT_ATTRIBUTE15
2604 ,term_rec.VAR_RENT_INV_ID
2605 ,term_rec.VAR_RENT_TYPE
2606 ,term_rec.CHANGED_FLAG
2607 ,tlinfo.NORM_START_DATE
2608 ,tlinfo.TERM_TEMPLATE_ID
2609 ,tlinfo.EVENT_TYPE_CODE
2610 ,tlinfo.LEASE_STATUS
2611 ,tlinfo.NORM_END_DATE
2612 ,term_rec.RECOVERABLE_FLAG
2613 ,tlinfo.PERIOD_BILLREC_ID
2614 ,tlinfo.AMOUNT_TYPE
2615 ,tlinfo.REC_AGR_LINE_ID
2616 ,term_rec.GROUPING_RULE_ID
2617 ,term_rec.AREA_TYPE_CODE
2618 ,term_rec.AREA
2619 ,term_rec.TERM_COMMENTS);
2620 END IF;
2621
2622 IF p_change_mode = 'UPDATE' THEN
2623
2624 pnp_debug_pkg.debug('create_hist_corr_upd : '||p_change_mode|| '- p_eff_str_dt : '||p_eff_str_dt);
2625 pnp_debug_pkg.debug('create_hist_corr_upd : '||p_change_mode|| '- p_eff_end_dt : '||p_eff_end_dt);
2626 pnp_debug_pkg.debug('create_hist_corr_upd : '||p_change_mode|| '- term_str_dt : '||term_rec.start_date);
2627 pnp_debug_pkg.debug('create_hist_corr_upd : '||p_change_mode|| '- term_end_dt : '||term_rec.end_date);
2628
2629 IF (p_eff_str_dt > term_rec.start_date AND
2630 NVL(p_eff_end_dt, term_rec.end_date) <= term_rec.end_date) THEN
2631
2632 SELECT pn_payment_terms_s.nextval
2633 INTO l_new_term_id
2634 FROM DUAL;
2635
2636 l_adj_type_code := NULL;
2637 l_term_comments := NULL;
2638
2639 pnp_debug_pkg.debug('create_hist_corr_upd : '||p_change_mode|| '- l_new_term_id: '||l_new_term_id);
2640 pnp_debug_pkg.debug('create_hist_corr_upd : '||p_change_mode|| '- Updating term with new Term Id');
2641
2642 UPDATE pn_payment_terms_all
2643 SET start_date = p_eff_str_dt,
2644 end_date = NVL(p_eff_end_dt,term_rec.end_date),
2645 term_altered_flag = 'Y',
2646 changed_flag = 'N',
2647 payment_term_id = l_new_term_id
2648 WHERE payment_term_id = p_term_id;
2649
2650 SELECT pn_distributions_history_s1.NEXTVAL
2651 INTO l_dist_change_id
2652 FROM DUAL;
2653
2654 pnp_debug_pkg.debug('create_hist_corr_upd : '||p_change_mode|| '- l_dist_change_id: '||l_dist_change_id);
2655
2656 FOR dist_rec IN get_dist_cur(p_term_id) LOOP
2657
2658 l_new_dist_id := NULL;
2659 SELECT pn_distributions_s.nextval
2660 INTO l_new_dist_id
2661 FROM DUAL;
2662
2663 pnp_debug_pkg.debug('create_hist_corr_upd : '||p_change_mode|| '- l_new_dist_id : '||l_new_dist_id);
2664
2665 UPDATE pn_distributions_all
2666 SET distribution_id = l_new_dist_id,
2667 payment_term_id = l_new_term_id
2668 WHERE distribution_id = dist_rec.distribution_id;
2669
2670 pnp_debug_pkg.debug('create_hist_corr_upd : '||p_change_mode|| '- Inserting into Dist. Hist.');
2671
2672 INSERT INTO pn_distributions_history
2673 (DISTRIBUTION_HISTORY_ID
2674 ,LAST_UPDATE_DATE
2675 ,LAST_UPDATED_BY
2676 ,CREATION_DATE
2677 ,CREATED_BY
2678 ,LAST_UPDATE_LOGIN
2679 ,DISTRIBUTION_CHANGE_ID
2680 ,DISTRIBUTION_ID
2681 ,ACCOUNT_ID
2682 ,ACCOUNT_CLASS
2683 ,PERCENTAGE
2684 ,LINE_NUMBER
2685 ,ATTRIBUTE_CATEGORY
2686 ,ATTRIBUTE1
2687 ,ATTRIBUTE2
2688 ,ATTRIBUTE3
2689 ,ATTRIBUTE4
2690 ,ATTRIBUTE5
2691 ,ATTRIBUTE6
2692 ,ATTRIBUTE7
2693 ,ATTRIBUTE8
2694 ,ATTRIBUTE9
2695 ,ATTRIBUTE10
2696 ,ATTRIBUTE11
2697 ,ATTRIBUTE12
2698 ,ATTRIBUTE13
2699 ,ATTRIBUTE14
2700 ,ATTRIBUTE15)
2701 VALUES
2702 (pn_distributions_history_s.NEXTVAL
2703 ,SYSDATE
2704 ,FND_GLOBAL.USER_ID
2705 ,SYSDATE
2706 ,FND_GLOBAL.USER_ID
2707 ,FND_GLOBAL.LOGIN_ID
2708 ,l_dist_change_id
2709 ,l_new_dist_id
2710 ,dist_rec.ACCOUNT_ID
2711 ,dist_rec.ACCOUNT_CLASS
2712 ,dist_rec.PERCENTAGE
2713 ,dist_rec.LINE_NUMBER
2714 ,dist_rec.ATTRIBUTE_CATEGORY
2715 ,dist_rec.ATTRIBUTE1
2716 ,dist_rec.ATTRIBUTE2
2717 ,dist_rec.ATTRIBUTE3
2718 ,dist_rec.ATTRIBUTE4
2719 ,dist_rec.ATTRIBUTE5
2720 ,dist_rec.ATTRIBUTE6
2721 ,dist_rec.ATTRIBUTE7
2722 ,dist_rec.ATTRIBUTE8
2723 ,dist_rec.ATTRIBUTE9
2724 ,dist_rec.ATTRIBUTE10
2725 ,dist_rec.ATTRIBUTE11
2726 ,dist_rec.ATTRIBUTE12
2727 ,dist_rec.ATTRIBUTE13
2728 ,dist_rec.ATTRIBUTE14
2729 ,dist_rec.ATTRIBUTE15);
2730
2731 END LOOP;
2732
2733 ELSIF (p_eff_str_dt = term_rec.start_date AND
2734 NVL(p_eff_end_dt, term_rec.end_date) <= term_rec.end_date) THEN
2735
2736 pnp_debug_pkg.debug('create_hist_corr_upd : '||p_change_mode|| '- Updating term with Eff. End Date');
2737
2738 UPDATE pn_payment_terms_all
2739 SET end_date = NVL(p_eff_end_dt, term_rec.end_date),
2740 term_altered_flag = 'Y',
2741 changed_flag = 'Y'
2742 WHERE payment_term_id = p_term_id;
2743
2744 l_new_term_id := tlinfo.payment_term_id;
2745 l_adj_type_code := 'ADJ-PCON';
2746 l_term_comments := term_rec.term_comments;
2747
2748 pnp_debug_pkg.debug('create_hist_corr_upd : '||p_change_mode|| '- l_new_term_id: '||l_new_term_id);
2749
2750 END IF;
2751
2752 IF (p_eff_str_dt >= term_rec.start_date AND
2753 NVL(p_eff_end_dt, term_rec.end_date) <= term_rec.end_date) THEN
2754
2755 pnp_debug_pkg.debug('create_hist_corr_upd : '||p_change_mode|| '- l_new_term_id: '||l_new_term_id);
2756 pnp_debug_pkg.debug('create_hist_corr_upd : '||p_change_mode|| '- l_dist_change_id: '||l_dist_change_id);
2757 pnp_debug_pkg.debug('create_hist_corr_upd : '||p_change_mode|| '- Inserting into Term Hist.');
2758
2759 INSERT INTO pn_payment_terms_history
2760 (TERM_HISTORY_ID
2761 ,LAST_UPDATE_DATE
2762 ,LAST_UPDATED_BY
2763 ,CREATION_DATE
2764 ,CREATED_BY
2765 ,LAST_UPDATE_LOGIN
2766 ,PAYMENT_TERM_ID
2767 ,DISTRIBUTION_CHANGE_ID
2768 ,PREV_TERM_HISTORY_ID
2769 ,ADJUSTMENT_TYPE_CODE
2770 ,TOTAL_ADJ_AMOUNT
2771 ,PAYMENT_PURPOSE_CODE
2772 ,PAYMENT_TERM_TYPE_CODE
2773 ,FREQUENCY_CODE
2774 ,LEASE_ID
2775 ,LEASE_CHANGE_ID
2776 ,START_DATE
2777 ,END_DATE
2778 ,VENDOR_ID
2779 ,VENDOR_SITE_ID
2780 ,TARGET_DATE
2781 ,ACTUAL_AMOUNT
2782 ,ESTIMATED_AMOUNT
2783 ,SET_OF_BOOKS_ID
2784 ,CURRENCY_CODE
2785 ,RATE
2786 ,ATTRIBUTE_CATEGORY
2787 ,ATTRIBUTE1
2788 ,ATTRIBUTE2
2789 ,ATTRIBUTE3
2790 ,ATTRIBUTE4
2791 ,ATTRIBUTE5
2792 ,ATTRIBUTE6
2793 ,ATTRIBUTE7
2794 ,ATTRIBUTE8
2795 ,ATTRIBUTE9
2796 ,ATTRIBUTE10
2797 ,ATTRIBUTE11
2798 ,ATTRIBUTE12
2799 ,ATTRIBUTE13
2800 ,ATTRIBUTE14
2801 ,ATTRIBUTE15
2802 ,CUSTOMER_ID
2803 ,CUSTOMER_SITE_USE_ID
2804 ,NORMALIZE
2805 ,LOCATION_ID
2806 ,SCHEDULE_DAY
2807 ,CUST_SHIP_SITE_ID
2808 ,AP_AR_TERM_ID
2809 ,CUST_TRX_TYPE_ID
2810 ,PROJECT_ID
2811 ,TASK_ID
2812 ,ORGANIZATION_ID
2813 ,EXPENDITURE_TYPE
2814 ,EXPENDITURE_ITEM_DATE
2815 ,TAX_GROUP_ID
2816 ,TAX_CODE_ID
2817 ,TAX_CLASSIFICATION_CODE
2818 ,TAX_INCLUDED
2819 ,DISTRIBUTION_SET_ID
2820 ,INV_RULE_ID
2821 ,ACCOUNT_RULE_ID
2822 ,SALESREP_ID
2823 ,APPROVED_BY
2824 ,STATUS
2825 ,INDEX_PERIOD_ID
2826 ,INDEX_TERM_INDICATOR
2827 ,PO_HEADER_ID
2828 ,CUST_PO_NUMBER
2829 ,RECEIPT_METHOD_ID
2830 ,LEGAL_ENTITY_ID
2831 ,PROJECT_ATTRIBUTE_CATEGORY
2832 ,PROJECT_ATTRIBUTE1
2833 ,PROJECT_ATTRIBUTE2
2834 ,PROJECT_ATTRIBUTE3
2835 ,PROJECT_ATTRIBUTE4
2836 ,PROJECT_ATTRIBUTE5
2837 ,PROJECT_ATTRIBUTE6
2838 ,PROJECT_ATTRIBUTE7
2839 ,PROJECT_ATTRIBUTE8
2840 ,PROJECT_ATTRIBUTE9
2841 ,PROJECT_ATTRIBUTE10
2842 ,PROJECT_ATTRIBUTE11
2843 ,PROJECT_ATTRIBUTE12
2844 ,PROJECT_ATTRIBUTE13
2845 ,PROJECT_ATTRIBUTE14
2846 ,PROJECT_ATTRIBUTE15
2847 ,VAR_RENT_INV_ID
2848 ,VAR_RENT_TYPE
2849 ,CHANGED_FLAG
2850 ,NORM_START_DATE
2851 ,TERM_TEMPLATE_ID
2852 ,EVENT_TYPE_CODE
2853 ,LEASE_STATUS
2854 ,NORM_END_DATE
2855 ,RECOVERABLE_FLAG
2856 ,PERIOD_BILLREC_ID
2857 ,AMOUNT_TYPE
2858 ,REC_AGR_LINE_ID
2859 ,GROUPING_RULE_ID
2860 ,AREA_TYPE_CODE
2861 ,AREA
2862 ,TERM_COMMENTS)
2863 VALUES
2864 (PN_PAYMENT_TERMS_HISTORY_S.NEXTVAL
2865 ,SYSDATE
2866 ,FND_GLOBAL.USER_ID
2867 ,SYSDATE
2868 ,FND_GLOBAL.USER_ID
2869 ,FND_GLOBAL.LOGIN_ID
2870 ,l_new_term_id
2871 ,l_dist_change_id
2872 ,l_prev_term_hist_id
2873 ,l_adj_type_code
2874 ,NULL -- TOTAL_ADJ_AMOUNT
2875 ,term_rec.PAYMENT_PURPOSE_CODE
2876 ,term_rec.PAYMENT_TERM_TYPE_CODE
2877 ,term_rec.FREQUENCY_CODE
2878 ,term_rec.LEASE_ID
2879 ,term_rec.LEASE_CHANGE_ID
2880 ,p_eff_str_dt
2881 ,NVL(p_eff_end_dt,term_rec.end_date)
2882 ,term_rec.VENDOR_ID
2883 ,term_rec.VENDOR_SITE_ID
2884 ,term_rec.TARGET_DATE
2885 ,term_rec.ACTUAL_AMOUNT
2886 ,term_rec.ESTIMATED_AMOUNT
2887 ,term_rec.SET_OF_BOOKS_ID
2888 ,term_rec.CURRENCY_CODE
2889 ,term_rec.RATE
2890 ,term_rec.ATTRIBUTE_CATEGORY
2891 ,term_rec.ATTRIBUTE1
2892 ,term_rec.ATTRIBUTE2
2893 ,term_rec.ATTRIBUTE3
2894 ,term_rec.ATTRIBUTE4
2895 ,term_rec.ATTRIBUTE5
2896 ,term_rec.ATTRIBUTE6
2897 ,term_rec.ATTRIBUTE7
2898 ,term_rec.ATTRIBUTE8
2899 ,term_rec.ATTRIBUTE9
2900 ,term_rec.ATTRIBUTE10
2901 ,term_rec.ATTRIBUTE11
2902 ,term_rec.ATTRIBUTE12
2903 ,term_rec.ATTRIBUTE13
2904 ,term_rec.ATTRIBUTE14
2905 ,term_rec.ATTRIBUTE15
2906 ,term_rec.CUSTOMER_ID
2907 ,term_rec.CUSTOMER_SITE_USE_ID
2908 ,term_rec.NORMALIZE
2909 ,term_rec.LOCATION_ID
2910 ,term_rec.SCHEDULE_DAY
2911 ,term_rec.CUST_SHIP_SITE_ID
2912 ,term_rec.AP_AR_TERM_ID
2913 ,term_rec.CUST_TRX_TYPE_ID
2914 ,term_rec.PROJECT_ID
2915 ,term_rec.TASK_ID
2916 ,term_rec.ORGANIZATION_ID
2917 ,term_rec.EXPENDITURE_TYPE
2918 ,term_rec.EXPENDITURE_ITEM_DATE
2919 ,term_rec.TAX_GROUP_ID
2920 ,term_rec.TAX_CODE_ID
2921 ,term_rec.TAX_CLASSIFICATION_CODE
2922 ,term_rec.TAX_INCLUDED
2923 ,term_rec.DISTRIBUTION_SET_ID
2924 ,term_rec.INV_RULE_ID
2925 ,term_rec.ACCOUNT_RULE_ID
2926 ,term_rec.SALESREP_ID
2927 ,term_rec.APPROVED_BY
2928 ,term_rec.STATUS
2929 ,term_rec.INDEX_PERIOD_ID
2930 ,term_rec.INDEX_TERM_INDICATOR
2931 ,term_rec.PO_HEADER_ID
2932 ,term_rec.CUST_PO_NUMBER
2933 ,term_rec.RECEIPT_METHOD_ID
2934 ,term_rec.LEGAL_ENTITY_ID
2935 ,term_rec.PROJECT_ATTRIBUTE_CATEGORY
2936 ,term_rec.PROJECT_ATTRIBUTE1
2937 ,term_rec.PROJECT_ATTRIBUTE2
2938 ,term_rec.PROJECT_ATTRIBUTE3
2939 ,term_rec.PROJECT_ATTRIBUTE4
2940 ,term_rec.PROJECT_ATTRIBUTE5
2941 ,term_rec.PROJECT_ATTRIBUTE6
2942 ,term_rec.PROJECT_ATTRIBUTE7
2943 ,term_rec.PROJECT_ATTRIBUTE8
2944 ,term_rec.PROJECT_ATTRIBUTE9
2945 ,term_rec.PROJECT_ATTRIBUTE10
2946 ,term_rec.PROJECT_ATTRIBUTE11
2947 ,term_rec.PROJECT_ATTRIBUTE12
2948 ,term_rec.PROJECT_ATTRIBUTE13
2949 ,term_rec.PROJECT_ATTRIBUTE14
2950 ,term_rec.PROJECT_ATTRIBUTE15
2951 ,term_rec.VAR_RENT_INV_ID
2952 ,term_rec.VAR_RENT_TYPE
2953 ,term_rec.CHANGED_FLAG
2954 ,tlinfo.NORM_START_DATE
2955 ,tlinfo.TERM_TEMPLATE_ID
2956 ,tlinfo.EVENT_TYPE_CODE
2957 ,tlinfo.LEASE_STATUS
2958 ,tlinfo.NORM_END_DATE
2959 ,term_rec.RECOVERABLE_FLAG
2960 ,tlinfo.PERIOD_BILLREC_ID
2961 ,tlinfo.AMOUNT_TYPE
2962 ,tlinfo.REC_AGR_LINE_ID
2963 ,term_rec.GROUPING_RULE_ID
2964 ,term_rec.AREA_TYPE_CODE
2965 ,term_rec.AREA
2966 ,l_term_comments);
2967
2968 END IF;
2969
2970 pnp_debug_pkg.debug('create_hist_corr_upd : '||p_change_mode|| '- Setting value of counter n');
2971
2972 IF p_eff_str_dt <> term_rec.start_date THEN
2973 IF NVL(p_eff_end_dt, term_rec.end_date) = term_rec.end_date THEN
2974 n := 1;
2975 ELSIF NVL(p_eff_end_dt, term_rec.end_date) < term_rec.end_date THEN
2976 n := 2;
2977 END IF;
2978 ELSIF p_eff_str_dt = term_rec.start_date THEN
2979 n := 1;
2980 END IF;
2981
2982 pnp_debug_pkg.debug('create_hist_corr_upd : '||p_change_mode|| '- Check counter value n :'||n);
2983
2984 FOR i in 1..n LOOP
2985
2986 pnp_debug_pkg.debug('create_hist_corr_upd : '||p_change_mode|| '- In Term Loop for i :'||i);
2987
2988 IF p_eff_str_dt <> term_rec.start_date AND
2989 i = 1 THEN
2990
2991 l_term_id := p_term_id;
2992 l_str_dt := term_rec.start_date;
2993 l_end_dt := p_eff_str_dt - 1;
2994 l_adj_type_code := 'ADJ-PCON';
2995 l_term_comments := term_rec.term_comments;
2996 l_last_update_date := SYSDATE;
2997 l_last_updated_by := FND_GLOBAL.USER_ID;
2998 l_creation_date := tlinfo.creation_date;
2999 l_created_by := tlinfo.created_by;
3000 l_last_update_login := FND_GLOBAL.LOGIN_ID;
3001 l_changed_flag := 'Y';
3002
3003 ELSIF (p_eff_str_dt <> term_rec.start_date AND
3004 i = 2) OR
3005 (p_eff_str_dt = term_rec.start_date AND
3006 i = 1) THEN
3007
3008 SELECT pn_payment_terms_s.nextval
3009 INTO l_term_id
3010 FROM DUAL;
3011
3012 l_str_dt := NVL(p_eff_end_dt, term_rec.end_date) + 1;
3013 l_end_dt := term_rec.end_date;
3014 l_adj_type_code := NULL;
3015 l_term_comments := NULL;
3016 l_last_update_date := SYSDATE;
3017 l_last_updated_by := FND_GLOBAL.USER_ID;
3018 l_creation_date := SYSDATE;
3019 l_created_by := FND_GLOBAL.USER_ID;
3020 l_last_update_login := FND_GLOBAL.LOGIN_ID;
3021 l_changed_flag := 'N';
3022
3023 END IF;
3024
3025 pnp_debug_pkg.debug('create_hist_corr_upd : '||p_change_mode|| '- l_term_id : '||l_term_id);
3026 pnp_debug_pkg.debug('create_hist_corr_upd : '||p_change_mode|| '- l_str_dt : '||l_str_dt);
3027 pnp_debug_pkg.debug('create_hist_corr_upd : '||p_change_mode|| '- l_end_dt : '||l_end_dt);
3028 pnp_debug_pkg.debug('create_hist_corr_upd : '||p_change_mode|| '- Inserting into Terms table.');
3029
3030 INSERT INTO pn_payment_terms_all
3031 (PAYMENT_TERM_ID
3032 ,LAST_UPDATE_DATE
3033 ,LAST_UPDATED_BY
3034 ,CREATION_DATE
3035 ,CREATED_BY
3036 ,LAST_UPDATE_LOGIN
3037 ,PAYMENT_PURPOSE_CODE
3038 ,PAYMENT_TERM_TYPE_CODE
3039 ,FREQUENCY_CODE
3040 ,LEASE_ID
3041 ,LEASE_CHANGE_ID
3042 ,START_DATE
3043 ,END_DATE
3044 ,VENDOR_ID
3045 ,VENDOR_SITE_ID
3046 ,CUSTOMER_ID
3047 ,CUSTOMER_SITE_USE_ID
3048 ,TARGET_DATE
3049 ,ACTUAL_AMOUNT
3050 ,ESTIMATED_AMOUNT
3051 ,SET_OF_BOOKS_ID
3052 ,CURRENCY_CODE
3053 ,RATE
3054 ,NORMALIZE
3055 ,LOCATION_ID
3056 ,SCHEDULE_DAY
3057 ,CUST_SHIP_SITE_ID
3058 ,AP_AR_TERM_ID
3059 ,CUST_TRX_TYPE_ID
3060 ,PROJECT_ID
3061 ,TASK_ID
3062 ,ORGANIZATION_ID
3063 ,EXPENDITURE_TYPE
3064 ,EXPENDITURE_ITEM_DATE
3065 ,TAX_GROUP_ID
3066 ,TAX_CODE_ID
3067 ,TAX_CLASSIFICATION_CODE
3068 ,TAX_INCLUDED
3069 ,DISTRIBUTION_SET_ID
3070 ,INV_RULE_ID
3071 ,ACCOUNT_RULE_ID
3072 ,SALESREP_ID
3073 ,APPROVED_BY
3074 ,STATUS
3075 ,INDEX_PERIOD_ID
3076 ,INDEX_TERM_INDICATOR
3077 ,PO_HEADER_ID
3078 ,CUST_PO_NUMBER
3079 ,RECEIPT_METHOD_ID
3080 ,VAR_RENT_INV_ID
3081 ,VAR_RENT_TYPE
3082 ,PERIOD_BILLREC_ID
3083 ,REC_AGR_LINE_ID
3084 ,AMOUNT_TYPE
3085 ,CHANGED_FLAG
3086 ,TERM_TEMPLATE_ID
3087 ,LEGAL_ENTITY_ID
3088 ,ATTRIBUTE_CATEGORY
3089 ,ATTRIBUTE1
3090 ,ATTRIBUTE2
3091 ,ATTRIBUTE3
3092 ,ATTRIBUTE4
3093 ,ATTRIBUTE5
3094 ,ATTRIBUTE6
3095 ,ATTRIBUTE7
3096 ,ATTRIBUTE8
3097 ,ATTRIBUTE9
3098 ,ATTRIBUTE10
3099 ,ATTRIBUTE11
3100 ,ATTRIBUTE12
3101 ,ATTRIBUTE13
3102 ,ATTRIBUTE14
3103 ,ATTRIBUTE15
3104 ,PROJECT_ATTRIBUTE_CATEGORY
3105 ,PROJECT_ATTRIBUTE1
3106 ,PROJECT_ATTRIBUTE2
3107 ,PROJECT_ATTRIBUTE3
3108 ,PROJECT_ATTRIBUTE4
3109 ,PROJECT_ATTRIBUTE5
3110 ,PROJECT_ATTRIBUTE6
3111 ,PROJECT_ATTRIBUTE7
3112 ,PROJECT_ATTRIBUTE8
3113 ,PROJECT_ATTRIBUTE9
3114 ,PROJECT_ATTRIBUTE10
3115 ,PROJECT_ATTRIBUTE11
3116 ,PROJECT_ATTRIBUTE12
3117 ,PROJECT_ATTRIBUTE13
3118 ,PROJECT_ATTRIBUTE14
3119 ,PROJECT_ATTRIBUTE15
3120 ,org_id
3121 ,lease_status
3122 ,recoverable_flag
3123 ,area_type_code
3124 ,area
3125 ,grouping_rule_id
3126 ,term_altered_flag
3127 ,source_code
3128 ,term_comments)
3129 VALUES
3130 (l_term_id
3131 ,l_last_update_date
3132 ,l_last_updated_by
3133 ,l_creation_date
3134 ,l_created_by
3135 ,l_last_update_login
3136 ,tlinfo.PAYMENT_PURPOSE_CODE
3137 ,tlinfo.PAYMENT_TERM_TYPE_CODE
3138 ,tlinfo.FREQUENCY_CODE
3139 ,tlinfo.LEASE_ID
3140 ,term_rec.LEASE_CHANGE_ID
3141 ,l_str_dt
3142 ,l_end_dt
3143 ,tlinfo.VENDOR_ID
3144 ,tlinfo.VENDOR_SITE_ID
3145 ,tlinfo.CUSTOMER_ID
3146 ,tlinfo.CUSTOMER_SITE_USE_ID
3147 ,tlinfo.TARGET_DATE
3148 ,tlinfo.ACTUAL_AMOUNT
3149 ,tlinfo.ESTIMATED_AMOUNT
3150 ,tlinfo.SET_OF_BOOKS_ID
3151 ,tlinfo.CURRENCY_CODE
3152 ,tlinfo.RATE
3153 ,tlinfo.NORMALIZE
3154 ,tlinfo.LOCATION_ID
3155 ,tlinfo.SCHEDULE_DAY
3156 ,tlinfo.CUST_SHIP_SITE_ID
3157 ,tlinfo.AP_AR_TERM_ID
3158 ,tlinfo.CUST_TRX_TYPE_ID
3159 ,tlinfo.PROJECT_ID
3160 ,tlinfo.TASK_ID
3161 ,tlinfo.ORGANIZATION_ID
3162 ,tlinfo.EXPENDITURE_TYPE
3163 ,tlinfo.EXPENDITURE_ITEM_DATE
3164 ,tlinfo.TAX_GROUP_ID
3165 ,tlinfo.TAX_CODE_ID
3166 ,tlinfo.TAX_CLASSIFICATION_CODE
3167 ,tlinfo.TAX_INCLUDED
3168 ,tlinfo.DISTRIBUTION_SET_ID
3169 ,tlinfo.INV_RULE_ID
3170 ,tlinfo.ACCOUNT_RULE_ID
3171 ,tlinfo.SALESREP_ID
3172 ,tlinfo.APPROVED_BY
3173 ,tlinfo.STATUS
3174 ,tlinfo.INDEX_PERIOD_ID
3175 ,tlinfo.INDEX_TERM_INDICATOR
3176 ,tlinfo.PO_HEADER_ID
3177 ,tlinfo.CUST_PO_NUMBER
3178 ,tlinfo.RECEIPT_METHOD_ID
3179 ,tlinfo.VAR_RENT_INV_ID
3180 ,tlinfo.VAR_RENT_TYPE
3181 ,tlinfo.PERIOD_BILLREC_ID
3182 ,tlinfo.REC_AGR_LINE_ID
3183 ,tlinfo.AMOUNT_TYPE
3184 ,l_changed_flag
3185 ,tlinfo.TERM_TEMPLATE_ID
3186 ,tlinfo.LEGAL_ENTITY_ID
3187 ,tlinfo.ATTRIBUTE_CATEGORY
3188 ,tlinfo.ATTRIBUTE1
3189 ,tlinfo.ATTRIBUTE2
3190 ,tlinfo.ATTRIBUTE3
3191 ,tlinfo.ATTRIBUTE4
3192 ,tlinfo.ATTRIBUTE5
3193 ,tlinfo.ATTRIBUTE6
3194 ,tlinfo.ATTRIBUTE7
3195 ,tlinfo.ATTRIBUTE8
3196 ,tlinfo.ATTRIBUTE9
3197 ,tlinfo.ATTRIBUTE10
3198 ,tlinfo.ATTRIBUTE11
3199 ,tlinfo.ATTRIBUTE12
3200 ,tlinfo.ATTRIBUTE13
3201 ,tlinfo.ATTRIBUTE14
3202 ,tlinfo.ATTRIBUTE15
3203 ,tlinfo.PROJECT_ATTRIBUTE_CATEGORY
3204 ,tlinfo.PROJECT_ATTRIBUTE1
3205 ,tlinfo.PROJECT_ATTRIBUTE2
3206 ,tlinfo.PROJECT_ATTRIBUTE3
3207 ,tlinfo.PROJECT_ATTRIBUTE4
3208 ,tlinfo.PROJECT_ATTRIBUTE5
3209 ,tlinfo.PROJECT_ATTRIBUTE6
3210 ,tlinfo.PROJECT_ATTRIBUTE7
3211 ,tlinfo.PROJECT_ATTRIBUTE8
3212 ,tlinfo.PROJECT_ATTRIBUTE9
3213 ,tlinfo.PROJECT_ATTRIBUTE10
3214 ,tlinfo.PROJECT_ATTRIBUTE11
3215 ,tlinfo.PROJECT_ATTRIBUTE12
3216 ,tlinfo.PROJECT_ATTRIBUTE13
3217 ,tlinfo.PROJECT_ATTRIBUTE14
3218 ,tlinfo.PROJECT_ATTRIBUTE15
3219 ,tlinfo.org_id
3220 ,tlinfo.lease_status
3221 ,tlinfo.recoverable_flag
3222 ,tlinfo.area_type_code
3223 ,tlinfo.area
3224 ,tlinfo.grouping_rule_id
3225 ,'Y'
3226 ,tlinfo.source_code
3227 ,tlinfo.term_comments);
3228
3229 IF NVL(p_hist_dist_tab.count,0) <> 0 THEN
3230
3231 SELECT pn_distributions_history_s1.NEXTVAL
3232 INTO l_dist_change_id
3233 FROM DUAL;
3234
3235 l_insert_dist_hist := 'N';
3236
3237 FOR j in 1..p_hist_dist_tab.count LOOP
3238
3239 pnp_debug_pkg.debug('create_hist_corr_upd : '||p_change_mode|| '- In Dist. Loop for j :'||j);
3240
3241 IF p_eff_str_dt <> term_rec.start_date AND
3242 i = 1 THEN
3243
3244 l_dist_id := p_hist_dist_tab(j).distribution_id;
3245 l_insert_dist_hist := 'N';
3246
3247 ELSIF (p_eff_str_dt <> term_rec.start_date AND
3248 i = 2) OR
3249 (p_eff_str_dt = term_rec.start_date AND
3250 i = 1) THEN
3251
3252 SELECT pn_distributions_s.NEXTVAL
3253 INTO l_dist_id
3254 FROM DUAL;
3255
3256 l_insert_dist_hist := 'Y';
3257
3258 END IF;
3259
3260 pnp_debug_pkg.debug('create_hist_corr_upd : '||p_change_mode|| '- l_dist_id : '||l_dist_id);
3261 pnp_debug_pkg.debug('create_hist_corr_upd : '||p_change_mode|| '- l_insert_dist_hist: '||l_insert_dist_hist);
3262 pnp_debug_pkg.debug('create_hist_corr_upd : '||p_change_mode|| '- Inserting into Dist. table.');
3263
3264 INSERT INTO pn_distributions_all
3265 (distribution_id
3266 ,account_id
3267 ,payment_term_id
3268 ,term_template_id
3269 ,account_class
3270 ,percentage
3271 ,line_number
3272 ,last_update_date
3273 ,last_updated_by
3274 ,creation_date
3275 ,created_by
3276 ,last_update_login
3277 ,attribute_category
3278 ,attribute1
3279 ,attribute2
3280 ,attribute3
3281 ,attribute4
3282 ,attribute5
3283 ,attribute6
3284 ,attribute7
3285 ,attribute8
3286 ,attribute9
3287 ,attribute10
3288 ,attribute11
3289 ,attribute12
3290 ,attribute13
3291 ,attribute14
3292 ,attribute15
3293 ,org_id)
3294 VALUES
3295 (l_dist_id
3296 ,p_hist_dist_tab(j).account_id
3297 ,l_term_id
3298 ,p_hist_dist_tab(j).term_template_id
3299 ,p_hist_dist_tab(j).account_class
3300 ,p_hist_dist_tab(j).percentage
3301 ,p_hist_dist_tab(j).line_number
3302 ,l_last_update_date
3303 ,l_last_updated_by
3304 ,l_creation_date
3305 ,l_created_by
3306 ,l_last_update_login
3307 ,p_hist_dist_tab(j).attribute_category
3308 ,p_hist_dist_tab(j).attribute1
3309 ,p_hist_dist_tab(j).attribute2
3310 ,p_hist_dist_tab(j).attribute3
3311 ,p_hist_dist_tab(j).attribute4
3312 ,p_hist_dist_tab(j).attribute5
3313 ,p_hist_dist_tab(j).attribute6
3314 ,p_hist_dist_tab(j).attribute7
3315 ,p_hist_dist_tab(j).attribute8
3316 ,p_hist_dist_tab(j).attribute9
3317 ,p_hist_dist_tab(j).attribute10
3318 ,p_hist_dist_tab(j).attribute11
3319 ,p_hist_dist_tab(j).attribute12
3320 ,p_hist_dist_tab(j).attribute13
3321 ,p_hist_dist_tab(j).attribute14
3322 ,p_hist_dist_tab(j).attribute15
3323 ,p_hist_dist_tab(j).org_id);
3324
3325 IF l_insert_dist_hist = 'Y' THEN
3326
3327 pnp_debug_pkg.debug('create_hist_corr_upd : '||p_change_mode|| '- Inserting into Dist. Hist. table.');
3328
3329 INSERT INTO pn_distributions_history
3330 (DISTRIBUTION_HISTORY_ID
3331 ,LAST_UPDATE_DATE
3332 ,LAST_UPDATED_BY
3333 ,CREATION_DATE
3334 ,CREATED_BY
3335 ,LAST_UPDATE_LOGIN
3336 ,DISTRIBUTION_CHANGE_ID
3337 ,DISTRIBUTION_ID
3338 ,ACCOUNT_ID
3339 ,ACCOUNT_CLASS
3340 ,PERCENTAGE
3341 ,LINE_NUMBER
3342 ,ATTRIBUTE_CATEGORY
3343 ,ATTRIBUTE1
3344 ,ATTRIBUTE2
3345 ,ATTRIBUTE3
3346 ,ATTRIBUTE4
3347 ,ATTRIBUTE5
3348 ,ATTRIBUTE6
3349 ,ATTRIBUTE7
3350 ,ATTRIBUTE8
3351 ,ATTRIBUTE9
3352 ,ATTRIBUTE10
3353 ,ATTRIBUTE11
3354 ,ATTRIBUTE12
3355 ,ATTRIBUTE13
3356 ,ATTRIBUTE14
3357 ,ATTRIBUTE15)
3358 VALUES
3359 (pn_distributions_history_s.NEXTVAL
3360 ,SYSDATE
3361 ,FND_GLOBAL.USER_ID
3362 ,SYSDATE
3363 ,FND_GLOBAL.USER_ID
3364 ,FND_GLOBAL.LOGIN_ID
3365 ,l_dist_change_id
3366 ,l_dist_id
3367 ,p_hist_dist_tab(j).ACCOUNT_ID
3368 ,p_hist_dist_tab(j).ACCOUNT_CLASS
3369 ,p_hist_dist_tab(j).PERCENTAGE
3370 ,p_hist_dist_tab(j).LINE_NUMBER
3371 ,p_hist_dist_tab(j).ATTRIBUTE_CATEGORY
3372 ,p_hist_dist_tab(j).ATTRIBUTE1
3373 ,p_hist_dist_tab(j).ATTRIBUTE2
3374 ,p_hist_dist_tab(j).ATTRIBUTE3
3375 ,p_hist_dist_tab(j).ATTRIBUTE4
3376 ,p_hist_dist_tab(j).ATTRIBUTE5
3377 ,p_hist_dist_tab(j).ATTRIBUTE6
3378 ,p_hist_dist_tab(j).ATTRIBUTE7
3379 ,p_hist_dist_tab(j).ATTRIBUTE8
3380 ,p_hist_dist_tab(j).ATTRIBUTE9
3381 ,p_hist_dist_tab(j).ATTRIBUTE10
3382 ,p_hist_dist_tab(j).ATTRIBUTE11
3383 ,p_hist_dist_tab(j).ATTRIBUTE12
3384 ,p_hist_dist_tab(j).ATTRIBUTE13
3385 ,p_hist_dist_tab(j).ATTRIBUTE14
3386 ,p_hist_dist_tab(j).ATTRIBUTE15);
3387 END IF;
3388 END LOOP;
3389
3390 IF l_insert_dist_hist = 'N' THEN
3391
3392 OPEN get_max_dist_change_id_cur(p_term_id);
3393 FETCH get_max_dist_change_id_cur INTO l_dist_change_id;
3394 CLOSE get_max_dist_change_id_cur;
3395
3396 pnp_debug_pkg.debug('create_hist_corr_upd : '||p_change_mode|| '- get_max_dist_change_id : '||l_dist_change_id);
3397 END IF;
3398 END IF;
3399
3400 pnp_debug_pkg.debug('create_hist_corr_upd : '||p_change_mode|| '- Inserting into Term Hist. table.');
3401
3402 INSERT INTO pn_payment_terms_history
3403 (TERM_HISTORY_ID
3404 ,LAST_UPDATE_DATE
3405 ,LAST_UPDATED_BY
3406 ,CREATION_DATE
3407 ,CREATED_BY
3408 ,LAST_UPDATE_LOGIN
3409 ,PAYMENT_TERM_ID
3410 ,DISTRIBUTION_CHANGE_ID
3411 ,PREV_TERM_HISTORY_ID
3412 ,ADJUSTMENT_TYPE_CODE
3413 ,TOTAL_ADJ_AMOUNT
3414 ,PAYMENT_PURPOSE_CODE
3415 ,PAYMENT_TERM_TYPE_CODE
3416 ,FREQUENCY_CODE
3417 ,LEASE_ID
3418 ,LEASE_CHANGE_ID
3419 ,START_DATE
3420 ,END_DATE
3421 ,VENDOR_ID
3422 ,VENDOR_SITE_ID
3423 ,TARGET_DATE
3424 ,ACTUAL_AMOUNT
3425 ,ESTIMATED_AMOUNT
3426 ,SET_OF_BOOKS_ID
3427 ,CURRENCY_CODE
3428 ,RATE
3429 ,ATTRIBUTE_CATEGORY
3430 ,ATTRIBUTE1
3431 ,ATTRIBUTE2
3432 ,ATTRIBUTE3
3433 ,ATTRIBUTE4
3434 ,ATTRIBUTE5
3435 ,ATTRIBUTE6
3436 ,ATTRIBUTE7
3437 ,ATTRIBUTE8
3438 ,ATTRIBUTE9
3439 ,ATTRIBUTE10
3440 ,ATTRIBUTE11
3441 ,ATTRIBUTE12
3442 ,ATTRIBUTE13
3443 ,ATTRIBUTE14
3444 ,ATTRIBUTE15
3445 ,CUSTOMER_ID
3446 ,CUSTOMER_SITE_USE_ID
3447 ,NORMALIZE
3448 ,LOCATION_ID
3449 ,SCHEDULE_DAY
3450 ,CUST_SHIP_SITE_ID
3451 ,AP_AR_TERM_ID
3452 ,CUST_TRX_TYPE_ID
3453 ,PROJECT_ID
3454 ,TASK_ID
3455 ,ORGANIZATION_ID
3456 ,EXPENDITURE_TYPE
3457 ,EXPENDITURE_ITEM_DATE
3458 ,TAX_GROUP_ID
3459 ,TAX_CODE_ID
3460 ,TAX_CLASSIFICATION_CODE
3461 ,TAX_INCLUDED
3462 ,DISTRIBUTION_SET_ID
3463 ,INV_RULE_ID
3464 ,ACCOUNT_RULE_ID
3465 ,SALESREP_ID
3466 ,APPROVED_BY
3467 ,STATUS
3468 ,INDEX_PERIOD_ID
3469 ,INDEX_TERM_INDICATOR
3470 ,PO_HEADER_ID
3471 ,CUST_PO_NUMBER
3472 ,RECEIPT_METHOD_ID
3473 ,LEGAL_ENTITY_ID
3474 ,PROJECT_ATTRIBUTE_CATEGORY
3475 ,PROJECT_ATTRIBUTE1
3476 ,PROJECT_ATTRIBUTE2
3477 ,PROJECT_ATTRIBUTE3
3478 ,PROJECT_ATTRIBUTE4
3479 ,PROJECT_ATTRIBUTE5
3480 ,PROJECT_ATTRIBUTE6
3481 ,PROJECT_ATTRIBUTE7
3482 ,PROJECT_ATTRIBUTE8
3483 ,PROJECT_ATTRIBUTE9
3484 ,PROJECT_ATTRIBUTE10
3485 ,PROJECT_ATTRIBUTE11
3486 ,PROJECT_ATTRIBUTE12
3487 ,PROJECT_ATTRIBUTE13
3488 ,PROJECT_ATTRIBUTE14
3489 ,PROJECT_ATTRIBUTE15
3490 ,VAR_RENT_INV_ID
3491 ,VAR_RENT_TYPE
3492 ,CHANGED_FLAG
3493 ,NORM_START_DATE
3494 ,TERM_TEMPLATE_ID
3495 ,EVENT_TYPE_CODE
3496 ,LEASE_STATUS
3497 ,NORM_END_DATE
3498 ,RECOVERABLE_FLAG
3499 ,PERIOD_BILLREC_ID
3500 ,AMOUNT_TYPE
3501 ,REC_AGR_LINE_ID
3502 ,GROUPING_RULE_ID
3503 ,AREA_TYPE_CODE
3504 ,AREA
3505 ,TERM_COMMENTS)
3506 VALUES
3507 (PN_PAYMENT_TERMS_HISTORY_S.NEXTVAL
3508 ,SYSDATE
3509 ,FND_GLOBAL.USER_ID
3510 ,SYSDATE
3511 ,FND_GLOBAL.USER_ID
3512 ,FND_GLOBAL.LOGIN_ID
3513 ,l_term_id
3514 ,l_dist_change_id
3515 ,l_prev_term_hist_id
3516 ,l_adj_type_code
3517 ,NULL -- TOTAL_ADJ_AMOUNT
3518 ,tlinfo.PAYMENT_PURPOSE_CODE
3519 ,tlinfo.PAYMENT_TERM_TYPE_CODE
3520 ,tlinfo.FREQUENCY_CODE
3521 ,tlinfo.LEASE_ID
3522 ,term_rec.LEASE_CHANGE_ID
3523 ,l_str_dt
3524 ,l_end_dt
3525 ,tlinfo.VENDOR_ID
3526 ,tlinfo.VENDOR_SITE_ID
3527 ,tlinfo.TARGET_DATE
3528 ,tlinfo.ACTUAL_AMOUNT
3529 ,tlinfo.ESTIMATED_AMOUNT
3530 ,tlinfo.SET_OF_BOOKS_ID
3531 ,tlinfo.CURRENCY_CODE
3532 ,tlinfo.RATE
3533 ,tlinfo.ATTRIBUTE_CATEGORY
3534 ,tlinfo.ATTRIBUTE1
3535 ,tlinfo.ATTRIBUTE2
3536 ,tlinfo.ATTRIBUTE3
3537 ,tlinfo.ATTRIBUTE4
3538 ,tlinfo.ATTRIBUTE5
3539 ,tlinfo.ATTRIBUTE6
3540 ,tlinfo.ATTRIBUTE7
3541 ,tlinfo.ATTRIBUTE8
3542 ,tlinfo.ATTRIBUTE9
3543 ,tlinfo.ATTRIBUTE10
3544 ,tlinfo.ATTRIBUTE11
3545 ,tlinfo.ATTRIBUTE12
3546 ,tlinfo.ATTRIBUTE13
3547 ,tlinfo.ATTRIBUTE14
3548 ,tlinfo.ATTRIBUTE15
3549 ,tlinfo.CUSTOMER_ID
3550 ,tlinfo.CUSTOMER_SITE_USE_ID
3551 ,tlinfo.NORMALIZE
3552 ,tlinfo.LOCATION_ID
3553 ,tlinfo.SCHEDULE_DAY
3554 ,tlinfo.CUST_SHIP_SITE_ID
3555 ,tlinfo.AP_AR_TERM_ID
3556 ,tlinfo.CUST_TRX_TYPE_ID
3557 ,tlinfo.PROJECT_ID
3558 ,tlinfo.TASK_ID
3559 ,tlinfo.ORGANIZATION_ID
3560 ,tlinfo.EXPENDITURE_TYPE
3561 ,tlinfo.EXPENDITURE_ITEM_DATE
3562 ,tlinfo.TAX_GROUP_ID
3563 ,tlinfo.TAX_CODE_ID
3564 ,tlinfo.TAX_CLASSIFICATION_CODE
3565 ,tlinfo.TAX_INCLUDED
3566 ,tlinfo.DISTRIBUTION_SET_ID
3567 ,tlinfo.INV_RULE_ID
3568 ,tlinfo.ACCOUNT_RULE_ID
3569 ,tlinfo.SALESREP_ID
3570 ,tlinfo.APPROVED_BY
3571 ,tlinfo.STATUS
3572 ,tlinfo.INDEX_PERIOD_ID
3573 ,tlinfo.INDEX_TERM_INDICATOR
3574 ,tlinfo.PO_HEADER_ID
3575 ,tlinfo.CUST_PO_NUMBER
3576 ,tlinfo.RECEIPT_METHOD_ID
3577 ,tlinfo.LEGAL_ENTITY_ID
3578 ,tlinfo.PROJECT_ATTRIBUTE_CATEGORY
3579 ,tlinfo.PROJECT_ATTRIBUTE1
3580 ,tlinfo.PROJECT_ATTRIBUTE2
3581 ,tlinfo.PROJECT_ATTRIBUTE3
3582 ,tlinfo.PROJECT_ATTRIBUTE4
3583 ,tlinfo.PROJECT_ATTRIBUTE5
3584 ,tlinfo.PROJECT_ATTRIBUTE6
3585 ,tlinfo.PROJECT_ATTRIBUTE7
3586 ,tlinfo.PROJECT_ATTRIBUTE8
3587 ,tlinfo.PROJECT_ATTRIBUTE9
3588 ,tlinfo.PROJECT_ATTRIBUTE10
3589 ,tlinfo.PROJECT_ATTRIBUTE11
3590 ,tlinfo.PROJECT_ATTRIBUTE12
3591 ,tlinfo.PROJECT_ATTRIBUTE13
3592 ,tlinfo.PROJECT_ATTRIBUTE14
3593 ,tlinfo.PROJECT_ATTRIBUTE15
3594 ,tlinfo.VAR_RENT_INV_ID
3595 ,tlinfo.VAR_RENT_TYPE
3596 ,tlinfo.CHANGED_FLAG
3597 ,tlinfo.NORM_START_DATE
3598 ,tlinfo.TERM_TEMPLATE_ID
3599 ,tlinfo.EVENT_TYPE_CODE
3600 ,tlinfo.LEASE_STATUS
3601 ,tlinfo.NORM_END_DATE
3602 ,tlinfo.RECOVERABLE_FLAG
3603 ,tlinfo.PERIOD_BILLREC_ID
3604 ,tlinfo.AMOUNT_TYPE
3605 ,tlinfo.REC_AGR_LINE_ID
3606 ,tlinfo.GROUPING_RULE_ID
3607 ,tlinfo.AREA_TYPE_CODE
3608 ,tlinfo.AREA
3609 ,l_term_comments);
3610 END LOOP;
3611 END IF;
3612 END IF;
3613
3614 pnp_debug_pkg.debug('PNT_PAYMENT_TERMS_PKG.create_hist_corr_upd (-)');
3615
3616 END create_hist_corr_upd;
3617
3618 --------------------------------------------------------------------------------
3619 -- FUNCTION : return_agreement_number
3620 -- INVOKED FROM : pn_payment_terms_v form view (PNPTERMV.sql)
3621 -- PURPOSE : returns the agreement number to which the payment term is associated.
3622 -- HISTORY :
3623 --
3624 -- 12-MAR-07 Prabhakar o Created.
3625 --------------------------------------------------------------------------------
3626 FUNCTION return_agreement_number( p_payment_term_id IN NUMBER) RETURN VARCHAR2 IS
3627
3628 l_agreement_number VARCHAR2(30) := NULL;
3629
3630 CURSOR c1 IS
3631 SELECT opex_agr_id,index_period_id,var_rent_inv_id,rec_agr_line_id
3632 FROM pn_payment_terms_all
3633 where payment_term_id = p_payment_term_id;
3634
3635 BEGIN
3636
3637 FOR rec1 IN c1 LOOP
3638 IF rec1.index_period_id IS NOT NULL THEN
3639
3640 SELECT pil.index_lease_number
3641 INTO l_agreement_number
3642 FROM pn_index_lease_periods_all pilp,
3643 pn_index_leases_all pil
3644 WHERE pilp.index_lease_id = pil.index_lease_id
3645 AND pilp.index_period_id = rec1.index_period_id;
3646
3647 ELSIF rec1.opex_agr_id IS NOT NULL THEN
3648
3649 SELECT agr_num
3650 INTO l_agreement_number
3651 FROM pn_opex_agreements_all
3652 WHERE agreement_id = rec1.opex_agr_id;
3653
3654 ELSIF rec1.var_rent_inv_id IS NOT NULL THEN
3655
3656 SELECT var.rent_num
3657 INTO l_agreement_number
3658 FROM pn_var_rents_all var,
3659 pn_var_rent_inv_all inv
3660 WHERE var.var_rent_id = inv.var_rent_id
3661 AND inv.var_rent_inv_id = rec1.var_rent_inv_id;
3662
3663 ELSIF rec1.rec_agr_line_id IS NOT NULL THEN
3664
3665 SELECT rec.rec_agreement_num
3666 INTO l_agreement_number
3667 FROM pn_rec_agreements_all rec,
3668 pn_rec_agr_lines_all rlines
3669 WHERE rlines.rec_agreement_id = rec.rec_agreement_id
3670 AND rlines.rec_agr_line_id = rec1.rec_agr_line_id;
3671
3672 ELSE
3673
3674 SELECT NULL
3675 INTO l_agreement_number
3676 FROM DUAL;
3677
3678 END IF;
3679 END LOOP;
3680
3681 RETURN l_agreement_number;
3682
3683 END return_agreement_number;
3684
3685 --------------------------------------------------------------------------------
3686 -- FUNCTION : CHECK_IF_OPEX_TERM
3687 -- INVOKED FROM :
3688 -- PURPOSE : Checks whether or not the term is created from Opex UI.
3689 -- HISTORY :
3690 --
3691 -- 09-MAY-07 Prabhakar o Created.
3692 --------------------------------------------------------------------------------
3693 FUNCTION CHECK_IF_OPEX_TERM ( p_payment_term_id IN NUMBER,
3694 p_opex_agr_id IN NUMBER )
3695 RETURN BOOLEAN IS
3696
3697 l_dummy NUMBER := 0;
3698
3699 CURSOR dummy IS
3700 SELECT 1 flag
3701 FROM pn_opex_est_payments_all
3702 WHERE (payment_term_id = p_payment_term_id or catch_up_term_id = p_payment_term_id)
3703 AND agreement_id = p_opex_agr_id;
3704
3705 BEGIN
3706
3707 FOR rec IN dummy LOOP
3708 l_dummy := rec.flag;
3709 END LOOP;
3710
3711 IF l_dummy = 1 THEN return(TRUE);
3712 ELSE return(FALSE);
3713 END IF;
3714
3715 END CHECK_IF_OPEX_TERM;
3716
3717 --------------------------------------------------------------------------------
3718 -- FUNCTION : get_source_module_type
3719 -- INVOKED FROM : pn_payment_terms_v form view (PNPTERMV.sql)
3720 -- PURPOSE : returns the source of the term from which it is created.
3721 -- HISTORY :
3722 --
3723 -- 09-MAY-07 Prabhakar o Created.
3724 --------------------------------------------------------------------------------
3725 FUNCTION get_source_module_type( p_payment_term_id IN NUMBER) RETURN VARCHAR2 IS
3726
3727 l_source_module VARCHAR2(30) := NULL;
3728
3729 CURSOR c1 IS
3730 SELECT opex_agr_id,
3731 opex_recon_id,
3732 index_period_id,
3733 var_rent_inv_id,
3734 rec_agr_line_id,
3735 source_code
3736 FROM pn_payment_terms_all
3737 where payment_term_id = p_payment_term_id;
3738
3739 BEGIN
3740
3741 FOR rec1 IN c1 LOOP
3742
3743 IF (rec1.opex_agr_id IS NOT NULL AND
3744 CHECK_IF_OPEX_TERM (p_payment_term_id, rec1.opex_agr_id) ) OR
3745 rec1.opex_recon_id IS NOT NULL THEN
3746 l_source_module := 'OPEX';
3747 ELSIF rec1.index_period_id IS NOT NULL THEN
3748 l_source_module := 'RI';
3749 ELSIF rec1.var_rent_inv_id IS NOT NULL THEN
3750 l_source_module := 'VR';
3751 ELSIF rec1.rec_agr_line_id IS NOT NULL THEN
3752 l_source_module := 'REC';
3753 ELSE
3754 l_source_module := rec1.source_code;
3755 END IF;
3756
3757 END LOOP;
3758
3759 RETURN l_source_module;
3760
3761 END get_source_module_type;
3762
3763 PROCEDURE MODIFY_ROW ( x_payment_term_id IN NUMBER
3764 ,x_var_rent_inv_id IN NUMBER
3765 ,x_changed_flag IN VARCHAR2)
3766 IS
3767 BEGIN
3768
3769 pnp_debug_pkg.debug ('PNT_PAYMENT_TERMS_PKG.MODIFY_ROW (+)');
3770
3771 UPDATE pn_payment_terms_all
3772 SET changed_flag = x_changed_flag
3773 WHERE payment_term_id = NVL(x_payment_term_id ,payment_term_id )
3774 AND var_rent_inv_id = x_var_rent_inv_id;
3775
3776 pnp_debug_pkg.debug ('PNT_PAYMENT_TERMS_PKG.MODIFY_ROW (-)');
3777
3778 EXCEPTION
3779 WHEN others THEN
3780 NULL;
3781 END MODIFY_ROW;
3782
3783
3784
3785 END PNT_PAYMENT_TERMS_PKG;