[Home] [Help]
PACKAGE BODY: APPS.PNT_PAYMENT_TERMS_PKG
Source
1 PACKAGE BODY pnt_payment_terms_pkg AS
2 -- $Header: PNTPYTRB.pls 120.16.12010000.5 2010/02/02 09:51:00 jsundara 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 ELSIF x_customer_id IS NOT NULL THEN/* 9162932 */
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 -- 11-DEC-09 rthumma o Bug 9089546 : Modified call to UPDATE_PNT_ITEMS_AMT
1094 -- to pass x_frequency_code.
1095 --------------------------------------------------------------------------------
1096 PROCEDURE UPDATE_ROW (
1097 X_PAYMENT_TERM_ID IN NUMBER,
1098 X_PAYMENT_PURPOSE_CODE IN VARCHAR2,
1099 X_PAYMENT_TERM_TYPE_CODE IN VARCHAR2,
1100 X_FREQUENCY_CODE IN VARCHAR2,
1101 X_LEASE_ID IN NUMBER,
1102 X_LEASE_CHANGE_ID IN NUMBER,
1103 X_START_DATE IN DATE,
1104 X_END_DATE IN DATE,
1105 X_VENDOR_ID IN NUMBER,
1106 X_VENDOR_SITE_ID IN NUMBER,
1107 X_CUSTOMER_ID IN NUMBER,
1108 X_CUSTOMER_SITE_USE_ID IN NUMBER,
1109 X_TARGET_DATE IN DATE,
1110 X_ACTUAL_AMOUNT IN NUMBER,
1111 X_ESTIMATED_AMOUNT IN NUMBER,
1112 X_SET_OF_BOOKS_ID IN NUMBER,
1113 X_CURRENCY_CODE IN VARCHAR2,
1114 X_RATE IN NUMBER,
1115 X_NORMALIZE IN VARCHAR2,
1116 X_LOCATION_ID IN NUMBER,
1117 X_SCHEDULE_DAY IN NUMBER,
1118 X_CUST_SHIP_SITE_ID IN NUMBER,
1119 X_AP_AR_TERM_ID IN NUMBER,
1120 X_CUST_TRX_TYPE_ID IN NUMBER,
1121 X_PROJECT_ID IN NUMBER,
1122 X_TASK_ID IN NUMBER,
1123 X_ORGANIZATION_ID IN NUMBER,
1124 X_EXPENDITURE_TYPE IN VARCHAR2,
1125 X_EXPENDITURE_ITEM_DATE IN DATE,
1126 X_TAX_GROUP_ID IN NUMBER,
1127 X_TAX_CODE_ID IN NUMBER,
1128 X_TAX_CLASSIFICATION_CODE IN VARCHAR2,
1129 X_TAX_INCLUDED IN VARCHAR2,
1130 X_DISTRIBUTION_SET_ID IN NUMBER,
1131 X_INV_RULE_ID IN NUMBER,
1132 X_ACCOUNT_RULE_ID IN NUMBER,
1133 X_SALESREP_ID IN NUMBER,
1134 X_APPROVED_BY IN NUMBER,
1135 X_STATUS IN VARCHAR2,
1136 X_INDEX_PERIOD_ID IN NUMBER,
1137 X_INDEX_TERM_INDICATOR IN VARCHAR2,
1138 X_PO_HEADER_ID IN NUMBER,
1139 X_CUST_PO_NUMBER IN VARCHAR2,
1140 X_RECEIPT_METHOD_ID IN NUMBER,
1141 X_VAR_RENT_INV_ID IN NUMBER,
1142 X_VAR_RENT_TYPE IN VARCHAR2,
1143 X_CHANGED_FLAG IN VARCHAR2,
1144 X_ATTRIBUTE_CATEGORY IN VARCHAR2,
1145 X_ATTRIBUTE1 IN VARCHAR2,
1146 X_ATTRIBUTE2 IN VARCHAR2,
1147 X_ATTRIBUTE3 IN VARCHAR2,
1148 X_ATTRIBUTE4 IN VARCHAR2,
1149 X_ATTRIBUTE5 IN VARCHAR2,
1150 X_ATTRIBUTE6 IN VARCHAR2,
1151 X_ATTRIBUTE7 IN VARCHAR2,
1152 X_ATTRIBUTE8 IN VARCHAR2,
1153 X_ATTRIBUTE9 IN VARCHAR2,
1154 X_ATTRIBUTE10 IN VARCHAR2,
1155 X_ATTRIBUTE11 IN VARCHAR2,
1156 X_ATTRIBUTE12 IN VARCHAR2,
1157 X_ATTRIBUTE13 IN VARCHAR2,
1158 X_ATTRIBUTE14 IN VARCHAR2,
1159 X_ATTRIBUTE15 IN VARCHAR2,
1160 X_PROJECT_ATTRIBUTE_CATEGORY IN VARCHAR2,
1161 X_PROJECT_ATTRIBUTE1 IN VARCHAR2,
1162 X_PROJECT_ATTRIBUTE2 IN VARCHAR2,
1163 X_PROJECT_ATTRIBUTE3 IN VARCHAR2,
1164 X_PROJECT_ATTRIBUTE4 IN VARCHAR2,
1165 X_PROJECT_ATTRIBUTE5 IN VARCHAR2,
1166 X_PROJECT_ATTRIBUTE6 IN VARCHAR2,
1167 X_PROJECT_ATTRIBUTE7 IN VARCHAR2,
1168 X_PROJECT_ATTRIBUTE8 IN VARCHAR2,
1169 X_PROJECT_ATTRIBUTE9 IN VARCHAR2,
1170 X_PROJECT_ATTRIBUTE10 IN VARCHAR2,
1171 X_PROJECT_ATTRIBUTE11 IN VARCHAR2,
1172 X_PROJECT_ATTRIBUTE12 IN VARCHAR2,
1173 X_PROJECT_ATTRIBUTE13 IN VARCHAR2,
1174 X_PROJECT_ATTRIBUTE14 IN VARCHAR2,
1175 X_PROJECT_ATTRIBUTE15 IN VARCHAR2,
1176 X_LAST_UPDATE_DATE IN DATE,
1177 X_LAST_UPDATED_BY IN NUMBER,
1178 X_LAST_UPDATE_LOGIN IN NUMBER,
1179 x_recoverable_flag IN VARCHAR2,
1180 x_area_type_code IN VARCHAR2,
1181 x_area IN NUMBER,
1182 x_grouping_rule_id IN NUMBER,
1183 x_term_altered_flag IN VARCHAR2,
1184 x_source_code IN VARCHAR2,
1185 x_term_comments IN VARCHAR2,
1186 X_TERM_TEMPLATE_ID IN NUMBER,
1187 x_include_in_var_rent IN VARCHAR2 DEFAULT NULL,
1188 X_RECUR_BB_CALC_DATE IN DATE,
1189 x_opex_agr_id IN NUMBER,
1190 x_opex_recon_id IN NUMBER,
1191 x_opex_type IN VARCHAR2
1192 )
1193 IS
1194 CURSOR has_distributions IS
1195 SELECT 'Y'
1196 FROM dual
1197 WHERE EXISTS (SELECT 'Y' FROM pn_distributions_all
1198 WHERE payment_term_id = x_payment_term_id);
1199
1200 l_legal_entity_id pn_payment_terms.legal_entity_id%TYPE;
1201 l_org_id pn_payment_terms.org_id%TYPE;
1202 l_find_le BOOLEAN;
1203 l_return_status VARCHAR2 (2) := NULL;
1204 l_old_est NUMBER;
1205 l_status PN_LEASES.status%TYPE;
1206 l_context varchar2(240);
1207 l_rate pn_payment_terms.rate%TYPE;
1208 l_precision NUMBER;
1209 l_ext_precision NUMBER;
1210 l_min_acct_unit NUMBER;
1211 l_update_nbp_flag VARCHAR2(1);
1212
1213 BEGIN
1214
1215 pnp_debug_pkg.debug ('PNT_PAYMENT_TERMS_PKG.UPDATE_ROW (+)');
1216
1217 -- Check IF both Estimated AND actual amount are NULL
1218 l_context := 'checking est AND actual amt';
1219 l_return_status := NULL;
1220 PNT_PAYMENT_TERMS_PKG.CHECK_PAYMENT_AMOUNTS (
1221 l_RETURN_STATUS
1222 ,X_ACTUAL_AMOUNT
1223 ,X_ESTIMATED_AMOUNT
1224 );
1225
1226 IF (l_return_status IS NOT NULL) THEN
1227 app_exception.Raise_Exception;
1228 END IF;
1229
1230
1231 IF ( tlinfo.start_date <> X_START_DATE
1232 OR tlinfo.end_date <> X_END_DATE
1233 OR tlinfo.actual_amount <> X_ACTUAL_AMOUNT
1234 OR NVL(tlinfo.include_in_var_rent,'N') <> NVL(X_INCLUDE_IN_VAR_RENT,'N')) THEN
1235
1236 l_update_nbp_flag := 'Y';
1237 ELSE
1238 l_update_nbp_flag := NULL;
1239 END IF;
1240
1241 -----------------------------------------------------------------------------
1242 -- Bug Fix : 1668495.
1243 -- Removed the call to the
1244 -- following PROCEDURE
1245 -- PNT_PAYMENT_TERMS_PKG.CHECK_APPROVED_SCHEDULE_EXISTS
1246 -- form the UPDATE TABLE HANDLER
1247 -- to allow the user to change the
1248 -- Vendor Information , Customer Information
1249 -- AND also the GL Account even when the
1250 -- approved payment schedules exists.
1251 -----------------------------------------------------------------------------
1252
1253 /*
1254 Added the following IF statement logic in response to bug ID #1845607
1255 Please refer to PROCEDURE UPDATE_PNT_ITEMS_AMT
1256 */
1257
1258
1259 IF nvl(X_NORMALIZE,'N') = 'N' THEN
1260
1261 l_context := 'SELECTing lease status';
1262
1263 SELECT status
1264 INTO l_status
1265 FROM pn_leases_all
1266 WHERE lease_id = X_LEASE_ID;
1267
1268 l_context := 'SELECTing estimated amt';
1269
1270 SELECT estimated_amount
1271 INTO l_old_est
1272 FROM pn_payment_terms_all
1273 WHERE payment_term_id = X_PAYMENT_TERM_ID;
1274
1275 IF (l_status = 'F' AND l_old_est <> X_ESTIMATED_AMOUNT) THEN
1276
1277 l_context := 'updating payment items amount';
1278 UPDATE_PNT_ITEMS_AMT(X_PAYMENT_TERM_ID,
1279 l_old_est,
1280 X_ESTIMATED_AMOUNT,
1281 X_LEASE_ID,
1282 X_START_DATE,
1283 X_END_DATE,
1284 X_FREQUENCY_CODE);
1285 END IF;
1286
1287 END IF;
1288
1289 l_context := 'Selecting old rate and org_id';
1290
1291 SELECT rate, org_id,legal_entity_id
1292 INTO l_rate, l_org_id,l_legal_entity_id
1293 FROM pn_payment_terms_all
1294 WHERE payment_term_id = X_PAYMENT_TERM_ID;
1295
1296 l_context := 'updating payment items rate IF rate IS changed';
1297
1298 -- use the fact that rate must be greater than zero
1299 IF (nvl(l_rate,0) <> nvl(X_RATE,-1)) THEN
1300 UPDATE_PNT_ITEMS_RATE(X_PAYMENT_TERM_ID, X_RATE);
1301 END IF;
1302
1303 l_context := 'updating vendor AND customer info';
1304
1305 PNT_PAYMENT_TERMS_PKG.UPDATE_VENDOR_AND_CUST (
1306 X_PAYMENT_TERM_ID
1307 ,X_VENDOR_ID
1308 ,X_VENDOR_SITE_ID
1309 ,X_LAST_UPDATE_DATE
1310 ,X_LAST_UPDATED_BY
1311 ,X_LAST_UPDATE_LOGIN
1312 ,X_CUSTOMER_ID
1313 ,X_CUSTOMER_SITE_USE_ID
1314 ,X_CUST_SHIP_SITE_ID
1315 );
1316
1317 l_context := 'checking if legal entity changed';
1318
1319 IF pn_r12_util_pkg.is_r12 THEN
1320 IF x_vendor_site_id IS NOT NULL THEN -- PAYABLES
1321 l_find_le := TRUE;
1322 IF l_legal_entity_id is NOT NULL THEN
1323 FOR chk_distributions IN has_distributions LOOP
1324 l_find_le := FALSE;
1325 END LOOP;
1326 END IF; /* 9162932 */
1327
1328 IF l_find_le THEN
1329 l_legal_entity_id :=
1330 pn_r12_util_pkg.get_le_for_ap(
1331 p_code_combination_id => null
1332 ,p_location_id => x_vendor_site_id
1333 ,p_org_id => l_org_id
1334 );
1335 END IF;
1336 ELSIF x_customer_id is NOT NULL THEN -- RECEIVABLES
1337 l_legal_entity_id :=
1338 pn_r12_util_pkg.get_le_for_ar(
1339 p_customer_id => x_customer_id
1340 ,p_transaction_type_id => x_cust_trx_type_id
1341 ,p_org_id => l_org_id
1342 );
1343 END IF;
1344 END IF;
1345
1346 /* get currency info for rounding */
1347 fnd_currency.get_info( currency_code => x_currency_code
1348 ,precision => l_precision
1349 ,ext_precision => l_ext_precision
1350 ,min_acct_unit => l_min_acct_unit);
1351
1352 l_context := 'updating payment terms';
1353
1354 UPDATE PN_PAYMENT_TERMS_ALL
1355 SET PAYMENT_TERM_ID = X_PAYMENT_TERM_ID,
1356 PAYMENT_PURPOSE_CODE = X_PAYMENT_PURPOSE_CODE,
1357 PAYMENT_TERM_TYPE_CODE = X_PAYMENT_TERM_TYPE_CODE,
1358 FREQUENCY_CODE = X_FREQUENCY_CODE,
1359 LEASE_ID = X_LEASE_ID,
1360 LEASE_CHANGE_ID = X_LEASE_CHANGE_ID,
1361 START_DATE = X_START_DATE,
1362 END_DATE = X_END_DATE,
1363 VENDOR_ID = X_VENDOR_ID,
1364 VENDOR_SITE_ID = X_VENDOR_SITE_ID,
1365 CUSTOMER_ID = X_CUSTOMER_ID,
1366 CUSTOMER_SITE_USE_ID = X_CUSTOMER_SITE_USE_ID,
1367 TARGET_DATE = X_TARGET_DATE,
1368 ACTUAL_AMOUNT = ROUND(X_ACTUAL_AMOUNT, l_precision),
1369 ESTIMATED_AMOUNT = ROUND(X_ESTIMATED_AMOUNT, l_precision),
1370 SET_OF_BOOKS_ID = X_SET_OF_BOOKS_ID,
1371 CURRENCY_CODE = X_CURRENCY_CODE,
1372 RATE = X_RATE,
1373 NORMALIZE = X_NORMALIZE,
1374 LOCATION_ID = X_LOCATION_ID,
1375 SCHEDULE_DAY = X_SCHEDULE_DAY,
1376 CUST_SHIP_SITE_ID = X_CUST_SHIP_SITE_ID,
1377 AP_AR_TERM_ID = X_AP_AR_TERM_ID,
1378 CUST_TRX_TYPE_ID = X_CUST_TRX_TYPE_ID,
1379 PROJECT_ID = X_PROJECT_ID,
1380 TASK_ID = X_TASK_ID,
1381 ORGANIZATION_ID = X_ORGANIZATION_ID,
1382 EXPENDITURE_TYPE = X_EXPENDITURE_TYPE,
1383 EXPENDITURE_ITEM_DATE = X_EXPENDITURE_ITEM_DATE,
1384 TAX_GROUP_ID = X_TAX_GROUP_ID,
1385 TAX_CODE_ID = X_TAX_CODE_ID,
1386 TAX_CLASSIFICATION_CODE = X_TAX_CLASSIFICATION_CODE,
1387 TAX_INCLUDED = X_TAX_INCLUDED,
1388 DISTRIBUTION_SET_ID = X_DISTRIBUTION_SET_ID,
1389 INV_RULE_ID = X_INV_RULE_ID,
1390 ACCOUNT_RULE_ID = X_ACCOUNT_RULE_ID,
1391 SALESREP_ID = X_SALESREP_ID,
1392 APPROVED_BY = X_APPROVED_BY,
1393 STATUS = X_STATUS,
1394 INDEX_PERIOD_ID = X_INDEX_PERIOD_ID,
1395 INDEX_TERM_INDICATOR = X_INDEX_TERM_INDICATOR,
1396 PO_HEADER_ID = X_PO_HEADER_ID,
1397 CUST_PO_NUMBER = X_CUST_PO_NUMBER,
1398 RECEIPT_METHOD_ID = X_RECEIPT_METHOD_ID,
1399 VAR_RENT_INV_ID = X_VAR_RENT_INV_ID,
1400 VAR_RENT_TYPE = X_VAR_RENT_TYPE,
1401 CHANGED_FLAG = X_CHANGED_FLAG,
1402 LEGAL_ENTITY_ID = L_LEGAL_ENTITY_ID,
1403 ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
1404 ATTRIBUTE1 = X_ATTRIBUTE1,
1405 ATTRIBUTE2 = X_ATTRIBUTE2,
1406 ATTRIBUTE3 = X_ATTRIBUTE3,
1407 ATTRIBUTE4 = X_ATTRIBUTE4,
1408 ATTRIBUTE5 = X_ATTRIBUTE5,
1409 ATTRIBUTE6 = X_ATTRIBUTE6,
1410 ATTRIBUTE7 = X_ATTRIBUTE7,
1411 ATTRIBUTE8 = X_ATTRIBUTE8,
1412 ATTRIBUTE9 = X_ATTRIBUTE9,
1413 ATTRIBUTE10 = X_ATTRIBUTE10,
1414 ATTRIBUTE11 = X_ATTRIBUTE11,
1415 ATTRIBUTE12 = X_ATTRIBUTE12,
1416 ATTRIBUTE13 = X_ATTRIBUTE13,
1417 ATTRIBUTE14 = X_ATTRIBUTE14,
1418 ATTRIBUTE15 = X_ATTRIBUTE15,
1419 PROJECT_ATTRIBUTE_CATEGORY = X_PROJECT_ATTRIBUTE_CATEGORY,
1420 PROJECT_ATTRIBUTE1 = X_PROJECT_ATTRIBUTE1,
1421 PROJECT_ATTRIBUTE2 = X_PROJECT_ATTRIBUTE2,
1422 PROJECT_ATTRIBUTE3 = X_PROJECT_ATTRIBUTE3,
1423 PROJECT_ATTRIBUTE4 = X_PROJECT_ATTRIBUTE4,
1424 PROJECT_ATTRIBUTE5 = X_PROJECT_ATTRIBUTE5,
1425 PROJECT_ATTRIBUTE6 = X_PROJECT_ATTRIBUTE6,
1426 PROJECT_ATTRIBUTE7 = X_PROJECT_ATTRIBUTE7,
1427 PROJECT_ATTRIBUTE8 = X_PROJECT_ATTRIBUTE8,
1428 PROJECT_ATTRIBUTE9 = X_PROJECT_ATTRIBUTE9,
1429 PROJECT_ATTRIBUTE10 = X_PROJECT_ATTRIBUTE10,
1430 PROJECT_ATTRIBUTE11 = X_PROJECT_ATTRIBUTE11,
1431 PROJECT_ATTRIBUTE12 = X_PROJECT_ATTRIBUTE12,
1432 PROJECT_ATTRIBUTE13 = X_PROJECT_ATTRIBUTE13,
1433 PROJECT_ATTRIBUTE14 = X_PROJECT_ATTRIBUTE14,
1434 PROJECT_ATTRIBUTE15 = X_PROJECT_ATTRIBUTE15,
1435 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
1436 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
1437 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
1438 RECOVERABLE_FLAG = X_RECOVERABLE_FLAG,
1439 AREA_TYPE_CODE = X_AREA_TYPE_CODE,
1440 AREA = X_AREA,
1441 GROUPING_RULE_ID = X_GROUPING_RULE_ID,
1442 TERM_ALTERED_FLAG = X_TERM_ALTERED_FLAG,
1443 SOURCE_CODE = X_SOURCE_CODE,
1444 TERM_COMMENTS = X_TERM_COMMENTS,
1445 TERM_TEMPLATE_ID = X_TERM_TEMPLATE_ID,
1446 INCLUDE_IN_VAR_RENT = X_INCLUDE_IN_VAR_RENT,
1447 UPDATE_NBP_FLAG = l_update_nbp_flag,
1448 RECUR_BB_CALC_DATE = X_RECUR_BB_CALC_DATE,
1449 OPEX_AGR_ID = X_OPEX_AGR_ID,
1450 OPEX_RECON_ID = X_OPEX_RECON_ID,
1451 OPEX_TYPE = X_OPEX_TYPE
1452 WHERE PAYMENT_TERM_ID = X_PAYMENT_TERM_ID;
1453
1454 IF (SQL%NOTFOUND) THEN
1455 RAISE NO_DATA_FOUND;
1456 END IF;
1457
1458
1459 pnp_debug_pkg.debug ('PNT_PAYMENT_TERMS_PKG.UPDATE_ROW (-)');
1460
1461 EXCEPTION
1462 WHEN OTHERS THEN
1463 RAISE_APPLICATION_ERROR(-20001,'Error while ' || l_context || to_char(sqlcode));
1464 app_exception.Raise_Exception;
1465
1466 END UPDATE_ROW;
1467
1468 -------------------------------------------------------------------------------
1469 -- PROCDURE : DELETE_ROW
1470 -- INVOKED FROM : DELETE_ROW procedure
1471 -- PURPOSE : deletes the row
1472 -- HISTORY :
1473 -- 05-JUL-05 sdmahesh o Bug 4284035 - Replaced pn_payment_terms with _ALL
1474 -- table.
1475 -------------------------------------------------------------------------------
1476 PROCEDURE DELETE_ROW (
1477 X_PAYMENT_TERM_ID IN NUMBER
1478 )
1479 IS
1480 BEGIN
1481
1482 pnp_debug_pkg.debug ('PNT_PAYMENT_TERMS_PKG.DELETE_ROW (+)');
1483
1484 DELETE FROM pn_payment_terms_all
1485 WHERE payment_term_id = x_payment_term_id;
1486
1487 IF (SQL%NOTFOUND) THEN
1488 RAISE NO_DATA_FOUND;
1489 END IF;
1490
1491 pnp_debug_pkg.debug ('PNT_PAYMENT_TERMS_PKG.DELETE_ROW (-)');
1492 END DELETE_ROW;
1493
1494 -------------------------------------------------------------------------------
1495 -- PROCDURE : UPDATE_VENDOR_AND_CUST
1496 -- INVOKED FROM :
1497 -- PURPOSE : UPDATE each item with the new vendor AND vendor site
1498 -- HISTORY :
1499 -- 05-JUL-05 sdmahesh o Bug 4284035 - Replaced pn_payment_items with _ALL
1500 -- table.
1501 -------------------------------------------------------------------------------
1502 PROCEDURE UPDATE_VENDOR_AND_CUST
1503 (
1504 X_PAYMENT_TERM_ID IN NUMBER
1505 ,X_VENDOR_ID IN NUMBER
1506 ,X_VENDOR_SITE_ID IN NUMBER
1507 ,X_LAST_UPDATE_DATE IN DATE
1508 ,X_LAST_UPDATED_BY IN NUMBER
1509 ,X_LAST_UPDATE_LOGIN IN NUMBER
1510 ,X_CUSTOMER_ID IN NUMBER
1511 ,X_CUSTOMER_SITE_USE_ID IN NUMBER
1512 ,X_CUST_SHIP_SITE_ID IN NUMBER
1513 )
1514 IS
1515 CURSOR c1 IS
1516 SELECT ppi.payment_item_id
1517 FROM pn_payment_items_all ppi
1518 WHERE ppi.payment_term_id = X_PAYMENT_TERM_ID
1519 AND ppi.transferred_to_ap_flag IS NULL
1520 AND ppi.transferred_to_ar_flag IS NULL
1521 FOR UPDATE OF ppi.payment_item_id NOWAIT;
1522
1523 BEGIN
1524 pnp_debug_pkg.debug('PNT_PAYMENT_TERMS_PKG.UPDATE_VENDOR_AND_CUST (+)');
1525 -- UPDATE each item with the new vendor AND vendor site
1526 FOR eachItem IN c1 LOOP
1527
1528 UPDATE pn_payment_items_all
1529 SET VENDOR_ID = X_VENDOR_ID
1530 ,VENDOR_SITE_ID = X_VENDOR_SITE_ID
1531 ,LAST_UPDATE_DATE = X_LAST_UPDATE_DATE
1532 ,LAST_UPDATED_BY = X_LAST_UPDATED_BY
1533 ,LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
1534 ,CUSTOMER_ID = X_CUSTOMER_ID
1535 ,CUSTOMER_SITE_USE_ID = X_CUSTOMER_SITE_USE_ID
1536 ,CUST_SHIP_SITE_ID = X_CUST_SHIP_SITE_ID
1537 WHERE payment_item_id = eachItem.payment_item_id;
1538
1539 END LOOP;
1540
1541 pnp_debug_pkg.debug('PNT_PAYMENT_TERMS_PKG.UPDATE_VENDOR_AND_CUST (-)');
1542 END UPDATE_VENDOR_AND_CUST;
1543
1544 -------------------------------------------------------------------------------
1545 -- PROCEDURE : CHECK_APPROVED_SCHEDULE_EXISTS
1546 -- DESCRIPTION: This PROCEDURE will check IF an approved payment
1547 -- schedule exists BETWEEN the given start AND end
1548 -- date OF the payment term we are trying to create.
1549 -- HISTORY :
1550 -- 23-SEP-03 ATUPPAD o Changed the procedure to handle the One Time terms as in
1551 -- this case start and end dates are the same.
1552 -- Also, the query now executes through Cursor.
1553 -- Also, optmized the query by not joining with tables
1554 -- pn_payment_items and pn_leases. (bug#3140238)
1555 -- 05-JUL-05 sdmahesh o Bug 4284035 - Replaced pn_leases with _ALL table.
1556 -------------------------------------------------------------------------------
1557 PROCEDURE Check_Approved_Schedule_Exists (
1558 x_return_status IN OUT NOCOPY VARCHAR2
1559 ,x_lease_id IN NUMBER
1560 ,x_start_date IN DATE
1561 ,x_end_date IN DATE
1562 ,x_schedule_day IN NUMBER
1563 )
1564 IS
1565 l_leaseNumber VARCHAR2(30) := NULL;
1566 l_startDate DATE := NULL;
1567 l_endDate DATE := NULL;
1568
1569 -- Get the details of Approved Schedules
1570 CURSOR c_chk_app_sch IS
1571 SELECT lease_num
1572 FROM pn_leases_all
1573 WHERE lease_id = x_lease_id
1574 AND EXISTS
1575 (
1576 SELECT pps.lease_id
1577 FROM pn_payment_schedules_all pps
1578 WHERE pps.lease_id = x_lease_id
1579 AND pps.payment_status_lookup_code = 'APPROVED'
1580 AND TO_CHAR(pps.schedule_date, 'DD') = x_schedule_day
1581 AND pps.schedule_date BETWEEN l_startDate AND l_endDate
1582 );
1583
1584 BEGIN
1585 pnp_debug_pkg.debug('PNT_PAYMENT_TERMS_PKG.CHECK_APPROVED_SCHEDULE_EXISTS (+)');
1586
1587 SELECT TRUNC(x_start_date, 'MM')
1588 ,LAST_DAY(x_end_date)
1589 INTO l_startDate
1590 ,l_endDate
1591 FROM DUAL;
1592
1593 OPEN c_chk_app_sch;
1594 FETCH c_chk_app_sch INTO l_leaseNumber;
1595 IF (c_chk_app_sch%NOTFOUND) THEN
1596 CLOSE c_chk_app_sch;
1597 RETURN;
1598 END IF;
1599
1600 CLOSE c_chk_app_sch;
1601
1602 x_return_status := 'E';
1603 fnd_message.set_name('PN','PN_LEASES_APPROVED_PMT_EXISTS');
1604 fnd_message.set_token('LEASE_NUMBER', l_leaseNumber);
1605 fnd_message.set_token('START_DATE', l_startDate);
1606 fnd_message.set_token('END_DATE', l_endDate);
1607
1608 pnp_debug_pkg.debug('PNT_PAYMENT_TERMS_PKG.CHECK_APPROVED_SCHEDULE_EXISTS (-) RetStat: '||x_return_status);
1609 END Check_Approved_Schedule_Exists;
1610
1611 --------------------------------------------------------------------------------
1612 -- PROCEDURE : UPDATE_PNT_ITEMS_AMT
1613 -- PURPOSE : updates scheduled items FOR non-normalized payment terms
1614 -- OTHER REF : bug ID # 1845607
1615 -- SCOPE : local / private
1616 -- NOTE : Logic FOR PROCEDURE UPDATE_PNT_ITEMS_AMT:
1617 -- FOR each payment item with status 'draft'
1618 -- - do UPDATE OF estimated amount
1619 -- - IF the actual amount equals the old estimated amount
1620 -- (which means that it hasn't been changed)
1621 -- THEN set actual amount equal to new estimated amount
1622 -- taking INTO account proration rules
1623 --
1624 -- HISTORY:
1625 -- 28-DEC-01 FTANUDJA o Created
1626 -- 11-OCT-04 STripathi o Fixed for BUG# 3942284. Changed paramater p_freq
1627 -- to p_freq_code. For OT terms, update amount with
1628 -- the p_new_est_amt.
1629 -- 05-JUL-05 sdmahesh o Bug 4284035 - Replaced pn_payment_items,
1630 -- pn_payment_schedules, pn_leases with _ALL table.
1631 -- 24-NOV-05 Kiran o rounding for amounts done based on currency precision
1632 --------------------------------------------------------------------------------
1633
1634 PROCEDURE UPDATE_PNT_ITEMS_AMT (
1635 p_pnt_term_id IN pn_payment_items.payment_term_id%TYPE,
1636 p_old_est_amt IN pn_payment_items.estimated_amount%TYPE,
1637 p_new_est_amt IN pn_payment_items.estimated_amount%TYPE,
1638 p_lease_id IN pn_payment_terms.lease_id%TYPE,
1639 p_trm_str_dt IN DATE,
1640 p_trm_end_dt IN DATE,
1641 p_freq_code IN pn_payment_terms.frequency_code%TYPE
1642 )
1643 IS
1644
1645 l_prorate_rule NUMBER;
1646 l_act_amt NUMBER;
1647 l_est_amt NUMBER;
1648 l_sch_str_dt DATE;
1649 l_sch_end_dt DATE;
1650 l_last_sch_dt DATE;
1651 l_context VARCHAR2(240);
1652 l_first_last VARCHAR2(1);
1653 l_freq NUMBER;
1654
1655 CURSOR pnt_cursor IS
1656 SELECT ppi.payment_item_id
1657 , pps.payment_schedule_id
1658 , ppi.actual_amount
1659 , ppi.estimated_amount
1660 , pps.schedule_date
1661 , ppi.currency_code
1662 FROM pn_payment_items_all ppi,
1663 pn_payment_schedules_all pps
1664 WHERE ppi.payment_term_id = p_pnt_term_id
1665 AND ppi.payment_item_type_lookup_code = 'CASH'
1666 AND pps.payment_schedule_id = ppi.payment_schedule_id
1667 AND pps.payment_status_lookup_code = 'DRAFT';
1668
1669 l_precision NUMBER;
1670 l_ext_precision NUMBER;
1671 l_min_acct_unit NUMBER;
1672 l_first BOOLEAN;
1673 BEGIN
1674
1675 pnp_debug_pkg.debug('PNT_PAYMENT_TERMS_PKG.UPDATE_PNT_ITEMS_AMT (+) TermId: '
1676 ||p_pnt_term_id||', OldEstAmt: '||p_old_est_amt
1677 ||', NewEstAmt: '||p_new_est_amt||', Freq: '||p_freq_code);
1678 l_context:= 'Updating for OneTime term';
1679
1680 IF p_freq_code = 'OT' THEN
1681 l_est_amt := p_new_est_amt;
1682 l_act_amt := p_new_est_amt;
1683
1684 l_first := TRUE;
1685
1686 FOR pnt_rec IN pnt_cursor LOOP
1687 IF l_first THEN
1688 fnd_currency.get_info ( currency_code => pnt_rec.currency_code
1689 ,precision => l_precision
1690 ,ext_precision => l_ext_precision
1691 ,min_acct_unit => l_min_acct_unit);
1692 l_first := FALSE;
1693 END IF;
1694
1695 UPDATE pn_payment_items_all
1696 SET estimated_amount = ROUND(l_est_amt, l_precision)
1697 ,actual_amount = ROUND(l_act_amt, l_precision)
1698 ,export_currency_amount = ROUND(l_act_amt, l_precision)
1699 ,last_update_date = SYSDATE
1700 ,last_updated_by = NVL(fnd_profile.value('USER_ID'),0)
1701 ,last_update_login= NVL(fnd_profile.value('LOGIN_ID'),0)
1702 WHERE payment_item_id = pnt_rec.payment_item_id;
1703
1704 EXIT;
1705 END LOOP;
1706
1707 ELSE
1708
1709 l_context:= 'getting proration rule';
1710 l_freq := pn_schedules_items.get_frequency(p_freq_code);
1711
1712 SELECT payment_term_proration_rule
1713 INTO l_prorate_rule
1714 FROM pn_leases_all
1715 WHERE lease_id = p_lease_id;
1716
1717 l_context:= 'entering UPDATE loop';
1718
1719 l_first := TRUE;
1720
1721 FOR pnt_rec IN pnt_cursor LOOP
1722
1723 /* get currency info the first time */
1724 IF l_first THEN
1725 fnd_currency.get_info ( currency_code => pnt_rec.currency_code
1726 ,precision => l_precision
1727 ,ext_precision => l_ext_precision
1728 ,min_acct_unit => l_min_acct_unit);
1729 l_first := FALSE;
1730 END IF;
1731
1732 /** pro-rating part*/
1733
1734 l_sch_str_dt := TO_DATE('01/'||TO_CHAR(pnt_rec.schedule_date,'MM/YYYY'),'DD/MM/YYYY');
1735 -- the above copied FROM FIRST_DAY in PNSCHITB.pls as OF 01/02/2002
1736
1737 l_sch_end_dt := least(LAST_DAY(p_trm_end_dt),
1738 LAST_DAY(ADD_MONTHS(l_sch_str_dt, (l_freq-1))));
1739
1740 IF l_sch_end_dt = LAST_DAY(p_trm_end_dt) THEN
1741
1742 l_last_sch_dt := PN_SCHEDULES_ITEMS.FIRST_DAY(ADD_MONTHS(p_trm_end_dt,((l_freq -1) * -1)));
1743
1744 END IF;
1745
1746 /* We would need to do the pro-ration only IF the schedule IS the first or the last
1747 schedule */
1748
1749 l_context:= 'taking into account various dates';
1750
1751 IF to_char(pnt_rec.schedule_date,'MM/YYYY') = to_char(p_trm_str_dt,'MM/YYYY') OR
1752 to_char(pnt_rec.schedule_date,'MM/YYYY') = to_char(l_last_sch_dt,'MM/YYYY') THEN
1753
1754 PN_SCHEDULES_ITEMS.GET_AMOUNT(
1755 l_sch_str_dt,
1756 l_sch_end_dt,
1757 p_trm_str_dt,
1758 p_trm_end_dt,
1759 pnt_rec.actual_amount,
1760 p_new_est_amt,
1761 l_freq,
1762 TO_CHAR(l_prorate_rule),
1763 l_act_amt,
1764 l_est_amt
1765 );
1766 pnp_debug_pkg.debug('Get Amt; SchStrDt: '||l_sch_str_dt||', SchEndDt: '||l_sch_end_dt
1767 ||', ActAmt: '||l_act_amt||', EstAmt: '||l_est_amt);
1768
1769 l_first_last := 'Y';
1770
1771 ELSE
1772
1773 l_est_amt := p_new_est_amt;
1774 l_first_last := 'N';
1775
1776 END IF;
1777
1778 l_context := 'figuring out NOCOPY amounts';
1779
1780 IF (l_first_last = 'Y' AND nvl(pnt_rec.actual_amount,0) <> nvl(pnt_rec.estimated_amount,0)) OR
1781 (l_first_last = 'N' AND nvl(pnt_rec.actual_amount,0) <> nvl(p_old_est_amt,0)) THEN
1782
1783 l_act_amt := pnt_rec.actual_amount;
1784
1785 ELSE
1786
1787 l_act_amt := l_est_amt;
1788
1789 END IF;
1790
1791 /** updates estimated amount AND actual amount accordingly in the items table */
1792 l_context := 'updates items table';
1793
1794 UPDATE pn_payment_items_all
1795 SET estimated_amount = ROUND(l_est_amt, l_precision)
1796 ,actual_amount = ROUND(l_act_amt, l_precision)
1797 ,export_currency_amount = ROUND(l_act_amt, l_precision)
1798 ,last_update_date = SYSDATE
1799 ,last_updated_by = NVL(fnd_profile.value('USER_ID'),0)
1800 ,last_update_login= NVL(fnd_profile.value('LOGIN_ID'),0)
1801 WHERE payment_item_id = pnt_rec.payment_item_id;
1802
1803 END LOOP;
1804
1805 END IF; -- p_freq_code = 'OT'
1806
1807 pnp_debug_pkg.debug('PNT_PAYMENT_TERMS_PKG.UPDATE_PNT_ITEMS_AMT (-)');
1808 EXCEPTION
1809 WHEN OTHERS THEN
1810 RAISE_APPLICATION_ERROR(-20001,'Error while ' || l_context || TO_CHAR(sqlcode));
1811 app_exception.Raise_Exception;
1812
1813 pnp_debug_pkg.debug('PNT_PAYMENT_TERMS_PKG.UPDATE_PNT_ITEMS_AMT (-)');
1814 END UPDATE_PNT_ITEMS_AMT;
1815
1816 -------------------------------------------------------------------------------
1817 -- PROCEDURE : UPDATE_PNT_ITEMS_RATE
1818 -- PURPOSE : cascade changed rate FROM term level to item level
1819 -- SCOPE : local / private
1820 -- DESCRIPTION : The logic IS as follows:
1821 -- o Given a particular payment term
1822 -- o UPDATE items in schedules still in DRAFT
1823 -- o Implicit: item IS not exported
1824 -- o Implicit: term has been finalized
1825 -- HISTORY:
1826 -- 15-MAY-02 ftanudja o Created
1827 -- 05-JUL-05 sdmahesh o Bug 4284035 - Replaced pn_payment_items with _ALL
1828 -- table.
1829 -------------------------------------------------------------------------------
1830
1831 PROCEDURE UPDATE_PNT_ITEMS_RATE(p_pnt_term_id pn_payment_items.payment_item_id%TYPE,
1832 p_new_rate pn_payment_items.rate%TYPE)
1833 IS
1834 l_context VARCHAR2(240);
1835
1836 CURSOR pnt_cursor IS
1837 SELECT payment_item_id
1838 FROM pn_payment_items_all ppi
1839 WHERE ppi.payment_term_id = p_pnt_term_id
1840 AND ppi.export_to_ap_flag IS NULL
1841 AND ppi.export_to_ar_flag IS NULL;
1842
1843 BEGIN
1844
1845 pnp_debug_pkg.debug('PNT_PAYMENT_TERMS_PKG.UPDATE_PNT_ITEMS_RATE (+)');
1846 l_context := 'opening payment rate cursor';
1847
1848 FOR pnt_rec IN pnt_cursor LOOP
1849
1850 l_context := 'updating payment item ID: '||pnt_rec.payment_item_id||' with new rate';
1851 UPDATE pn_payment_items_all
1852 SET rate = p_new_rate
1853 WHERE payment_item_id = pnt_rec.payment_item_id;
1854
1855 END LOOP;
1856
1857 pnp_debug_pkg.debug('PNT_PAYMENT_TERMS_PKG.UPDATE_PNT_ITEMS_RATE (-)');
1858 EXCEPTION
1859 WHEN OTHERS THEN
1860 RAISE_APPLICATION_ERROR(-20001,'Error while ' || l_context || TO_CHAR(sqlcode));
1861 app_exception.Raise_Exception;
1862 pnp_debug_pkg.debug('PNT_PAYMENT_TERMS_PKG.UPDATE_PNT_ITEMS_RATE (-)');
1863 END UPDATE_PNT_ITEMS_RATE;
1864
1865 -------------------------------------------------------------------------------
1866 -- PROCEDURE : create_hist_corr_upd
1867 -- DESCRIPTION: This procedure creates history for the term record when the
1868 -- term record is 'corrected' or 'updated' in the Leases UI.
1869 -- HISTORY
1870 -- 15-OCT-04 Mrinal o Created.
1871 -- 15-JUL-05 ftanudja o R12 changes - #4495054, #4497295
1872 -- 25-JUL-05 sdmahesh o Bug 4284035 - Replaced pn_payment_terms with _ALL
1873 -- table.
1874 -------------------------------------------------------------------------------
1875 PROCEDURE create_hist_corr_upd(p_term_id IN NUMBER,
1876 p_dist_changed IN NUMBER,
1877 p_hist_dist_tab IN dist_type,
1878 p_change_mode IN VARCHAR2,
1879 p_eff_str_dt IN DATE,
1880 p_eff_end_dt IN DATE) IS
1881
1882 term_rec PN_PAYMENT_TERMS_ALL%ROWTYPE;
1883 n NUMBER(1);
1884 l_dist_change_id NUMBER(15);
1885 l_prev_term_hist_id NUMBER(15);
1886 l_new_term_id NUMBER(15);
1887 l_new_dist_id NUMBER(15);
1888 l_term_id NUMBER(15);
1889 l_dist_id NUMBER(15);
1890 l_str_dt DATE;
1891 l_end_dt DATE;
1892 l_last_update_date DATE;
1893 l_last_updated_by NUMBER;
1894 l_creation_date DATE;
1895 l_created_by NUMBER;
1896 l_last_update_login NUMBER;
1897 l_insert_dist_hist VARCHAR2(1);
1898 l_adj_type_code pn_payment_terms_history.adjustment_type_code%TYPE;
1899 l_term_comments pn_payment_terms_history.term_comments%TYPE;
1900 l_changed_flag pn_payment_terms.changed_flag%TYPE;
1901
1902 CURSOR get_term_cur(p_term_id IN NUMBER) IS
1903 SELECT *
1904 FROM pn_payment_terms_all
1905 WHERE payment_term_id = p_term_id;
1906
1907 CURSOR get_dist_cur(p_term_id IN NUMBER) IS
1908 SELECT *
1909 FROM pn_distributions_all
1910 WHERE payment_term_id = p_term_id;
1911
1912 CURSOR get_max_dist_change_id_cur(p_term_id IN NUMBER) IS
1913 SELECT MAX(pdh.distribution_change_id)
1914 FROM pn_distributions_history pdh,
1915 pn_distributions_all pd
1916 WHERE pdh.distribution_id = pd.distribution_id
1917 AND pd.payment_term_id = p_term_id;
1918
1919 CURSOR get_max_term_hist_id_cur(p_term_id IN NUMBER) IS
1920 SELECT MAX(term_history_id)
1921 FROM pn_payment_terms_history
1922 WHERE payment_term_id = p_term_id;
1923 BEGIN
1924
1925 pnp_debug_pkg.debug('PNT_PAYMENT_TERMS_PKG.create_hist_corr_upd (+)');
1926 pnp_debug_pkg.debug('create_hist_corr_upd - IN : p_term_id :'||p_term_id);
1927 pnp_debug_pkg.debug('create_hist_corr_upd - IN : p_dist_changed :'||p_dist_changed);
1928 pnp_debug_pkg.debug('create_hist_corr_upd - IN : p_hist_dist_tab.count :'||p_hist_dist_tab.count);
1929 pnp_debug_pkg.debug('create_hist_corr_upd - IN : p_change_mode :'||p_change_mode);
1930 pnp_debug_pkg.debug('create_hist_corr_upd - IN : p_eff_str_dt :'||p_eff_str_dt);
1931 pnp_debug_pkg.debug('create_hist_corr_upd - IN : p_eff_end_dt :'||p_eff_end_dt);
1932
1933 l_dist_change_id := NULL;
1934 l_prev_term_hist_id := NULL;
1935 l_new_term_id := NULL;
1936 l_new_dist_id := NULL;
1937 l_term_id := NULL;
1938 l_dist_id := NULL;
1939 l_adj_type_code := NULL;
1940 l_changed_flag := NULL;
1941
1942 OPEN get_term_cur(p_term_id);
1943 FETCH get_term_cur INTO term_rec;
1944 CLOSE get_term_cur;
1945
1946 IF pn_r12_util_pkg.is_r12 THEN
1947 term_rec.tax_group_id := null;
1948 term_rec.tax_code_id := null;
1949 tlinfo.tax_group_id := null;
1950 tlinfo.tax_code_id := null;
1951
1952 ELSE
1953 term_rec.tax_classification_code := null;
1954 tlinfo.tax_classification_code := null;
1955 END IF;
1956
1957 IF (NVL(term_rec.location_id, -9999) <> NVL(tlinfo.location_id, -9999) OR
1958 term_rec.start_date <> tlinfo.start_date OR
1959 term_rec.end_date <> tlinfo.end_date OR
1960 NVL(term_rec.estimated_amount, -9999) <> NVL(tlinfo.estimated_amount, -9999) OR
1961 NVL(term_rec.actual_amount, -9999) <> NVL(tlinfo.actual_amount, -9999) OR
1962 NVL(term_rec.vendor_id, -9999) <> NVL(tlinfo.vendor_id, -9999) OR
1963 NVL(term_rec.vendor_site_id, -9999) <> NVL(tlinfo.vendor_site_id, -9999) OR
1964 NVL(term_rec.ap_ar_term_id, -9999) <> NVL(tlinfo.ap_ar_term_id, -9999) OR
1965 NVL(term_rec.tax_group_id, -9999) <> NVL(tlinfo.tax_group_id, -9999) OR
1966 NVL(term_rec.tax_code_id, -9999) <> NVL(tlinfo.tax_code_id, -9999) OR
1967 NVL(term_rec.tax_classification_code, 'X') <> NVL(tlinfo.tax_classification_code, 'X') OR
1968 NVL(term_rec.tax_included, 'N') <> NVL(tlinfo.tax_included, 'N') OR
1969 NVL(term_rec.distribution_set_id, -9999) <> NVL(tlinfo.distribution_set_id, -9999) OR
1970 NVL(term_rec.project_id, -9999) <> NVL(tlinfo.project_id, -9999) OR
1971 NVL(term_rec.task_id, -9999) <> NVL(tlinfo.task_id, -9999) OR
1972 term_rec.expenditure_type <> tlinfo.expenditure_type OR
1973 term_rec.expenditure_item_date <> tlinfo.expenditure_item_date OR
1974 NVL(term_rec.organization_id, -9999) <> NVL(tlinfo.organization_id, -9999) OR
1975 NVL(term_rec.customer_id, -9999) <> NVL(tlinfo.customer_id, -9999) OR
1976 NVL(term_rec.customer_site_use_id, -9999) <> NVL(tlinfo.customer_site_use_id, -9999) OR
1977 NVL(term_rec.cust_ship_site_id, -9999) <> NVL(tlinfo.cust_ship_site_id, -9999) OR
1978 NVL(term_rec.cust_trx_type_id, -9999) <> NVL(tlinfo.cust_trx_type_id, -9999) OR
1979 NVL(term_rec.receipt_method_id, -9999) <> NVL(tlinfo.receipt_method_id, -9999) OR
1980 NVL(term_rec.cust_po_number, -9999) <> NVL(tlinfo.cust_po_number, -9999) OR
1981 NVL(term_rec.inv_rule_id, -9999) <> NVL(tlinfo.inv_rule_id, -9999) OR
1982 NVL(term_rec.account_rule_id, -9999) <> NVL(tlinfo.account_rule_id, -9999) OR
1983 NVL(term_rec.salesrep_id, -9999) <> NVL(tlinfo.salesrep_id, -9999)) OR
1984 NVL(p_dist_changed, 0) > 0
1985 THEN
1986
1987 pnp_debug_pkg.debug('create_hist_corr_upd : p_dist_changed :'||p_dist_changed);
1988 pnp_debug_pkg.debug('create_hist_corr_upd : term_altered_flag :'||tlinfo.term_altered_flag);
1989
1990 IF NVL(p_dist_changed, 0) >= 0 THEN
1991
1992 IF NVL(p_hist_dist_tab.count,0) <> 0 AND
1993 NVL(tlinfo.term_altered_flag, 'N') = 'N' THEN
1994
1995 SELECT pn_distributions_history_s1.NEXTVAL
1996 INTO l_dist_change_id
1997 FROM DUAL;
1998
1999 pnp_debug_pkg.debug('create_hist_corr_upd : l_dist_change_id :'||l_dist_change_id);
2000
2001 FOR i in 1..p_hist_dist_tab.count LOOP
2002
2003 pnp_debug_pkg.debug('create_hist_corr_upd : Inserting in Dist. Hist. for very first time');
2004
2005 INSERT INTO pn_distributions_history
2006 (
2007 DISTRIBUTION_HISTORY_ID
2008 ,LAST_UPDATE_DATE
2009 ,LAST_UPDATED_BY
2010 ,CREATION_DATE
2011 ,CREATED_BY
2012 ,LAST_UPDATE_LOGIN
2013 ,DISTRIBUTION_CHANGE_ID
2014 ,DISTRIBUTION_ID
2015 ,ACCOUNT_ID
2016 ,ACCOUNT_CLASS
2017 ,PERCENTAGE
2018 ,LINE_NUMBER
2019 ,ATTRIBUTE_CATEGORY
2020 ,ATTRIBUTE1
2021 ,ATTRIBUTE2
2022 ,ATTRIBUTE3
2023 ,ATTRIBUTE4
2024 ,ATTRIBUTE5
2025 ,ATTRIBUTE6
2026 ,ATTRIBUTE7
2027 ,ATTRIBUTE8
2028 ,ATTRIBUTE9
2029 ,ATTRIBUTE10
2030 ,ATTRIBUTE11
2031 ,ATTRIBUTE12
2032 ,ATTRIBUTE13
2033 ,ATTRIBUTE14
2034 ,ATTRIBUTE15)
2035 VALUES
2036 (
2037 pn_distributions_history_s.NEXTVAL
2038 ,SYSDATE
2039 ,FND_GLOBAL.USER_ID
2040 ,SYSDATE
2041 ,FND_GLOBAL.USER_ID
2042 ,FND_GLOBAL.LOGIN_ID
2043 ,l_dist_change_id
2044 ,p_hist_dist_tab(i).DISTRIBUTION_ID
2045 ,p_hist_dist_tab(i).ACCOUNT_ID
2046 ,p_hist_dist_tab(i).ACCOUNT_CLASS
2047 ,p_hist_dist_tab(i).PERCENTAGE
2048 ,p_hist_dist_tab(i).LINE_NUMBER
2049 ,p_hist_dist_tab(i).ATTRIBUTE_CATEGORY
2050 ,p_hist_dist_tab(i).ATTRIBUTE1
2051 ,p_hist_dist_tab(i).ATTRIBUTE2
2052 ,p_hist_dist_tab(i).ATTRIBUTE3
2053 ,p_hist_dist_tab(i).ATTRIBUTE4
2054 ,p_hist_dist_tab(i).ATTRIBUTE5
2055 ,p_hist_dist_tab(i).ATTRIBUTE6
2056 ,p_hist_dist_tab(i).ATTRIBUTE7
2057 ,p_hist_dist_tab(i).ATTRIBUTE8
2058 ,p_hist_dist_tab(i).ATTRIBUTE9
2059 ,p_hist_dist_tab(i).ATTRIBUTE10
2060 ,p_hist_dist_tab(i).ATTRIBUTE11
2061 ,p_hist_dist_tab(i).ATTRIBUTE12
2062 ,p_hist_dist_tab(i).ATTRIBUTE13
2063 ,p_hist_dist_tab(i).ATTRIBUTE14
2064 ,p_hist_dist_tab(i).ATTRIBUTE15
2065 );
2066 END LOOP;
2067
2068 pnp_debug_pkg.debug('create_hist_corr_upd : Inserting in Term Hist. for very first time');
2069
2070 INSERT INTO pn_payment_terms_history
2071 (
2072 TERM_HISTORY_ID
2073 ,LAST_UPDATE_DATE
2074 ,LAST_UPDATED_BY
2075 ,CREATION_DATE
2076 ,CREATED_BY
2077 ,LAST_UPDATE_LOGIN
2078 ,PAYMENT_TERM_ID
2079 ,DISTRIBUTION_CHANGE_ID
2080 ,PREV_TERM_HISTORY_ID
2081 ,ADJUSTMENT_TYPE_CODE
2082 ,TOTAL_ADJ_AMOUNT
2083 ,PAYMENT_PURPOSE_CODE
2084 ,PAYMENT_TERM_TYPE_CODE
2085 ,FREQUENCY_CODE
2086 ,LEASE_ID
2087 ,LEASE_CHANGE_ID
2088 ,START_DATE
2089 ,END_DATE
2090 ,VENDOR_ID
2091 ,VENDOR_SITE_ID
2092 ,TARGET_DATE
2093 ,ACTUAL_AMOUNT
2094 ,ESTIMATED_AMOUNT
2095 ,SET_OF_BOOKS_ID
2096 ,CURRENCY_CODE
2097 ,RATE
2098 ,ATTRIBUTE_CATEGORY
2099 ,ATTRIBUTE1
2100 ,ATTRIBUTE2
2101 ,ATTRIBUTE3
2102 ,ATTRIBUTE4
2103 ,ATTRIBUTE5
2104 ,ATTRIBUTE6
2105 ,ATTRIBUTE7
2106 ,ATTRIBUTE8
2107 ,ATTRIBUTE9
2108 ,ATTRIBUTE10
2109 ,ATTRIBUTE11
2110 ,ATTRIBUTE12
2111 ,ATTRIBUTE13
2112 ,ATTRIBUTE14
2113 ,ATTRIBUTE15
2114 ,CUSTOMER_ID
2115 ,CUSTOMER_SITE_USE_ID
2116 ,NORMALIZE
2117 ,LOCATION_ID
2118 ,SCHEDULE_DAY
2119 ,CUST_SHIP_SITE_ID
2120 ,AP_AR_TERM_ID
2121 ,CUST_TRX_TYPE_ID
2122 ,PROJECT_ID
2123 ,TASK_ID
2124 ,ORGANIZATION_ID
2125 ,EXPENDITURE_TYPE
2126 ,EXPENDITURE_ITEM_DATE
2127 ,TAX_GROUP_ID
2128 ,TAX_CODE_ID
2129 ,TAX_CLASSIFICATION_CODE
2130 ,TAX_INCLUDED
2131 ,DISTRIBUTION_SET_ID
2132 ,INV_RULE_ID
2133 ,ACCOUNT_RULE_ID
2134 ,SALESREP_ID
2135 ,APPROVED_BY
2136 ,STATUS
2137 ,INDEX_PERIOD_ID
2138 ,INDEX_TERM_INDICATOR
2139 ,PO_HEADER_ID
2140 ,CUST_PO_NUMBER
2141 ,RECEIPT_METHOD_ID
2142 ,LEGAL_ENTITY_ID
2143 ,PROJECT_ATTRIBUTE_CATEGORY
2144 ,PROJECT_ATTRIBUTE1
2145 ,PROJECT_ATTRIBUTE2
2146 ,PROJECT_ATTRIBUTE3
2147 ,PROJECT_ATTRIBUTE4
2148 ,PROJECT_ATTRIBUTE5
2149 ,PROJECT_ATTRIBUTE6
2150 ,PROJECT_ATTRIBUTE7
2151 ,PROJECT_ATTRIBUTE8
2152 ,PROJECT_ATTRIBUTE9
2153 ,PROJECT_ATTRIBUTE10
2154 ,PROJECT_ATTRIBUTE11
2155 ,PROJECT_ATTRIBUTE12
2156 ,PROJECT_ATTRIBUTE13
2157 ,PROJECT_ATTRIBUTE14
2158 ,PROJECT_ATTRIBUTE15
2159 ,VAR_RENT_INV_ID
2160 ,VAR_RENT_TYPE
2161 ,CHANGED_FLAG
2162 ,NORM_START_DATE
2163 ,TERM_TEMPLATE_ID
2164 ,EVENT_TYPE_CODE
2165 ,LEASE_STATUS
2166 ,NORM_END_DATE
2167 ,RECOVERABLE_FLAG
2168 ,PERIOD_BILLREC_ID
2169 ,AMOUNT_TYPE
2170 ,REC_AGR_LINE_ID
2171 ,GROUPING_RULE_ID
2172 ,AREA_TYPE_CODE
2173 ,AREA
2174 ,TERM_COMMENTS)
2175 VALUES
2176 (PN_PAYMENT_TERMS_HISTORY_S.NEXTVAL
2177 ,SYSDATE
2178 ,FND_GLOBAL.USER_ID
2179 ,SYSDATE
2180 ,FND_GLOBAL.USER_ID
2181 ,FND_GLOBAL.LOGIN_ID
2182 ,tlinfo.PAYMENT_TERM_ID
2183 ,l_dist_change_id
2184 ,NULL -- PREV_TERM_HISTORY_ID
2185 ,NULL -- ADJUSTMENT_TYPE_CODE
2186 ,NULL -- TOTAL_ADJ_AMOUNT
2187 ,tlinfo.PAYMENT_PURPOSE_CODE
2188 ,tlinfo.PAYMENT_TERM_TYPE_CODE
2189 ,tlinfo.FREQUENCY_CODE
2190 ,tlinfo.LEASE_ID
2191 ,tlinfo.LEASE_CHANGE_ID
2192 ,tlinfo.START_DATE
2193 ,tlinfo.END_DATE
2194 ,tlinfo.VENDOR_ID
2195 ,tlinfo.VENDOR_SITE_ID
2196 ,tlinfo.TARGET_DATE
2197 ,tlinfo.ACTUAL_AMOUNT
2198 ,tlinfo.ESTIMATED_AMOUNT
2199 ,tlinfo.SET_OF_BOOKS_ID
2200 ,tlinfo.CURRENCY_CODE
2201 ,tlinfo.RATE
2202 ,tlinfo.ATTRIBUTE_CATEGORY
2203 ,tlinfo.ATTRIBUTE1
2204 ,tlinfo.ATTRIBUTE2
2205 ,tlinfo.ATTRIBUTE3
2206 ,tlinfo.ATTRIBUTE4
2207 ,tlinfo.ATTRIBUTE5
2208 ,tlinfo.ATTRIBUTE6
2209 ,tlinfo.ATTRIBUTE7
2210 ,tlinfo.ATTRIBUTE8
2211 ,tlinfo.ATTRIBUTE9
2212 ,tlinfo.ATTRIBUTE10
2213 ,tlinfo.ATTRIBUTE11
2214 ,tlinfo.ATTRIBUTE12
2215 ,tlinfo.ATTRIBUTE13
2216 ,tlinfo.ATTRIBUTE14
2217 ,tlinfo.ATTRIBUTE15
2218 ,tlinfo.CUSTOMER_ID
2219 ,tlinfo.CUSTOMER_SITE_USE_ID
2220 ,tlinfo.NORMALIZE
2221 ,tlinfo.LOCATION_ID
2222 ,tlinfo.SCHEDULE_DAY
2223 ,tlinfo.CUST_SHIP_SITE_ID
2224 ,tlinfo.AP_AR_TERM_ID
2225 ,tlinfo.CUST_TRX_TYPE_ID
2226 ,tlinfo.PROJECT_ID
2227 ,tlinfo.TASK_ID
2228 ,tlinfo.ORGANIZATION_ID
2229 ,tlinfo.EXPENDITURE_TYPE
2230 ,tlinfo.EXPENDITURE_ITEM_DATE
2231 ,tlinfo.TAX_GROUP_ID
2232 ,tlinfo.TAX_CODE_ID
2233 ,tlinfo.TAX_CLASSIFICATION_CODE
2234 ,tlinfo.TAX_INCLUDED
2235 ,tlinfo.DISTRIBUTION_SET_ID
2236 ,tlinfo.INV_RULE_ID
2237 ,tlinfo.ACCOUNT_RULE_ID
2238 ,tlinfo.SALESREP_ID
2239 ,tlinfo.APPROVED_BY
2240 ,tlinfo.STATUS
2241 ,tlinfo.INDEX_PERIOD_ID
2242 ,tlinfo.INDEX_TERM_INDICATOR
2243 ,tlinfo.PO_HEADER_ID
2244 ,tlinfo.CUST_PO_NUMBER
2245 ,tlinfo.RECEIPT_METHOD_ID
2246 ,tlinfo.LEGAL_ENTITY_ID
2247 ,tlinfo.PROJECT_ATTRIBUTE_CATEGORY
2248 ,tlinfo.PROJECT_ATTRIBUTE1
2249 ,tlinfo.PROJECT_ATTRIBUTE2
2250 ,tlinfo.PROJECT_ATTRIBUTE3
2251 ,tlinfo.PROJECT_ATTRIBUTE4
2252 ,tlinfo.PROJECT_ATTRIBUTE5
2253 ,tlinfo.PROJECT_ATTRIBUTE6
2254 ,tlinfo.PROJECT_ATTRIBUTE7
2255 ,tlinfo.PROJECT_ATTRIBUTE8
2256 ,tlinfo.PROJECT_ATTRIBUTE9
2257 ,tlinfo.PROJECT_ATTRIBUTE10
2258 ,tlinfo.PROJECT_ATTRIBUTE11
2259 ,tlinfo.PROJECT_ATTRIBUTE12
2260 ,tlinfo.PROJECT_ATTRIBUTE13
2261 ,tlinfo.PROJECT_ATTRIBUTE14
2262 ,tlinfo.PROJECT_ATTRIBUTE15
2263 ,tlinfo.VAR_RENT_INV_ID
2264 ,tlinfo.VAR_RENT_TYPE
2265 ,tlinfo.CHANGED_FLAG
2266 ,tlinfo.NORM_START_DATE
2267 ,tlinfo.TERM_TEMPLATE_ID
2268 ,tlinfo.EVENT_TYPE_CODE
2269 ,tlinfo.LEASE_STATUS
2270 ,tlinfo.NORM_END_DATE
2271 ,tlinfo.RECOVERABLE_FLAG
2272 ,tlinfo.PERIOD_BILLREC_ID
2273 ,tlinfo.AMOUNT_TYPE
2274 ,tlinfo.REC_AGR_LINE_ID
2275 ,tlinfo.GROUPING_RULE_ID
2276 ,tlinfo.AREA_TYPE_CODE
2277 ,tlinfo.AREA
2278 ,NULL);
2279
2280 pnp_debug_pkg.debug('create_hist_corr_upd : Updating Term Altered Flag in pn_payment_terms_all');
2281
2282 UPDATE pn_payment_terms_all
2283 SET term_altered_flag = 'Y'
2284 WHERE payment_term_id = p_term_id;
2285 END IF;
2286
2287 IF NVL(p_dist_changed, 0) > 0 AND
2288 p_change_mode = 'CORRECT' THEN
2289
2290 SELECT pn_distributions_history_s1.NEXTVAL
2291 INTO l_dist_change_id
2292 FROM DUAL;
2293
2294 pnp_debug_pkg.debug('create_hist_corr_upd : '||p_change_mode|| '- l_dist_change_id : '||l_dist_change_id);
2295
2296 FOR dist_rec IN get_dist_cur(p_term_id) LOOP
2297
2298 pnp_debug_pkg.debug('create_hist_corr_upd : '||p_change_mode|| '- Inserting into Dist. Hist.');
2299
2300 INSERT INTO pn_distributions_history
2301 (
2302 DISTRIBUTION_HISTORY_ID
2303 ,LAST_UPDATE_DATE
2304 ,LAST_UPDATED_BY
2305 ,CREATION_DATE
2306 ,CREATED_BY
2307 ,LAST_UPDATE_LOGIN
2308 ,DISTRIBUTION_CHANGE_ID
2309 ,DISTRIBUTION_ID
2310 ,ACCOUNT_ID
2311 ,ACCOUNT_CLASS
2312 ,PERCENTAGE
2313 ,LINE_NUMBER
2314 ,ATTRIBUTE_CATEGORY
2315 ,ATTRIBUTE1
2316 ,ATTRIBUTE2
2317 ,ATTRIBUTE3
2318 ,ATTRIBUTE4
2319 ,ATTRIBUTE5
2320 ,ATTRIBUTE6
2321 ,ATTRIBUTE7
2322 ,ATTRIBUTE8
2323 ,ATTRIBUTE9
2324 ,ATTRIBUTE10
2325 ,ATTRIBUTE11
2326 ,ATTRIBUTE12
2327 ,ATTRIBUTE13
2328 ,ATTRIBUTE14
2329 ,ATTRIBUTE15)
2330 VALUES
2331 (
2332 pn_distributions_history_s.NEXTVAL
2333 ,SYSDATE
2334 ,FND_GLOBAL.USER_ID
2335 ,SYSDATE
2336 ,FND_GLOBAL.USER_ID
2337 ,FND_GLOBAL.LOGIN_ID
2338 ,l_dist_change_id
2339 ,dist_rec.DISTRIBUTION_ID
2340 ,dist_rec.ACCOUNT_ID
2341 ,dist_rec.ACCOUNT_CLASS
2342 ,dist_rec.PERCENTAGE
2343 ,dist_rec.LINE_NUMBER
2344 ,dist_rec.ATTRIBUTE_CATEGORY
2345 ,dist_rec.ATTRIBUTE1
2346 ,dist_rec.ATTRIBUTE2
2347 ,dist_rec.ATTRIBUTE3
2348 ,dist_rec.ATTRIBUTE4
2349 ,dist_rec.ATTRIBUTE5
2350 ,dist_rec.ATTRIBUTE6
2351 ,dist_rec.ATTRIBUTE7
2352 ,dist_rec.ATTRIBUTE8
2353 ,dist_rec.ATTRIBUTE9
2354 ,dist_rec.ATTRIBUTE10
2355 ,dist_rec.ATTRIBUTE11
2356 ,dist_rec.ATTRIBUTE12
2357 ,dist_rec.ATTRIBUTE13
2358 ,dist_rec.ATTRIBUTE14
2359 ,dist_rec.ATTRIBUTE15);
2360
2361 END LOOP;
2362 END IF;
2363 END IF;
2364
2365 OPEN get_max_dist_change_id_cur(p_term_id);
2366 FETCH get_max_dist_change_id_cur INTO l_dist_change_id;
2367 CLOSE get_max_dist_change_id_cur;
2368
2369 pnp_debug_pkg.debug('create_hist_corr_upd : '||p_change_mode|| '- get_max_dist_change_id : '||l_dist_change_id);
2370
2371 OPEN get_max_term_hist_id_cur(p_term_id);
2372 FETCH get_max_term_hist_id_cur INTO l_prev_term_hist_id;
2373 CLOSE get_max_term_hist_id_cur;
2374
2375 pnp_debug_pkg.debug('create_hist_corr_upd : '||p_change_mode|| '- get_max_term_hist_id : '||l_prev_term_hist_id);
2376
2377 IF p_change_mode = 'CORRECT' THEN
2378
2379 IF (term_rec.start_date > tlinfo.start_date AND
2380 term_rec.end_date = tlinfo.end_date AND
2381 term_rec.actual_amount = tlinfo.actual_amount) OR
2382 (term_rec.start_date = tlinfo.start_date AND
2383 term_rec.end_date < tlinfo.end_date AND
2384 term_rec.actual_amount = tlinfo.actual_amount) OR
2385 (term_rec.start_date > tlinfo.start_date AND
2386 term_rec.end_date < tlinfo.end_date AND
2387 term_rec.actual_amount = tlinfo.actual_amount) THEN
2388
2389 l_adj_type_code := 'ADJ-PCON';
2390
2391 ELSIF (term_rec.start_date < tlinfo.start_date AND
2392 term_rec.end_date = tlinfo.end_date AND
2393 term_rec.actual_amount = tlinfo.actual_amount) OR
2394 (term_rec.start_date = tlinfo.start_date AND
2395 term_rec.end_date > tlinfo.end_date AND
2396 term_rec.actual_amount = tlinfo.actual_amount) OR
2397 (term_rec.start_date < tlinfo.start_date AND
2398 term_rec.end_date > tlinfo.end_date AND
2399 term_rec.actual_amount = tlinfo.actual_amount) THEN
2400
2401 l_adj_type_code := 'ADJ-PEXP';
2402
2403 ELSIF (term_rec.start_date = tlinfo.start_date AND
2404 term_rec.end_date = tlinfo.end_date AND
2405 term_rec.actual_amount <> tlinfo.actual_amount) THEN
2406
2407 l_adj_type_code := 'ADJ-AMT';
2408
2409 ELSE
2410 l_adj_type_code := 'ADJ-MUL';
2411 END IF;
2412
2413 pnp_debug_pkg.debug('create_hist_corr_upd : l_adj_type_code :'||l_adj_type_code);
2414 pnp_debug_pkg.debug('create_hist_corr_upd : '||p_change_mode|| '- Inserting into Term Hist.');
2415
2416 INSERT INTO pn_payment_terms_history
2417 (TERM_HISTORY_ID
2418 ,LAST_UPDATE_DATE
2419 ,LAST_UPDATED_BY
2420 ,CREATION_DATE
2421 ,CREATED_BY
2422 ,LAST_UPDATE_LOGIN
2423 ,PAYMENT_TERM_ID
2424 ,DISTRIBUTION_CHANGE_ID
2425 ,PREV_TERM_HISTORY_ID
2426 ,ADJUSTMENT_TYPE_CODE
2427 ,TOTAL_ADJ_AMOUNT
2428 ,PAYMENT_PURPOSE_CODE
2429 ,PAYMENT_TERM_TYPE_CODE
2430 ,FREQUENCY_CODE
2431 ,LEASE_ID
2432 ,LEASE_CHANGE_ID
2433 ,START_DATE
2434 ,END_DATE
2435 ,VENDOR_ID
2436 ,VENDOR_SITE_ID
2437 ,TARGET_DATE
2438 ,ACTUAL_AMOUNT
2439 ,ESTIMATED_AMOUNT
2440 ,SET_OF_BOOKS_ID
2441 ,CURRENCY_CODE
2442 ,RATE
2443 ,ATTRIBUTE_CATEGORY
2444 ,ATTRIBUTE1
2445 ,ATTRIBUTE2
2446 ,ATTRIBUTE3
2447 ,ATTRIBUTE4
2448 ,ATTRIBUTE5
2449 ,ATTRIBUTE6
2450 ,ATTRIBUTE7
2451 ,ATTRIBUTE8
2452 ,ATTRIBUTE9
2453 ,ATTRIBUTE10
2454 ,ATTRIBUTE11
2455 ,ATTRIBUTE12
2456 ,ATTRIBUTE13
2457 ,ATTRIBUTE14
2458 ,ATTRIBUTE15
2459 ,CUSTOMER_ID
2460 ,CUSTOMER_SITE_USE_ID
2461 ,NORMALIZE
2462 ,LOCATION_ID
2463 ,SCHEDULE_DAY
2464 ,CUST_SHIP_SITE_ID
2465 ,AP_AR_TERM_ID
2466 ,CUST_TRX_TYPE_ID
2467 ,PROJECT_ID
2468 ,TASK_ID
2469 ,ORGANIZATION_ID
2470 ,EXPENDITURE_TYPE
2471 ,EXPENDITURE_ITEM_DATE
2472 ,TAX_GROUP_ID
2473 ,TAX_CODE_ID
2474 ,TAX_CLASSIFICATION_CODE
2475 ,TAX_INCLUDED
2476 ,DISTRIBUTION_SET_ID
2477 ,INV_RULE_ID
2478 ,ACCOUNT_RULE_ID
2479 ,SALESREP_ID
2480 ,APPROVED_BY
2481 ,STATUS
2482 ,INDEX_PERIOD_ID
2483 ,INDEX_TERM_INDICATOR
2484 ,PO_HEADER_ID
2485 ,CUST_PO_NUMBER
2486 ,RECEIPT_METHOD_ID
2487 ,LEGAL_ENTITY_ID
2488 ,PROJECT_ATTRIBUTE_CATEGORY
2489 ,PROJECT_ATTRIBUTE1
2490 ,PROJECT_ATTRIBUTE2
2491 ,PROJECT_ATTRIBUTE3
2492 ,PROJECT_ATTRIBUTE4
2493 ,PROJECT_ATTRIBUTE5
2494 ,PROJECT_ATTRIBUTE6
2495 ,PROJECT_ATTRIBUTE7
2496 ,PROJECT_ATTRIBUTE8
2497 ,PROJECT_ATTRIBUTE9
2498 ,PROJECT_ATTRIBUTE10
2499 ,PROJECT_ATTRIBUTE11
2500 ,PROJECT_ATTRIBUTE12
2501 ,PROJECT_ATTRIBUTE13
2502 ,PROJECT_ATTRIBUTE14
2503 ,PROJECT_ATTRIBUTE15
2504 ,VAR_RENT_INV_ID
2505 ,VAR_RENT_TYPE
2506 ,CHANGED_FLAG
2507 ,NORM_START_DATE
2508 ,TERM_TEMPLATE_ID
2509 ,EVENT_TYPE_CODE
2510 ,LEASE_STATUS
2511 ,NORM_END_DATE
2512 ,RECOVERABLE_FLAG
2513 ,PERIOD_BILLREC_ID
2514 ,AMOUNT_TYPE
2515 ,REC_AGR_LINE_ID
2516 ,GROUPING_RULE_ID
2517 ,AREA_TYPE_CODE
2518 ,AREA
2519 ,TERM_COMMENTS)
2520 VALUES
2521 (PN_PAYMENT_TERMS_HISTORY_S.NEXTVAL
2522 ,SYSDATE
2523 ,FND_GLOBAL.USER_ID
2524 ,SYSDATE
2525 ,FND_GLOBAL.USER_ID
2526 ,FND_GLOBAL.LOGIN_ID
2527 ,tlinfo.payment_term_id
2528 ,l_dist_change_id
2529 ,l_prev_term_hist_id
2530 ,l_adj_type_code
2531 ,NULL -- TOTAL_ADJ_AMOUNT
2532 ,term_rec.PAYMENT_PURPOSE_CODE
2533 ,term_rec.PAYMENT_TERM_TYPE_CODE
2534 ,term_rec.FREQUENCY_CODE
2535 ,term_rec.LEASE_ID
2536 ,term_rec.LEASE_CHANGE_ID
2537 ,term_rec.START_DATE
2538 ,term_rec.END_DATE
2539 ,term_rec.VENDOR_ID
2540 ,term_rec.VENDOR_SITE_ID
2541 ,term_rec.TARGET_DATE
2542 ,term_rec.ACTUAL_AMOUNT
2543 ,term_rec.ESTIMATED_AMOUNT
2544 ,term_rec.SET_OF_BOOKS_ID
2545 ,term_rec.CURRENCY_CODE
2546 ,term_rec.RATE
2547 ,term_rec.ATTRIBUTE_CATEGORY
2548 ,term_rec.ATTRIBUTE1
2549 ,term_rec.ATTRIBUTE2
2550 ,term_rec.ATTRIBUTE3
2551 ,term_rec.ATTRIBUTE4
2552 ,term_rec.ATTRIBUTE5
2553 ,term_rec.ATTRIBUTE6
2554 ,term_rec.ATTRIBUTE7
2555 ,term_rec.ATTRIBUTE8
2556 ,term_rec.ATTRIBUTE9
2557 ,term_rec.ATTRIBUTE10
2558 ,term_rec.ATTRIBUTE11
2559 ,term_rec.ATTRIBUTE12
2560 ,term_rec.ATTRIBUTE13
2561 ,term_rec.ATTRIBUTE14
2562 ,term_rec.ATTRIBUTE15
2563 ,term_rec.CUSTOMER_ID
2564 ,term_rec.CUSTOMER_SITE_USE_ID
2565 ,term_rec.NORMALIZE
2566 ,term_rec.LOCATION_ID
2567 ,term_rec.SCHEDULE_DAY
2568 ,term_rec.CUST_SHIP_SITE_ID
2569 ,term_rec.AP_AR_TERM_ID
2570 ,term_rec.CUST_TRX_TYPE_ID
2571 ,term_rec.PROJECT_ID
2572 ,term_rec.TASK_ID
2573 ,term_rec.ORGANIZATION_ID
2574 ,term_rec.EXPENDITURE_TYPE
2575 ,term_rec.EXPENDITURE_ITEM_DATE
2576 ,term_rec.TAX_GROUP_ID
2577 ,term_rec.TAX_CODE_ID
2578 ,term_rec.TAX_CLASSIFICATION_CODE
2579 ,term_rec.TAX_INCLUDED
2580 ,term_rec.DISTRIBUTION_SET_ID
2581 ,term_rec.INV_RULE_ID
2582 ,term_rec.ACCOUNT_RULE_ID
2583 ,term_rec.SALESREP_ID
2584 ,term_rec.APPROVED_BY
2585 ,term_rec.STATUS
2586 ,term_rec.INDEX_PERIOD_ID
2587 ,term_rec.INDEX_TERM_INDICATOR
2588 ,term_rec.PO_HEADER_ID
2589 ,term_rec.CUST_PO_NUMBER
2590 ,term_rec.RECEIPT_METHOD_ID
2591 ,term_rec.LEGAL_ENTITY_ID
2592 ,term_rec.PROJECT_ATTRIBUTE_CATEGORY
2593 ,term_rec.PROJECT_ATTRIBUTE1
2594 ,term_rec.PROJECT_ATTRIBUTE2
2595 ,term_rec.PROJECT_ATTRIBUTE3
2596 ,term_rec.PROJECT_ATTRIBUTE4
2597 ,term_rec.PROJECT_ATTRIBUTE5
2598 ,term_rec.PROJECT_ATTRIBUTE6
2599 ,term_rec.PROJECT_ATTRIBUTE7
2600 ,term_rec.PROJECT_ATTRIBUTE8
2601 ,term_rec.PROJECT_ATTRIBUTE9
2602 ,term_rec.PROJECT_ATTRIBUTE10
2603 ,term_rec.PROJECT_ATTRIBUTE11
2604 ,term_rec.PROJECT_ATTRIBUTE12
2605 ,term_rec.PROJECT_ATTRIBUTE13
2606 ,term_rec.PROJECT_ATTRIBUTE14
2607 ,term_rec.PROJECT_ATTRIBUTE15
2608 ,term_rec.VAR_RENT_INV_ID
2609 ,term_rec.VAR_RENT_TYPE
2610 ,term_rec.CHANGED_FLAG
2611 ,tlinfo.NORM_START_DATE
2612 ,tlinfo.TERM_TEMPLATE_ID
2613 ,tlinfo.EVENT_TYPE_CODE
2614 ,tlinfo.LEASE_STATUS
2615 ,tlinfo.NORM_END_DATE
2616 ,term_rec.RECOVERABLE_FLAG
2617 ,tlinfo.PERIOD_BILLREC_ID
2618 ,tlinfo.AMOUNT_TYPE
2619 ,tlinfo.REC_AGR_LINE_ID
2620 ,term_rec.GROUPING_RULE_ID
2621 ,term_rec.AREA_TYPE_CODE
2622 ,term_rec.AREA
2623 ,term_rec.TERM_COMMENTS);
2624 END IF;
2625
2626 IF p_change_mode = 'UPDATE' THEN
2627
2628 pnp_debug_pkg.debug('create_hist_corr_upd : '||p_change_mode|| '- p_eff_str_dt : '||p_eff_str_dt);
2629 pnp_debug_pkg.debug('create_hist_corr_upd : '||p_change_mode|| '- p_eff_end_dt : '||p_eff_end_dt);
2630 pnp_debug_pkg.debug('create_hist_corr_upd : '||p_change_mode|| '- term_str_dt : '||term_rec.start_date);
2631 pnp_debug_pkg.debug('create_hist_corr_upd : '||p_change_mode|| '- term_end_dt : '||term_rec.end_date);
2632
2633 IF (p_eff_str_dt > term_rec.start_date AND
2634 NVL(p_eff_end_dt, term_rec.end_date) <= term_rec.end_date) THEN
2635
2636 SELECT pn_payment_terms_s.nextval
2637 INTO l_new_term_id
2638 FROM DUAL;
2639
2640 l_adj_type_code := NULL;
2641 l_term_comments := NULL;
2642
2643 pnp_debug_pkg.debug('create_hist_corr_upd : '||p_change_mode|| '- l_new_term_id: '||l_new_term_id);
2644 pnp_debug_pkg.debug('create_hist_corr_upd : '||p_change_mode|| '- Updating term with new Term Id');
2645
2646 UPDATE pn_payment_terms_all
2647 SET start_date = p_eff_str_dt,
2648 end_date = NVL(p_eff_end_dt,term_rec.end_date),
2649 term_altered_flag = 'Y',
2650 changed_flag = 'N',
2651 payment_term_id = l_new_term_id
2652 WHERE payment_term_id = p_term_id;
2653
2654 SELECT pn_distributions_history_s1.NEXTVAL
2655 INTO l_dist_change_id
2656 FROM DUAL;
2657
2658 pnp_debug_pkg.debug('create_hist_corr_upd : '||p_change_mode|| '- l_dist_change_id: '||l_dist_change_id);
2659
2660 FOR dist_rec IN get_dist_cur(p_term_id) LOOP
2661
2662 l_new_dist_id := NULL;
2663 SELECT pn_distributions_s.nextval
2664 INTO l_new_dist_id
2665 FROM DUAL;
2666
2667 pnp_debug_pkg.debug('create_hist_corr_upd : '||p_change_mode|| '- l_new_dist_id : '||l_new_dist_id);
2668
2669 UPDATE pn_distributions_all
2670 SET distribution_id = l_new_dist_id,
2671 payment_term_id = l_new_term_id
2672 WHERE distribution_id = dist_rec.distribution_id;
2673
2674 pnp_debug_pkg.debug('create_hist_corr_upd : '||p_change_mode|| '- Inserting into Dist. Hist.');
2675
2676 INSERT INTO pn_distributions_history
2677 (DISTRIBUTION_HISTORY_ID
2678 ,LAST_UPDATE_DATE
2679 ,LAST_UPDATED_BY
2680 ,CREATION_DATE
2681 ,CREATED_BY
2682 ,LAST_UPDATE_LOGIN
2683 ,DISTRIBUTION_CHANGE_ID
2684 ,DISTRIBUTION_ID
2685 ,ACCOUNT_ID
2686 ,ACCOUNT_CLASS
2687 ,PERCENTAGE
2688 ,LINE_NUMBER
2689 ,ATTRIBUTE_CATEGORY
2690 ,ATTRIBUTE1
2691 ,ATTRIBUTE2
2692 ,ATTRIBUTE3
2693 ,ATTRIBUTE4
2694 ,ATTRIBUTE5
2695 ,ATTRIBUTE6
2696 ,ATTRIBUTE7
2697 ,ATTRIBUTE8
2698 ,ATTRIBUTE9
2699 ,ATTRIBUTE10
2700 ,ATTRIBUTE11
2701 ,ATTRIBUTE12
2702 ,ATTRIBUTE13
2703 ,ATTRIBUTE14
2704 ,ATTRIBUTE15)
2705 VALUES
2706 (pn_distributions_history_s.NEXTVAL
2707 ,SYSDATE
2708 ,FND_GLOBAL.USER_ID
2709 ,SYSDATE
2710 ,FND_GLOBAL.USER_ID
2711 ,FND_GLOBAL.LOGIN_ID
2712 ,l_dist_change_id
2713 ,l_new_dist_id
2714 ,dist_rec.ACCOUNT_ID
2715 ,dist_rec.ACCOUNT_CLASS
2716 ,dist_rec.PERCENTAGE
2717 ,dist_rec.LINE_NUMBER
2718 ,dist_rec.ATTRIBUTE_CATEGORY
2719 ,dist_rec.ATTRIBUTE1
2720 ,dist_rec.ATTRIBUTE2
2721 ,dist_rec.ATTRIBUTE3
2722 ,dist_rec.ATTRIBUTE4
2723 ,dist_rec.ATTRIBUTE5
2724 ,dist_rec.ATTRIBUTE6
2725 ,dist_rec.ATTRIBUTE7
2726 ,dist_rec.ATTRIBUTE8
2727 ,dist_rec.ATTRIBUTE9
2728 ,dist_rec.ATTRIBUTE10
2729 ,dist_rec.ATTRIBUTE11
2730 ,dist_rec.ATTRIBUTE12
2731 ,dist_rec.ATTRIBUTE13
2732 ,dist_rec.ATTRIBUTE14
2733 ,dist_rec.ATTRIBUTE15);
2734
2735 END LOOP;
2736
2737 ELSIF (p_eff_str_dt = term_rec.start_date AND
2738 NVL(p_eff_end_dt, term_rec.end_date) <= term_rec.end_date) THEN
2739
2740 pnp_debug_pkg.debug('create_hist_corr_upd : '||p_change_mode|| '- Updating term with Eff. End Date');
2741
2742 UPDATE pn_payment_terms_all
2743 SET end_date = NVL(p_eff_end_dt, term_rec.end_date),
2744 term_altered_flag = 'Y',
2745 changed_flag = 'Y'
2746 WHERE payment_term_id = p_term_id;
2747
2748 l_new_term_id := tlinfo.payment_term_id;
2749 l_adj_type_code := 'ADJ-PCON';
2750 l_term_comments := term_rec.term_comments;
2751
2752 pnp_debug_pkg.debug('create_hist_corr_upd : '||p_change_mode|| '- l_new_term_id: '||l_new_term_id);
2753
2754 END IF;
2755
2756 IF (p_eff_str_dt >= term_rec.start_date AND
2757 NVL(p_eff_end_dt, term_rec.end_date) <= term_rec.end_date) THEN
2758
2759 pnp_debug_pkg.debug('create_hist_corr_upd : '||p_change_mode|| '- l_new_term_id: '||l_new_term_id);
2760 pnp_debug_pkg.debug('create_hist_corr_upd : '||p_change_mode|| '- l_dist_change_id: '||l_dist_change_id);
2761 pnp_debug_pkg.debug('create_hist_corr_upd : '||p_change_mode|| '- Inserting into Term Hist.');
2762
2763 INSERT INTO pn_payment_terms_history
2764 (TERM_HISTORY_ID
2765 ,LAST_UPDATE_DATE
2766 ,LAST_UPDATED_BY
2767 ,CREATION_DATE
2768 ,CREATED_BY
2769 ,LAST_UPDATE_LOGIN
2770 ,PAYMENT_TERM_ID
2771 ,DISTRIBUTION_CHANGE_ID
2772 ,PREV_TERM_HISTORY_ID
2773 ,ADJUSTMENT_TYPE_CODE
2774 ,TOTAL_ADJ_AMOUNT
2775 ,PAYMENT_PURPOSE_CODE
2776 ,PAYMENT_TERM_TYPE_CODE
2777 ,FREQUENCY_CODE
2778 ,LEASE_ID
2779 ,LEASE_CHANGE_ID
2780 ,START_DATE
2781 ,END_DATE
2782 ,VENDOR_ID
2783 ,VENDOR_SITE_ID
2784 ,TARGET_DATE
2785 ,ACTUAL_AMOUNT
2786 ,ESTIMATED_AMOUNT
2787 ,SET_OF_BOOKS_ID
2788 ,CURRENCY_CODE
2789 ,RATE
2790 ,ATTRIBUTE_CATEGORY
2791 ,ATTRIBUTE1
2792 ,ATTRIBUTE2
2793 ,ATTRIBUTE3
2794 ,ATTRIBUTE4
2795 ,ATTRIBUTE5
2796 ,ATTRIBUTE6
2797 ,ATTRIBUTE7
2798 ,ATTRIBUTE8
2799 ,ATTRIBUTE9
2800 ,ATTRIBUTE10
2801 ,ATTRIBUTE11
2802 ,ATTRIBUTE12
2803 ,ATTRIBUTE13
2804 ,ATTRIBUTE14
2805 ,ATTRIBUTE15
2806 ,CUSTOMER_ID
2807 ,CUSTOMER_SITE_USE_ID
2808 ,NORMALIZE
2809 ,LOCATION_ID
2810 ,SCHEDULE_DAY
2811 ,CUST_SHIP_SITE_ID
2812 ,AP_AR_TERM_ID
2813 ,CUST_TRX_TYPE_ID
2814 ,PROJECT_ID
2815 ,TASK_ID
2816 ,ORGANIZATION_ID
2817 ,EXPENDITURE_TYPE
2818 ,EXPENDITURE_ITEM_DATE
2819 ,TAX_GROUP_ID
2820 ,TAX_CODE_ID
2821 ,TAX_CLASSIFICATION_CODE
2822 ,TAX_INCLUDED
2823 ,DISTRIBUTION_SET_ID
2824 ,INV_RULE_ID
2825 ,ACCOUNT_RULE_ID
2826 ,SALESREP_ID
2827 ,APPROVED_BY
2828 ,STATUS
2829 ,INDEX_PERIOD_ID
2830 ,INDEX_TERM_INDICATOR
2831 ,PO_HEADER_ID
2832 ,CUST_PO_NUMBER
2833 ,RECEIPT_METHOD_ID
2834 ,LEGAL_ENTITY_ID
2835 ,PROJECT_ATTRIBUTE_CATEGORY
2836 ,PROJECT_ATTRIBUTE1
2837 ,PROJECT_ATTRIBUTE2
2838 ,PROJECT_ATTRIBUTE3
2839 ,PROJECT_ATTRIBUTE4
2840 ,PROJECT_ATTRIBUTE5
2841 ,PROJECT_ATTRIBUTE6
2842 ,PROJECT_ATTRIBUTE7
2843 ,PROJECT_ATTRIBUTE8
2844 ,PROJECT_ATTRIBUTE9
2845 ,PROJECT_ATTRIBUTE10
2846 ,PROJECT_ATTRIBUTE11
2847 ,PROJECT_ATTRIBUTE12
2848 ,PROJECT_ATTRIBUTE13
2849 ,PROJECT_ATTRIBUTE14
2850 ,PROJECT_ATTRIBUTE15
2851 ,VAR_RENT_INV_ID
2852 ,VAR_RENT_TYPE
2853 ,CHANGED_FLAG
2854 ,NORM_START_DATE
2855 ,TERM_TEMPLATE_ID
2856 ,EVENT_TYPE_CODE
2857 ,LEASE_STATUS
2858 ,NORM_END_DATE
2859 ,RECOVERABLE_FLAG
2860 ,PERIOD_BILLREC_ID
2861 ,AMOUNT_TYPE
2862 ,REC_AGR_LINE_ID
2863 ,GROUPING_RULE_ID
2864 ,AREA_TYPE_CODE
2865 ,AREA
2866 ,TERM_COMMENTS)
2867 VALUES
2868 (PN_PAYMENT_TERMS_HISTORY_S.NEXTVAL
2869 ,SYSDATE
2870 ,FND_GLOBAL.USER_ID
2871 ,SYSDATE
2872 ,FND_GLOBAL.USER_ID
2873 ,FND_GLOBAL.LOGIN_ID
2874 ,l_new_term_id
2875 ,l_dist_change_id
2876 ,l_prev_term_hist_id
2877 ,l_adj_type_code
2878 ,NULL -- TOTAL_ADJ_AMOUNT
2879 ,term_rec.PAYMENT_PURPOSE_CODE
2880 ,term_rec.PAYMENT_TERM_TYPE_CODE
2881 ,term_rec.FREQUENCY_CODE
2882 ,term_rec.LEASE_ID
2883 ,term_rec.LEASE_CHANGE_ID
2884 ,p_eff_str_dt
2885 ,NVL(p_eff_end_dt,term_rec.end_date)
2886 ,term_rec.VENDOR_ID
2887 ,term_rec.VENDOR_SITE_ID
2888 ,term_rec.TARGET_DATE
2889 ,term_rec.ACTUAL_AMOUNT
2890 ,term_rec.ESTIMATED_AMOUNT
2891 ,term_rec.SET_OF_BOOKS_ID
2892 ,term_rec.CURRENCY_CODE
2893 ,term_rec.RATE
2894 ,term_rec.ATTRIBUTE_CATEGORY
2895 ,term_rec.ATTRIBUTE1
2896 ,term_rec.ATTRIBUTE2
2897 ,term_rec.ATTRIBUTE3
2898 ,term_rec.ATTRIBUTE4
2899 ,term_rec.ATTRIBUTE5
2900 ,term_rec.ATTRIBUTE6
2901 ,term_rec.ATTRIBUTE7
2902 ,term_rec.ATTRIBUTE8
2903 ,term_rec.ATTRIBUTE9
2904 ,term_rec.ATTRIBUTE10
2905 ,term_rec.ATTRIBUTE11
2906 ,term_rec.ATTRIBUTE12
2907 ,term_rec.ATTRIBUTE13
2908 ,term_rec.ATTRIBUTE14
2909 ,term_rec.ATTRIBUTE15
2910 ,term_rec.CUSTOMER_ID
2911 ,term_rec.CUSTOMER_SITE_USE_ID
2912 ,term_rec.NORMALIZE
2913 ,term_rec.LOCATION_ID
2914 ,term_rec.SCHEDULE_DAY
2915 ,term_rec.CUST_SHIP_SITE_ID
2916 ,term_rec.AP_AR_TERM_ID
2917 ,term_rec.CUST_TRX_TYPE_ID
2918 ,term_rec.PROJECT_ID
2919 ,term_rec.TASK_ID
2920 ,term_rec.ORGANIZATION_ID
2921 ,term_rec.EXPENDITURE_TYPE
2922 ,term_rec.EXPENDITURE_ITEM_DATE
2923 ,term_rec.TAX_GROUP_ID
2924 ,term_rec.TAX_CODE_ID
2925 ,term_rec.TAX_CLASSIFICATION_CODE
2926 ,term_rec.TAX_INCLUDED
2927 ,term_rec.DISTRIBUTION_SET_ID
2928 ,term_rec.INV_RULE_ID
2929 ,term_rec.ACCOUNT_RULE_ID
2930 ,term_rec.SALESREP_ID
2931 ,term_rec.APPROVED_BY
2932 ,term_rec.STATUS
2933 ,term_rec.INDEX_PERIOD_ID
2934 ,term_rec.INDEX_TERM_INDICATOR
2935 ,term_rec.PO_HEADER_ID
2936 ,term_rec.CUST_PO_NUMBER
2937 ,term_rec.RECEIPT_METHOD_ID
2938 ,term_rec.LEGAL_ENTITY_ID
2939 ,term_rec.PROJECT_ATTRIBUTE_CATEGORY
2940 ,term_rec.PROJECT_ATTRIBUTE1
2941 ,term_rec.PROJECT_ATTRIBUTE2
2942 ,term_rec.PROJECT_ATTRIBUTE3
2943 ,term_rec.PROJECT_ATTRIBUTE4
2944 ,term_rec.PROJECT_ATTRIBUTE5
2945 ,term_rec.PROJECT_ATTRIBUTE6
2946 ,term_rec.PROJECT_ATTRIBUTE7
2947 ,term_rec.PROJECT_ATTRIBUTE8
2948 ,term_rec.PROJECT_ATTRIBUTE9
2949 ,term_rec.PROJECT_ATTRIBUTE10
2950 ,term_rec.PROJECT_ATTRIBUTE11
2951 ,term_rec.PROJECT_ATTRIBUTE12
2952 ,term_rec.PROJECT_ATTRIBUTE13
2953 ,term_rec.PROJECT_ATTRIBUTE14
2954 ,term_rec.PROJECT_ATTRIBUTE15
2955 ,term_rec.VAR_RENT_INV_ID
2956 ,term_rec.VAR_RENT_TYPE
2957 ,term_rec.CHANGED_FLAG
2958 ,tlinfo.NORM_START_DATE
2959 ,tlinfo.TERM_TEMPLATE_ID
2960 ,tlinfo.EVENT_TYPE_CODE
2961 ,tlinfo.LEASE_STATUS
2962 ,tlinfo.NORM_END_DATE
2963 ,term_rec.RECOVERABLE_FLAG
2964 ,tlinfo.PERIOD_BILLREC_ID
2965 ,tlinfo.AMOUNT_TYPE
2966 ,tlinfo.REC_AGR_LINE_ID
2967 ,term_rec.GROUPING_RULE_ID
2968 ,term_rec.AREA_TYPE_CODE
2969 ,term_rec.AREA
2970 ,l_term_comments);
2971
2972 END IF;
2973
2974 pnp_debug_pkg.debug('create_hist_corr_upd : '||p_change_mode|| '- Setting value of counter n');
2975
2976 IF p_eff_str_dt <> term_rec.start_date THEN
2977 IF NVL(p_eff_end_dt, term_rec.end_date) = term_rec.end_date THEN
2978 n := 1;
2979 ELSIF NVL(p_eff_end_dt, term_rec.end_date) < term_rec.end_date THEN
2980 n := 2;
2981 END IF;
2982 ELSIF p_eff_str_dt = term_rec.start_date THEN
2983 n := 1;
2984 END IF;
2985
2986 pnp_debug_pkg.debug('create_hist_corr_upd : '||p_change_mode|| '- Check counter value n :'||n);
2987
2988 FOR i in 1..n LOOP
2989
2990 pnp_debug_pkg.debug('create_hist_corr_upd : '||p_change_mode|| '- In Term Loop for i :'||i);
2991
2992 IF p_eff_str_dt <> term_rec.start_date AND
2993 i = 1 THEN
2994
2995 l_term_id := p_term_id;
2996 l_str_dt := term_rec.start_date;
2997 l_end_dt := p_eff_str_dt - 1;
2998 l_adj_type_code := 'ADJ-PCON';
2999 l_term_comments := term_rec.term_comments;
3000 l_last_update_date := SYSDATE;
3001 l_last_updated_by := FND_GLOBAL.USER_ID;
3002 l_creation_date := tlinfo.creation_date;
3003 l_created_by := tlinfo.created_by;
3004 l_last_update_login := FND_GLOBAL.LOGIN_ID;
3005 l_changed_flag := 'Y';
3006
3007 ELSIF (p_eff_str_dt <> term_rec.start_date AND
3008 i = 2) OR
3009 (p_eff_str_dt = term_rec.start_date AND
3010 i = 1) THEN
3011
3012 SELECT pn_payment_terms_s.nextval
3013 INTO l_term_id
3014 FROM DUAL;
3015
3016 l_str_dt := NVL(p_eff_end_dt, term_rec.end_date) + 1;
3017 l_end_dt := term_rec.end_date;
3018 l_adj_type_code := NULL;
3019 l_term_comments := NULL;
3020 l_last_update_date := SYSDATE;
3021 l_last_updated_by := FND_GLOBAL.USER_ID;
3022 l_creation_date := SYSDATE;
3023 l_created_by := FND_GLOBAL.USER_ID;
3024 l_last_update_login := FND_GLOBAL.LOGIN_ID;
3025 l_changed_flag := 'N';
3026
3027 END IF;
3028
3029 pnp_debug_pkg.debug('create_hist_corr_upd : '||p_change_mode|| '- l_term_id : '||l_term_id);
3030 pnp_debug_pkg.debug('create_hist_corr_upd : '||p_change_mode|| '- l_str_dt : '||l_str_dt);
3031 pnp_debug_pkg.debug('create_hist_corr_upd : '||p_change_mode|| '- l_end_dt : '||l_end_dt);
3032 pnp_debug_pkg.debug('create_hist_corr_upd : '||p_change_mode|| '- Inserting into Terms table.');
3033
3034 INSERT INTO pn_payment_terms_all
3035 (PAYMENT_TERM_ID
3036 ,LAST_UPDATE_DATE
3037 ,LAST_UPDATED_BY
3038 ,CREATION_DATE
3039 ,CREATED_BY
3040 ,LAST_UPDATE_LOGIN
3041 ,PAYMENT_PURPOSE_CODE
3042 ,PAYMENT_TERM_TYPE_CODE
3043 ,FREQUENCY_CODE
3044 ,LEASE_ID
3045 ,LEASE_CHANGE_ID
3046 ,START_DATE
3047 ,END_DATE
3048 ,VENDOR_ID
3049 ,VENDOR_SITE_ID
3050 ,CUSTOMER_ID
3051 ,CUSTOMER_SITE_USE_ID
3052 ,TARGET_DATE
3053 ,ACTUAL_AMOUNT
3054 ,ESTIMATED_AMOUNT
3055 ,SET_OF_BOOKS_ID
3056 ,CURRENCY_CODE
3057 ,RATE
3058 ,NORMALIZE
3059 ,LOCATION_ID
3060 ,SCHEDULE_DAY
3061 ,CUST_SHIP_SITE_ID
3062 ,AP_AR_TERM_ID
3063 ,CUST_TRX_TYPE_ID
3064 ,PROJECT_ID
3065 ,TASK_ID
3066 ,ORGANIZATION_ID
3067 ,EXPENDITURE_TYPE
3068 ,EXPENDITURE_ITEM_DATE
3069 ,TAX_GROUP_ID
3070 ,TAX_CODE_ID
3071 ,TAX_CLASSIFICATION_CODE
3072 ,TAX_INCLUDED
3073 ,DISTRIBUTION_SET_ID
3074 ,INV_RULE_ID
3075 ,ACCOUNT_RULE_ID
3076 ,SALESREP_ID
3077 ,APPROVED_BY
3078 ,STATUS
3079 ,INDEX_PERIOD_ID
3080 ,INDEX_TERM_INDICATOR
3081 ,PO_HEADER_ID
3082 ,CUST_PO_NUMBER
3083 ,RECEIPT_METHOD_ID
3084 ,VAR_RENT_INV_ID
3085 ,VAR_RENT_TYPE
3086 ,PERIOD_BILLREC_ID
3087 ,REC_AGR_LINE_ID
3088 ,AMOUNT_TYPE
3089 ,CHANGED_FLAG
3090 ,TERM_TEMPLATE_ID
3091 ,LEGAL_ENTITY_ID
3092 ,ATTRIBUTE_CATEGORY
3093 ,ATTRIBUTE1
3094 ,ATTRIBUTE2
3095 ,ATTRIBUTE3
3096 ,ATTRIBUTE4
3097 ,ATTRIBUTE5
3098 ,ATTRIBUTE6
3099 ,ATTRIBUTE7
3100 ,ATTRIBUTE8
3101 ,ATTRIBUTE9
3102 ,ATTRIBUTE10
3103 ,ATTRIBUTE11
3104 ,ATTRIBUTE12
3105 ,ATTRIBUTE13
3106 ,ATTRIBUTE14
3107 ,ATTRIBUTE15
3108 ,PROJECT_ATTRIBUTE_CATEGORY
3109 ,PROJECT_ATTRIBUTE1
3110 ,PROJECT_ATTRIBUTE2
3111 ,PROJECT_ATTRIBUTE3
3112 ,PROJECT_ATTRIBUTE4
3113 ,PROJECT_ATTRIBUTE5
3114 ,PROJECT_ATTRIBUTE6
3115 ,PROJECT_ATTRIBUTE7
3116 ,PROJECT_ATTRIBUTE8
3117 ,PROJECT_ATTRIBUTE9
3118 ,PROJECT_ATTRIBUTE10
3119 ,PROJECT_ATTRIBUTE11
3120 ,PROJECT_ATTRIBUTE12
3121 ,PROJECT_ATTRIBUTE13
3122 ,PROJECT_ATTRIBUTE14
3123 ,PROJECT_ATTRIBUTE15
3124 ,org_id
3125 ,lease_status
3126 ,recoverable_flag
3127 ,area_type_code
3128 ,area
3129 ,grouping_rule_id
3130 ,term_altered_flag
3131 ,source_code
3132 ,term_comments)
3133 VALUES
3134 (l_term_id
3135 ,l_last_update_date
3136 ,l_last_updated_by
3137 ,l_creation_date
3138 ,l_created_by
3139 ,l_last_update_login
3140 ,tlinfo.PAYMENT_PURPOSE_CODE
3141 ,tlinfo.PAYMENT_TERM_TYPE_CODE
3142 ,tlinfo.FREQUENCY_CODE
3143 ,tlinfo.LEASE_ID
3144 ,term_rec.LEASE_CHANGE_ID
3145 ,l_str_dt
3146 ,l_end_dt
3147 ,tlinfo.VENDOR_ID
3148 ,tlinfo.VENDOR_SITE_ID
3149 ,tlinfo.CUSTOMER_ID
3150 ,tlinfo.CUSTOMER_SITE_USE_ID
3151 ,tlinfo.TARGET_DATE
3152 ,tlinfo.ACTUAL_AMOUNT
3153 ,tlinfo.ESTIMATED_AMOUNT
3154 ,tlinfo.SET_OF_BOOKS_ID
3155 ,tlinfo.CURRENCY_CODE
3156 ,tlinfo.RATE
3157 ,tlinfo.NORMALIZE
3158 ,tlinfo.LOCATION_ID
3159 ,tlinfo.SCHEDULE_DAY
3160 ,tlinfo.CUST_SHIP_SITE_ID
3161 ,tlinfo.AP_AR_TERM_ID
3162 ,tlinfo.CUST_TRX_TYPE_ID
3163 ,tlinfo.PROJECT_ID
3164 ,tlinfo.TASK_ID
3165 ,tlinfo.ORGANIZATION_ID
3166 ,tlinfo.EXPENDITURE_TYPE
3167 ,tlinfo.EXPENDITURE_ITEM_DATE
3168 ,tlinfo.TAX_GROUP_ID
3169 ,tlinfo.TAX_CODE_ID
3170 ,tlinfo.TAX_CLASSIFICATION_CODE
3171 ,tlinfo.TAX_INCLUDED
3172 ,tlinfo.DISTRIBUTION_SET_ID
3173 ,tlinfo.INV_RULE_ID
3174 ,tlinfo.ACCOUNT_RULE_ID
3175 ,tlinfo.SALESREP_ID
3176 ,tlinfo.APPROVED_BY
3177 ,tlinfo.STATUS
3178 ,tlinfo.INDEX_PERIOD_ID
3179 ,tlinfo.INDEX_TERM_INDICATOR
3180 ,tlinfo.PO_HEADER_ID
3181 ,tlinfo.CUST_PO_NUMBER
3182 ,tlinfo.RECEIPT_METHOD_ID
3183 ,tlinfo.VAR_RENT_INV_ID
3184 ,tlinfo.VAR_RENT_TYPE
3185 ,tlinfo.PERIOD_BILLREC_ID
3186 ,tlinfo.REC_AGR_LINE_ID
3187 ,tlinfo.AMOUNT_TYPE
3188 ,l_changed_flag
3189 ,tlinfo.TERM_TEMPLATE_ID
3190 ,tlinfo.LEGAL_ENTITY_ID
3191 ,tlinfo.ATTRIBUTE_CATEGORY
3192 ,tlinfo.ATTRIBUTE1
3193 ,tlinfo.ATTRIBUTE2
3194 ,tlinfo.ATTRIBUTE3
3195 ,tlinfo.ATTRIBUTE4
3196 ,tlinfo.ATTRIBUTE5
3197 ,tlinfo.ATTRIBUTE6
3198 ,tlinfo.ATTRIBUTE7
3199 ,tlinfo.ATTRIBUTE8
3200 ,tlinfo.ATTRIBUTE9
3201 ,tlinfo.ATTRIBUTE10
3202 ,tlinfo.ATTRIBUTE11
3203 ,tlinfo.ATTRIBUTE12
3204 ,tlinfo.ATTRIBUTE13
3205 ,tlinfo.ATTRIBUTE14
3206 ,tlinfo.ATTRIBUTE15
3207 ,tlinfo.PROJECT_ATTRIBUTE_CATEGORY
3208 ,tlinfo.PROJECT_ATTRIBUTE1
3209 ,tlinfo.PROJECT_ATTRIBUTE2
3210 ,tlinfo.PROJECT_ATTRIBUTE3
3211 ,tlinfo.PROJECT_ATTRIBUTE4
3212 ,tlinfo.PROJECT_ATTRIBUTE5
3213 ,tlinfo.PROJECT_ATTRIBUTE6
3214 ,tlinfo.PROJECT_ATTRIBUTE7
3215 ,tlinfo.PROJECT_ATTRIBUTE8
3216 ,tlinfo.PROJECT_ATTRIBUTE9
3217 ,tlinfo.PROJECT_ATTRIBUTE10
3218 ,tlinfo.PROJECT_ATTRIBUTE11
3219 ,tlinfo.PROJECT_ATTRIBUTE12
3220 ,tlinfo.PROJECT_ATTRIBUTE13
3221 ,tlinfo.PROJECT_ATTRIBUTE14
3222 ,tlinfo.PROJECT_ATTRIBUTE15
3223 ,tlinfo.org_id
3224 ,tlinfo.lease_status
3225 ,tlinfo.recoverable_flag
3226 ,tlinfo.area_type_code
3227 ,tlinfo.area
3228 ,tlinfo.grouping_rule_id
3229 ,'Y'
3230 ,tlinfo.source_code
3231 ,tlinfo.term_comments);
3232
3233 IF NVL(p_hist_dist_tab.count,0) <> 0 THEN
3234
3235 SELECT pn_distributions_history_s1.NEXTVAL
3236 INTO l_dist_change_id
3237 FROM DUAL;
3238
3239 l_insert_dist_hist := 'N';
3240
3241 FOR j in 1..p_hist_dist_tab.count LOOP
3242
3243 pnp_debug_pkg.debug('create_hist_corr_upd : '||p_change_mode|| '- In Dist. Loop for j :'||j);
3244
3245 IF p_eff_str_dt <> term_rec.start_date AND
3246 i = 1 THEN
3247
3248 l_dist_id := p_hist_dist_tab(j).distribution_id;
3249 l_insert_dist_hist := 'N';
3250
3251 ELSIF (p_eff_str_dt <> term_rec.start_date AND
3252 i = 2) OR
3253 (p_eff_str_dt = term_rec.start_date AND
3254 i = 1) THEN
3255
3256 SELECT pn_distributions_s.NEXTVAL
3257 INTO l_dist_id
3258 FROM DUAL;
3259
3260 l_insert_dist_hist := 'Y';
3261
3262 END IF;
3263
3264 pnp_debug_pkg.debug('create_hist_corr_upd : '||p_change_mode|| '- l_dist_id : '||l_dist_id);
3265 pnp_debug_pkg.debug('create_hist_corr_upd : '||p_change_mode|| '- l_insert_dist_hist: '||l_insert_dist_hist);
3266 pnp_debug_pkg.debug('create_hist_corr_upd : '||p_change_mode|| '- Inserting into Dist. table.');
3267
3268 INSERT INTO pn_distributions_all
3269 (distribution_id
3270 ,account_id
3271 ,payment_term_id
3272 ,term_template_id
3273 ,account_class
3274 ,percentage
3275 ,line_number
3276 ,last_update_date
3277 ,last_updated_by
3278 ,creation_date
3279 ,created_by
3280 ,last_update_login
3281 ,attribute_category
3282 ,attribute1
3283 ,attribute2
3284 ,attribute3
3285 ,attribute4
3286 ,attribute5
3287 ,attribute6
3288 ,attribute7
3289 ,attribute8
3290 ,attribute9
3291 ,attribute10
3292 ,attribute11
3293 ,attribute12
3294 ,attribute13
3295 ,attribute14
3296 ,attribute15
3297 ,org_id)
3298 VALUES
3299 (l_dist_id
3300 ,p_hist_dist_tab(j).account_id
3301 ,l_term_id
3302 ,p_hist_dist_tab(j).term_template_id
3303 ,p_hist_dist_tab(j).account_class
3304 ,p_hist_dist_tab(j).percentage
3305 ,p_hist_dist_tab(j).line_number
3306 ,l_last_update_date
3307 ,l_last_updated_by
3308 ,l_creation_date
3309 ,l_created_by
3310 ,l_last_update_login
3311 ,p_hist_dist_tab(j).attribute_category
3312 ,p_hist_dist_tab(j).attribute1
3313 ,p_hist_dist_tab(j).attribute2
3314 ,p_hist_dist_tab(j).attribute3
3315 ,p_hist_dist_tab(j).attribute4
3316 ,p_hist_dist_tab(j).attribute5
3317 ,p_hist_dist_tab(j).attribute6
3318 ,p_hist_dist_tab(j).attribute7
3319 ,p_hist_dist_tab(j).attribute8
3320 ,p_hist_dist_tab(j).attribute9
3321 ,p_hist_dist_tab(j).attribute10
3322 ,p_hist_dist_tab(j).attribute11
3323 ,p_hist_dist_tab(j).attribute12
3324 ,p_hist_dist_tab(j).attribute13
3325 ,p_hist_dist_tab(j).attribute14
3326 ,p_hist_dist_tab(j).attribute15
3327 ,p_hist_dist_tab(j).org_id);
3328
3329 IF l_insert_dist_hist = 'Y' THEN
3330
3331 pnp_debug_pkg.debug('create_hist_corr_upd : '||p_change_mode|| '- Inserting into Dist. Hist. table.');
3332
3333 INSERT INTO pn_distributions_history
3334 (DISTRIBUTION_HISTORY_ID
3335 ,LAST_UPDATE_DATE
3336 ,LAST_UPDATED_BY
3337 ,CREATION_DATE
3338 ,CREATED_BY
3339 ,LAST_UPDATE_LOGIN
3340 ,DISTRIBUTION_CHANGE_ID
3341 ,DISTRIBUTION_ID
3342 ,ACCOUNT_ID
3343 ,ACCOUNT_CLASS
3344 ,PERCENTAGE
3345 ,LINE_NUMBER
3346 ,ATTRIBUTE_CATEGORY
3347 ,ATTRIBUTE1
3348 ,ATTRIBUTE2
3349 ,ATTRIBUTE3
3350 ,ATTRIBUTE4
3351 ,ATTRIBUTE5
3352 ,ATTRIBUTE6
3353 ,ATTRIBUTE7
3354 ,ATTRIBUTE8
3355 ,ATTRIBUTE9
3356 ,ATTRIBUTE10
3357 ,ATTRIBUTE11
3358 ,ATTRIBUTE12
3359 ,ATTRIBUTE13
3360 ,ATTRIBUTE14
3361 ,ATTRIBUTE15)
3362 VALUES
3363 (pn_distributions_history_s.NEXTVAL
3364 ,SYSDATE
3365 ,FND_GLOBAL.USER_ID
3366 ,SYSDATE
3367 ,FND_GLOBAL.USER_ID
3368 ,FND_GLOBAL.LOGIN_ID
3369 ,l_dist_change_id
3370 ,l_dist_id
3371 ,p_hist_dist_tab(j).ACCOUNT_ID
3372 ,p_hist_dist_tab(j).ACCOUNT_CLASS
3373 ,p_hist_dist_tab(j).PERCENTAGE
3374 ,p_hist_dist_tab(j).LINE_NUMBER
3375 ,p_hist_dist_tab(j).ATTRIBUTE_CATEGORY
3376 ,p_hist_dist_tab(j).ATTRIBUTE1
3377 ,p_hist_dist_tab(j).ATTRIBUTE2
3378 ,p_hist_dist_tab(j).ATTRIBUTE3
3379 ,p_hist_dist_tab(j).ATTRIBUTE4
3380 ,p_hist_dist_tab(j).ATTRIBUTE5
3381 ,p_hist_dist_tab(j).ATTRIBUTE6
3382 ,p_hist_dist_tab(j).ATTRIBUTE7
3383 ,p_hist_dist_tab(j).ATTRIBUTE8
3384 ,p_hist_dist_tab(j).ATTRIBUTE9
3385 ,p_hist_dist_tab(j).ATTRIBUTE10
3386 ,p_hist_dist_tab(j).ATTRIBUTE11
3387 ,p_hist_dist_tab(j).ATTRIBUTE12
3388 ,p_hist_dist_tab(j).ATTRIBUTE13
3389 ,p_hist_dist_tab(j).ATTRIBUTE14
3390 ,p_hist_dist_tab(j).ATTRIBUTE15);
3391 END IF;
3392 END LOOP;
3393
3394 IF l_insert_dist_hist = 'N' THEN
3395
3396 OPEN get_max_dist_change_id_cur(p_term_id);
3397 FETCH get_max_dist_change_id_cur INTO l_dist_change_id;
3398 CLOSE get_max_dist_change_id_cur;
3399
3400 pnp_debug_pkg.debug('create_hist_corr_upd : '||p_change_mode|| '- get_max_dist_change_id : '||l_dist_change_id);
3401 END IF;
3402 END IF;
3403
3404 pnp_debug_pkg.debug('create_hist_corr_upd : '||p_change_mode|| '- Inserting into Term Hist. table.');
3405
3406 INSERT INTO pn_payment_terms_history
3407 (TERM_HISTORY_ID
3408 ,LAST_UPDATE_DATE
3409 ,LAST_UPDATED_BY
3410 ,CREATION_DATE
3411 ,CREATED_BY
3412 ,LAST_UPDATE_LOGIN
3413 ,PAYMENT_TERM_ID
3414 ,DISTRIBUTION_CHANGE_ID
3415 ,PREV_TERM_HISTORY_ID
3416 ,ADJUSTMENT_TYPE_CODE
3417 ,TOTAL_ADJ_AMOUNT
3418 ,PAYMENT_PURPOSE_CODE
3419 ,PAYMENT_TERM_TYPE_CODE
3420 ,FREQUENCY_CODE
3421 ,LEASE_ID
3422 ,LEASE_CHANGE_ID
3423 ,START_DATE
3424 ,END_DATE
3425 ,VENDOR_ID
3426 ,VENDOR_SITE_ID
3427 ,TARGET_DATE
3428 ,ACTUAL_AMOUNT
3429 ,ESTIMATED_AMOUNT
3430 ,SET_OF_BOOKS_ID
3431 ,CURRENCY_CODE
3432 ,RATE
3433 ,ATTRIBUTE_CATEGORY
3434 ,ATTRIBUTE1
3435 ,ATTRIBUTE2
3436 ,ATTRIBUTE3
3437 ,ATTRIBUTE4
3438 ,ATTRIBUTE5
3439 ,ATTRIBUTE6
3440 ,ATTRIBUTE7
3441 ,ATTRIBUTE8
3442 ,ATTRIBUTE9
3443 ,ATTRIBUTE10
3444 ,ATTRIBUTE11
3445 ,ATTRIBUTE12
3446 ,ATTRIBUTE13
3447 ,ATTRIBUTE14
3448 ,ATTRIBUTE15
3449 ,CUSTOMER_ID
3450 ,CUSTOMER_SITE_USE_ID
3451 ,NORMALIZE
3452 ,LOCATION_ID
3453 ,SCHEDULE_DAY
3454 ,CUST_SHIP_SITE_ID
3455 ,AP_AR_TERM_ID
3456 ,CUST_TRX_TYPE_ID
3457 ,PROJECT_ID
3458 ,TASK_ID
3459 ,ORGANIZATION_ID
3460 ,EXPENDITURE_TYPE
3461 ,EXPENDITURE_ITEM_DATE
3462 ,TAX_GROUP_ID
3463 ,TAX_CODE_ID
3464 ,TAX_CLASSIFICATION_CODE
3465 ,TAX_INCLUDED
3466 ,DISTRIBUTION_SET_ID
3467 ,INV_RULE_ID
3468 ,ACCOUNT_RULE_ID
3469 ,SALESREP_ID
3470 ,APPROVED_BY
3471 ,STATUS
3472 ,INDEX_PERIOD_ID
3473 ,INDEX_TERM_INDICATOR
3474 ,PO_HEADER_ID
3475 ,CUST_PO_NUMBER
3476 ,RECEIPT_METHOD_ID
3477 ,LEGAL_ENTITY_ID
3478 ,PROJECT_ATTRIBUTE_CATEGORY
3479 ,PROJECT_ATTRIBUTE1
3480 ,PROJECT_ATTRIBUTE2
3481 ,PROJECT_ATTRIBUTE3
3482 ,PROJECT_ATTRIBUTE4
3483 ,PROJECT_ATTRIBUTE5
3484 ,PROJECT_ATTRIBUTE6
3485 ,PROJECT_ATTRIBUTE7
3486 ,PROJECT_ATTRIBUTE8
3487 ,PROJECT_ATTRIBUTE9
3488 ,PROJECT_ATTRIBUTE10
3489 ,PROJECT_ATTRIBUTE11
3490 ,PROJECT_ATTRIBUTE12
3491 ,PROJECT_ATTRIBUTE13
3492 ,PROJECT_ATTRIBUTE14
3493 ,PROJECT_ATTRIBUTE15
3494 ,VAR_RENT_INV_ID
3495 ,VAR_RENT_TYPE
3496 ,CHANGED_FLAG
3497 ,NORM_START_DATE
3498 ,TERM_TEMPLATE_ID
3499 ,EVENT_TYPE_CODE
3500 ,LEASE_STATUS
3501 ,NORM_END_DATE
3502 ,RECOVERABLE_FLAG
3503 ,PERIOD_BILLREC_ID
3504 ,AMOUNT_TYPE
3505 ,REC_AGR_LINE_ID
3506 ,GROUPING_RULE_ID
3507 ,AREA_TYPE_CODE
3508 ,AREA
3509 ,TERM_COMMENTS)
3510 VALUES
3511 (PN_PAYMENT_TERMS_HISTORY_S.NEXTVAL
3512 ,SYSDATE
3513 ,FND_GLOBAL.USER_ID
3514 ,SYSDATE
3515 ,FND_GLOBAL.USER_ID
3516 ,FND_GLOBAL.LOGIN_ID
3517 ,l_term_id
3518 ,l_dist_change_id
3519 ,l_prev_term_hist_id
3520 ,l_adj_type_code
3521 ,NULL -- TOTAL_ADJ_AMOUNT
3522 ,tlinfo.PAYMENT_PURPOSE_CODE
3523 ,tlinfo.PAYMENT_TERM_TYPE_CODE
3524 ,tlinfo.FREQUENCY_CODE
3525 ,tlinfo.LEASE_ID
3526 ,term_rec.LEASE_CHANGE_ID
3527 ,l_str_dt
3528 ,l_end_dt
3529 ,tlinfo.VENDOR_ID
3530 ,tlinfo.VENDOR_SITE_ID
3531 ,tlinfo.TARGET_DATE
3532 ,tlinfo.ACTUAL_AMOUNT
3533 ,tlinfo.ESTIMATED_AMOUNT
3534 ,tlinfo.SET_OF_BOOKS_ID
3535 ,tlinfo.CURRENCY_CODE
3536 ,tlinfo.RATE
3537 ,tlinfo.ATTRIBUTE_CATEGORY
3538 ,tlinfo.ATTRIBUTE1
3539 ,tlinfo.ATTRIBUTE2
3540 ,tlinfo.ATTRIBUTE3
3541 ,tlinfo.ATTRIBUTE4
3542 ,tlinfo.ATTRIBUTE5
3543 ,tlinfo.ATTRIBUTE6
3544 ,tlinfo.ATTRIBUTE7
3545 ,tlinfo.ATTRIBUTE8
3546 ,tlinfo.ATTRIBUTE9
3547 ,tlinfo.ATTRIBUTE10
3548 ,tlinfo.ATTRIBUTE11
3549 ,tlinfo.ATTRIBUTE12
3550 ,tlinfo.ATTRIBUTE13
3551 ,tlinfo.ATTRIBUTE14
3552 ,tlinfo.ATTRIBUTE15
3553 ,tlinfo.CUSTOMER_ID
3554 ,tlinfo.CUSTOMER_SITE_USE_ID
3555 ,tlinfo.NORMALIZE
3556 ,tlinfo.LOCATION_ID
3557 ,tlinfo.SCHEDULE_DAY
3558 ,tlinfo.CUST_SHIP_SITE_ID
3559 ,tlinfo.AP_AR_TERM_ID
3560 ,tlinfo.CUST_TRX_TYPE_ID
3561 ,tlinfo.PROJECT_ID
3562 ,tlinfo.TASK_ID
3563 ,tlinfo.ORGANIZATION_ID
3564 ,tlinfo.EXPENDITURE_TYPE
3565 ,tlinfo.EXPENDITURE_ITEM_DATE
3566 ,tlinfo.TAX_GROUP_ID
3567 ,tlinfo.TAX_CODE_ID
3568 ,tlinfo.TAX_CLASSIFICATION_CODE
3569 ,tlinfo.TAX_INCLUDED
3570 ,tlinfo.DISTRIBUTION_SET_ID
3571 ,tlinfo.INV_RULE_ID
3572 ,tlinfo.ACCOUNT_RULE_ID
3573 ,tlinfo.SALESREP_ID
3574 ,tlinfo.APPROVED_BY
3575 ,tlinfo.STATUS
3576 ,tlinfo.INDEX_PERIOD_ID
3577 ,tlinfo.INDEX_TERM_INDICATOR
3578 ,tlinfo.PO_HEADER_ID
3579 ,tlinfo.CUST_PO_NUMBER
3580 ,tlinfo.RECEIPT_METHOD_ID
3581 ,tlinfo.LEGAL_ENTITY_ID
3582 ,tlinfo.PROJECT_ATTRIBUTE_CATEGORY
3583 ,tlinfo.PROJECT_ATTRIBUTE1
3584 ,tlinfo.PROJECT_ATTRIBUTE2
3585 ,tlinfo.PROJECT_ATTRIBUTE3
3586 ,tlinfo.PROJECT_ATTRIBUTE4
3587 ,tlinfo.PROJECT_ATTRIBUTE5
3588 ,tlinfo.PROJECT_ATTRIBUTE6
3589 ,tlinfo.PROJECT_ATTRIBUTE7
3590 ,tlinfo.PROJECT_ATTRIBUTE8
3591 ,tlinfo.PROJECT_ATTRIBUTE9
3592 ,tlinfo.PROJECT_ATTRIBUTE10
3593 ,tlinfo.PROJECT_ATTRIBUTE11
3594 ,tlinfo.PROJECT_ATTRIBUTE12
3595 ,tlinfo.PROJECT_ATTRIBUTE13
3596 ,tlinfo.PROJECT_ATTRIBUTE14
3597 ,tlinfo.PROJECT_ATTRIBUTE15
3598 ,tlinfo.VAR_RENT_INV_ID
3599 ,tlinfo.VAR_RENT_TYPE
3600 ,tlinfo.CHANGED_FLAG
3601 ,tlinfo.NORM_START_DATE
3602 ,tlinfo.TERM_TEMPLATE_ID
3603 ,tlinfo.EVENT_TYPE_CODE
3604 ,tlinfo.LEASE_STATUS
3605 ,tlinfo.NORM_END_DATE
3606 ,tlinfo.RECOVERABLE_FLAG
3607 ,tlinfo.PERIOD_BILLREC_ID
3608 ,tlinfo.AMOUNT_TYPE
3609 ,tlinfo.REC_AGR_LINE_ID
3610 ,tlinfo.GROUPING_RULE_ID
3611 ,tlinfo.AREA_TYPE_CODE
3612 ,tlinfo.AREA
3613 ,l_term_comments);
3614 END LOOP;
3615 END IF;
3616 END IF;
3617
3618 pnp_debug_pkg.debug('PNT_PAYMENT_TERMS_PKG.create_hist_corr_upd (-)');
3619
3620 END create_hist_corr_upd;
3621
3622 --------------------------------------------------------------------------------
3623 -- FUNCTION : return_agreement_number
3624 -- INVOKED FROM : pn_payment_terms_v form view (PNPTERMV.sql)
3625 -- PURPOSE : returns the agreement number to which the payment term is associated.
3626 -- HISTORY :
3627 --
3628 -- 12-MAR-07 Prabhakar o Created.
3629 --------------------------------------------------------------------------------
3630 FUNCTION return_agreement_number( p_payment_term_id IN NUMBER) RETURN VARCHAR2 IS
3631
3632 l_agreement_number VARCHAR2(30) := NULL;
3633
3634 CURSOR c1 IS
3635 SELECT opex_agr_id,index_period_id,var_rent_inv_id,rec_agr_line_id
3636 FROM pn_payment_terms_all
3637 where payment_term_id = p_payment_term_id;
3638
3639 BEGIN
3640
3641 FOR rec1 IN c1 LOOP
3642 IF rec1.index_period_id IS NOT NULL THEN
3643
3644 SELECT pil.index_lease_number
3645 INTO l_agreement_number
3646 FROM pn_index_lease_periods_all pilp,
3647 pn_index_leases_all pil
3648 WHERE pilp.index_lease_id = pil.index_lease_id
3649 AND pilp.index_period_id = rec1.index_period_id;
3650
3651 ELSIF rec1.opex_agr_id IS NOT NULL THEN
3652
3653 SELECT agr_num
3654 INTO l_agreement_number
3655 FROM pn_opex_agreements_all
3656 WHERE agreement_id = rec1.opex_agr_id;
3657
3658 ELSIF rec1.var_rent_inv_id IS NOT NULL THEN
3659
3660 SELECT var.rent_num
3661 INTO l_agreement_number
3662 FROM pn_var_rents_all var,
3663 pn_var_rent_inv_all inv
3664 WHERE var.var_rent_id = inv.var_rent_id
3665 AND inv.var_rent_inv_id = rec1.var_rent_inv_id;
3666
3667 ELSIF rec1.rec_agr_line_id IS NOT NULL THEN
3668
3669 SELECT rec.rec_agreement_num
3670 INTO l_agreement_number
3671 FROM pn_rec_agreements_all rec,
3672 pn_rec_agr_lines_all rlines
3673 WHERE rlines.rec_agreement_id = rec.rec_agreement_id
3674 AND rlines.rec_agr_line_id = rec1.rec_agr_line_id;
3675
3676 ELSE
3677
3678 SELECT NULL
3679 INTO l_agreement_number
3680 FROM DUAL;
3681
3682 END IF;
3683 END LOOP;
3684
3685 RETURN l_agreement_number;
3686
3687 END return_agreement_number;
3688
3689 --------------------------------------------------------------------------------
3690 -- FUNCTION : CHECK_IF_OPEX_TERM
3691 -- INVOKED FROM :
3692 -- PURPOSE : Checks whether or not the term is created from Opex UI.
3693 -- HISTORY :
3694 --
3695 -- 09-MAY-07 Prabhakar o Created.
3696 --------------------------------------------------------------------------------
3697 FUNCTION CHECK_IF_OPEX_TERM ( p_payment_term_id IN NUMBER,
3698 p_opex_agr_id IN NUMBER )
3699 RETURN BOOLEAN IS
3700
3701 l_dummy NUMBER := 0;
3702
3703 CURSOR dummy IS
3704 SELECT 1 flag
3705 FROM pn_opex_est_payments_all
3706 WHERE (payment_term_id = p_payment_term_id or catch_up_term_id = p_payment_term_id)
3707 AND agreement_id = p_opex_agr_id;
3708
3709 BEGIN
3710
3711 FOR rec IN dummy LOOP
3712 l_dummy := rec.flag;
3713 END LOOP;
3714
3715 IF l_dummy = 1 THEN return(TRUE);
3716 ELSE return(FALSE);
3717 END IF;
3718
3719 END CHECK_IF_OPEX_TERM;
3720
3721 --------------------------------------------------------------------------------
3722 -- FUNCTION : get_source_module_type
3723 -- INVOKED FROM : pn_payment_terms_v form view (PNPTERMV.sql)
3724 -- PURPOSE : returns the source of the term from which it is created.
3725 -- HISTORY :
3726 --
3727 -- 09-MAY-07 Prabhakar o Created.
3728 --------------------------------------------------------------------------------
3729 FUNCTION get_source_module_type( p_payment_term_id IN NUMBER) RETURN VARCHAR2 IS
3730
3731 l_source_module VARCHAR2(30) := NULL;
3732
3733 CURSOR c1 IS
3734 SELECT opex_agr_id,
3735 opex_recon_id,
3736 index_period_id,
3737 var_rent_inv_id,
3738 rec_agr_line_id,
3739 source_code
3740 FROM pn_payment_terms_all
3741 where payment_term_id = p_payment_term_id;
3742
3743 BEGIN
3744
3745 FOR rec1 IN c1 LOOP
3746
3747 IF (rec1.opex_agr_id IS NOT NULL AND
3748 CHECK_IF_OPEX_TERM (p_payment_term_id, rec1.opex_agr_id) ) OR
3749 rec1.opex_recon_id IS NOT NULL THEN
3750 l_source_module := 'OPEX';
3751 ELSIF rec1.index_period_id IS NOT NULL THEN
3752 l_source_module := 'RI';
3753 ELSIF rec1.var_rent_inv_id IS NOT NULL THEN
3754 l_source_module := 'VR';
3755 ELSIF rec1.rec_agr_line_id IS NOT NULL THEN
3756 l_source_module := 'REC';
3757 ELSE
3758 l_source_module := rec1.source_code;
3759 END IF;
3760
3761 END LOOP;
3762
3763 RETURN l_source_module;
3764
3765 END get_source_module_type;
3766
3767 PROCEDURE MODIFY_ROW ( x_payment_term_id IN NUMBER
3768 ,x_var_rent_inv_id IN NUMBER
3769 ,x_changed_flag IN VARCHAR2)
3770 IS
3771 BEGIN
3772
3773 pnp_debug_pkg.debug ('PNT_PAYMENT_TERMS_PKG.MODIFY_ROW (+)');
3774
3775 UPDATE pn_payment_terms_all
3776 SET changed_flag = x_changed_flag
3777 WHERE payment_term_id = NVL(x_payment_term_id ,payment_term_id )
3778 AND var_rent_inv_id = x_var_rent_inv_id;
3779
3780 pnp_debug_pkg.debug ('PNT_PAYMENT_TERMS_PKG.MODIFY_ROW (-)');
3781
3782 EXCEPTION
3783 WHEN others THEN
3784 NULL;
3785 END MODIFY_ROW;
3786
3787
3788
3789 END PNT_PAYMENT_TERMS_PKG;