1 PACKAGE BODY pn_term_templates_pkg AS
2 -- $Header: PNTTRMTB.pls 120.4 2005/12/01 08:35:18 appldev ship $
3
4
5 /*============================================================================+
6 | Copyright (c) 2001 Oracle Corporation
7 | Redwood Shores, California, USA
8 | All rights reserved.
9 | DESCRIPTION
10 |
11 | These procedures consist are used a table handlers for the PN_TERM_TEMPLATES table.
12 | They include:
13 | INSERT_ROW - insert a row into PN_TERM_TEMPLATES.
14 | DELETE_ROW - deletes a row from PN_TERM_TEMPLATES.
15 | UPDATE_ROW - updates a row from PN_TERM_TEMPLATES.
16 | LOCKS_ROW - will check if a row has been modified since being queried by form.
17 |
18 |
19 | HISTORY
20 | 08-MAY-01 jbreyes o Created
21 | 13-DEC-01 Mrinal Misra o Added dbdrv command.
22 | 15-JAN-02 Mrinal Misra o In dbdrv command changed phase=pls to phase=plb.
23 | Added checkfile.Ref. Bug# 2184724.
24 | 05-JUN-02 Daniel Thota o Added org_id as a parameter to insert_row
25 | as part of Multi-Org
26 | 20-JUL-02 Mrinal Misra o Changed lock_row procedure for currency_code made
27 | null column in base table.
28 | 05-AUG-02 Mrinal Misra o Added x_location_id parameter for location_id
29 | column in table.
30 | 18-MAY-04 ftanudja o Add logic for term template type. 3539408.
31 | 15-JUL-2005 ftanudja o R12: add tax_classification_code. 4495054.
32 | 25-Jul-05 sdmahesh o overloaded delete_row proc to take PK as parameter
33 +===========================================================================*/
34
35 -------------------------------------------------------------------------------
36 -- PROCDURE : INSERT_ROW
37 -- INVOKED FROM : insert_row procedure
38 -- PURPOSE : inserts the row
39 -- HISTORY :
40 -- 13-JUL-05 sdmahesh o Bug 4284035 - Replaced pn_term_templates with _ALL
41 -- table.
42 -------------------------------------------------------------------------------
43 PROCEDURE insert_row (
44 x_rowid IN OUT NOCOPY VARCHAR2
45 ,x_term_template_id IN OUT NOCOPY NUMBER
46 ,x_name IN VARCHAR2
47 ,x_set_of_books_id IN NUMBER
48 ,x_currency_code IN VARCHAR2
49 ,x_last_update_date IN DATE
50 ,x_last_updated_by IN NUMBER
51 ,x_creation_date IN DATE
52 ,x_created_by IN NUMBER
53 ,x_normalize IN VARCHAR2
54 ,x_schedule_day IN NUMBER
55 ,x_payment_purpose_code IN VARCHAR2
56 ,x_payment_term_type_code IN VARCHAR2
57 ,x_accrual_account_id IN NUMBER
58 ,x_project_id IN NUMBER
59 ,x_task_id IN NUMBER
60 ,x_organization_id IN NUMBER
61 ,x_expenditure_type IN VARCHAR2
62 ,x_expenditure_item_date IN DATE
63 ,x_vendor_id IN NUMBER
64 ,x_vendor_site_id IN NUMBER
65 ,x_customer_id IN NUMBER
66 ,x_customer_site_use_id IN NUMBER
67 ,x_cust_ship_site_id IN NUMBER
68 ,x_ap_ar_term_id IN NUMBER
69 ,x_cust_trx_type_id IN NUMBER
70 ,x_tax_group_id IN NUMBER
71 ,x_tax_code_id IN NUMBER
72 ,x_tax_classification_code IN VARCHAR2
73 ,x_distribution_set_id IN NUMBER
74 ,x_inv_rule_id IN NUMBER
75 ,x_account_rule_id IN NUMBER
76 ,x_salesrep_id IN NUMBER
77 ,x_tax_included IN VARCHAR2
78 ,x_po_header_id IN NUMBER
79 ,x_cust_po_number IN VARCHAR2
80 ,x_receipt_method_id IN NUMBER
81 ,x_location_id IN NUMBER
82 ,x_active IN VARCHAR2
83 ,x_description IN VARCHAR2
84 ,x_term_template_type IN VARCHAR2
85 ,x_last_update_login IN NUMBER
86 ,x_attribute_category IN VARCHAR2
87 ,x_attribute1 IN VARCHAR2
88 ,x_attribute2 IN VARCHAR2
89 ,x_attribute3 IN VARCHAR2
90 ,x_attribute4 IN VARCHAR2
91 ,x_attribute5 IN VARCHAR2
92 ,x_attribute6 IN VARCHAR2
93 ,x_attribute7 IN VARCHAR2
94 ,x_attribute8 IN VARCHAR2
95 ,x_attribute9 IN VARCHAR2
96 ,x_attribute10 IN VARCHAR2
97 ,x_attribute11 IN VARCHAR2
98 ,x_attribute12 IN VARCHAR2
99 ,x_attribute13 IN VARCHAR2
100 ,x_attribute14 IN VARCHAR2
101 ,x_attribute15 IN VARCHAR2
102 ,x_org_id NUMBER
103 )
104 IS
105 CURSOR c IS
106 SELECT ROWID
107 FROM pn_term_templates_all
108 WHERE term_template_id = x_term_template_id;
109
110 l_return_status VARCHAR2 (30) := NULL;
111 l_rowid VARCHAR2 (18) := NULL;
112 BEGIN
113
114 --PNP_DEBUG_PKG.debug (' PN_TERM_TEMPLATES_PKG.insert_row (+)');
115 -- If no TERM_TEMPLATE_ID is provided, get one from sequence
116
117 pn_term_templates_pkg.check_unq_term_template (
118 l_return_status
119 ,x_term_template_id
120 ,x_name
121 ,x_org_id
122 );
123
124 IF (l_return_status IS NOT NULL) THEN
125 app_exception.raise_exception;
126 END IF;
127
128
129 -- IF (l_return_status IS NOT NULL) THEN APP_EXCEPTION.Raise_Exception; END IF;
130 INSERT INTO pn_term_templates_all
131 (
132 term_template_id
133 ,name
134 ,set_of_books_id
135 ,currency_code
136 ,last_update_date
137 ,last_updated_by
138 ,creation_date
139 ,created_by
140 ,normalize
141 ,schedule_day
142 ,payment_purpose_code
143 ,payment_term_type_code
144 ,accrual_account_id
145 ,project_id
146 ,task_id
147 ,organization_id
148 ,expenditure_type
149 ,expenditure_item_date
150 ,vendor_id
151 ,vendor_site_id
152 ,customer_id
153 ,customer_site_use_id
154 ,cust_ship_site_id
155 ,ap_ar_term_id
156 ,cust_trx_type_id
157 ,tax_group_id
158 ,tax_code_id
159 ,tax_classification_code
160 ,distribution_set_id
161 ,inv_rule_id
162 ,account_rule_id
163 ,salesrep_id
164 ,tax_included
165 ,po_header_id
166 ,cust_po_number
167 ,receipt_method_id
168 ,location_id
169 ,active
170 ,description
171 ,term_template_type
172 ,last_update_login
173 ,attribute_category
174 ,attribute1
175 ,attribute2
176 ,attribute3
177 ,attribute4
178 ,attribute5
179 ,attribute6
180 ,attribute7
181 ,attribute8
182 ,attribute9
183 ,attribute10
184 ,attribute11
185 ,attribute12
186 ,attribute13
187 ,attribute14
188 ,attribute15
189 ,org_id
190 )
191 VALUES
192 (
193 NVL(x_term_template_id,pn_term_templates_s.NEXTVAL)
194 ,x_name
195 ,x_set_of_books_id
196 ,x_currency_code
197 ,x_last_update_date
198 ,x_last_updated_by
199 ,x_creation_date
200 ,x_created_by
201 ,x_normalize
202 ,x_schedule_day
203 ,x_payment_purpose_code
204 ,x_payment_term_type_code
205 ,x_accrual_account_id
206 ,x_project_id
207 ,x_task_id
208 ,x_organization_id
209 ,x_expenditure_type
210 ,x_expenditure_item_date
211 ,x_vendor_id
212 ,x_vendor_site_id
213 ,x_customer_id
214 ,x_customer_site_use_id
215 ,x_cust_ship_site_id
216 ,x_ap_ar_term_id
217 ,x_cust_trx_type_id
218 ,x_tax_group_id
219 ,x_tax_code_id
220 ,x_tax_classification_code
221 ,x_distribution_set_id
222 ,x_inv_rule_id
223 ,x_account_rule_id
224 ,x_salesrep_id
225 ,x_tax_included
226 ,x_po_header_id
227 ,x_cust_po_number
228 ,x_receipt_method_id
229 ,x_location_id
230 ,x_active
231 ,x_description
232 ,x_term_template_type
233 ,x_last_update_login
234 ,x_attribute_category
235 ,x_attribute1
236 ,x_attribute2
237 ,x_attribute3
238 ,x_attribute4
239 ,x_attribute5
240 ,x_attribute6
241 ,x_attribute7
242 ,x_attribute8
243 ,x_attribute9
244 ,x_attribute10
245 ,x_attribute11
246 ,x_attribute12
247 ,x_attribute13
248 ,x_attribute14
249 ,x_attribute15
250 ,x_org_id
251 )
252 RETURNING term_template_id INTO x_term_template_id;
253
254 -- Check if a valid record was created.
255 OPEN c;
256 FETCH c INTO x_rowid;
257
258 IF (c%NOTFOUND) THEN
259 CLOSE c;
260 RAISE NO_DATA_FOUND;
261 END IF;
262
263 CLOSE c;
264
265 --PNP_DEBUG_PKG.debug (' PN_TERM_TEMPLATES_PKG.insert_row (-)');
266 END insert_row;
267
268
269 -------------------------------------------------------------------------------
270 -- PROCDURE : update_row
271 -- INVOKED FROM : update_row procedure
272 -- PURPOSE : upadtes the row
273 -- HISTORY :
274 -- 13-JUL-05 sdmahesh o Bug 4284035 - Replaced pn_term_templates with _ALL
275 -- table.Also changed the where clause for update stmt.
276 -------------------------------------------------------------------------------
277 PROCEDURE update_row (
278 x_rowid IN VARCHAR2
279 ,x_term_template_id IN NUMBER
280 ,x_name IN VARCHAR2
281 ,x_set_of_books_id IN NUMBER
282 ,x_currency_code IN VARCHAR2
283 ,x_last_update_date IN DATE
284 ,x_last_updated_by IN NUMBER
285 ,x_normalize IN VARCHAR2
286 ,x_schedule_day IN NUMBER
287 ,x_payment_purpose_code IN VARCHAR2
288 ,x_payment_term_type_code IN VARCHAR2
289 ,x_accrual_account_id IN NUMBER
290 ,x_project_id IN NUMBER
291 ,x_task_id IN NUMBER
292 ,x_organization_id IN NUMBER
293 ,x_expenditure_type IN VARCHAR2
294 ,x_expenditure_item_date IN DATE
295 ,x_vendor_id IN NUMBER
296 ,x_vendor_site_id IN NUMBER
297 ,x_customer_id IN NUMBER
298 ,x_customer_site_use_id IN NUMBER
299 ,x_cust_ship_site_id IN NUMBER
300 ,x_ap_ar_term_id IN NUMBER
301 ,x_cust_trx_type_id IN NUMBER
302 ,x_tax_group_id IN NUMBER
303 ,x_tax_code_id IN NUMBER
304 ,x_tax_classification_code IN VARCHAR2
305 ,x_distribution_set_id IN NUMBER
306 ,x_inv_rule_id IN NUMBER
307 ,x_account_rule_id IN NUMBER
308 ,x_salesrep_id IN NUMBER
309 ,x_tax_included IN VARCHAR2
310 ,x_po_header_id IN NUMBER
311 ,x_cust_po_number IN VARCHAR2
312 ,x_receipt_method_id IN NUMBER
313 ,x_location_id IN NUMBER
314 ,x_active IN VARCHAR2
315 ,x_description IN VARCHAR2
316 ,x_term_template_type IN VARCHAR2
317 ,x_last_update_login IN NUMBER
318 ,x_attribute_category IN VARCHAR2
319 ,x_attribute1 IN VARCHAR2
320 ,x_attribute2 IN VARCHAR2
321 ,x_attribute3 IN VARCHAR2
322 ,x_attribute4 IN VARCHAR2
323 ,x_attribute5 IN VARCHAR2
324 ,x_attribute6 IN VARCHAR2
325 ,x_attribute7 IN VARCHAR2
326 ,x_attribute8 IN VARCHAR2
327 ,x_attribute9 IN VARCHAR2
328 ,x_attribute10 IN VARCHAR2
329 ,x_attribute11 IN VARCHAR2
330 ,x_attribute12 IN VARCHAR2
331 ,x_attribute13 IN VARCHAR2
332 ,x_attribute14 IN VARCHAR2
333 ,x_attribute15 IN VARCHAR2
334 )
335 IS
336 l_return_status VARCHAR2 (30) := NULL;
337 l_org_id NUMBER;
338 BEGIN
339
340 --PNP_DEBUG_PKG.debug (' PN_TERM_TEMPLATES_PKG.update_row (+)');
341
342 SELECT org_id
343 INTO l_org_id
344 FROM pn_term_templates_all
345 WHERE term_template_id = x_term_template_id;
346
347 pn_term_templates_pkg.check_unq_term_template (
348 l_return_status
349 ,x_term_template_id
350 ,x_name
351 ,l_org_id
352 );
353
354 IF (l_return_status IS NOT NULL) THEN
355 app_exception.raise_exception;
356 END IF;
357
358 UPDATE pn_term_templates_all
359 SET name = x_name
360 ,set_of_books_id = x_set_of_books_id
361 ,currency_code = x_currency_code
362 ,last_update_date = x_last_update_date
363 ,last_updated_by = x_last_updated_by
364 ,normalize = x_normalize
365 ,schedule_day = x_schedule_day
366 ,payment_purpose_code = x_payment_purpose_code
367 ,payment_term_type_code = x_payment_term_type_code
368 ,accrual_account_id = x_accrual_account_id
369 ,project_id = x_project_id
370 ,task_id = x_task_id
371 ,organization_id = x_organization_id
372 ,expenditure_type = x_expenditure_type
373 ,expenditure_item_date = x_expenditure_item_date
374 ,vendor_id = x_vendor_id
375 ,vendor_site_id = x_vendor_site_id
376 ,customer_id = x_customer_id
377 ,customer_site_use_id = x_customer_site_use_id
378 ,cust_ship_site_id = x_cust_ship_site_id
379 ,ap_ar_term_id = x_ap_ar_term_id
380 ,cust_trx_type_id = x_cust_trx_type_id
381 ,tax_group_id = x_tax_group_id
382 ,tax_code_id = x_tax_code_id
383 ,tax_classification_code = x_tax_classification_code
384 ,distribution_set_id = x_distribution_set_id
385 ,inv_rule_id = x_inv_rule_id
386 ,account_rule_id = x_account_rule_id
387 ,salesrep_id = x_salesrep_id
388 ,tax_included = x_tax_included
389 ,po_header_id = x_po_header_id
390 ,cust_po_number = x_cust_po_number
391 ,receipt_method_id = x_receipt_method_id
392 ,location_id = x_location_id
393 ,active = x_active
394 ,description = x_description
395 ,term_template_type = x_term_template_type
396 ,last_update_login = x_last_update_login
397 ,attribute_category = x_attribute_category
398 ,attribute1 = x_attribute1
399 ,attribute2 = x_attribute2
400 ,attribute3 = x_attribute3
401 ,attribute4 = x_attribute4
402 ,attribute5 = x_attribute5
403 ,attribute6 = x_attribute6
404 ,attribute7 = x_attribute7
405 ,attribute8 = x_attribute8
406 ,attribute9 = x_attribute9
407 ,attribute10 = x_attribute10
408 ,attribute11 = x_attribute11
409 ,attribute12 = x_attribute12
410 ,attribute13 = x_attribute13
411 ,attribute14 = x_attribute14
412 ,attribute15 = x_attribute15
413 WHERE term_template_id = x_term_template_id;
414
415 IF (SQL%NOTFOUND) THEN
416 RAISE NO_DATA_FOUND;
417 END IF;
418
419 --PNP_DEBUG_PKG.debug (' PN_TERM_TEMPLATES_PKG.update_row (-)');
420 END update_row;
421
422
423 -------------------------------------------------------------------------------
424 -- PROCDURE : lock_row
425 -- INVOKED FROM : lock_row procedure
426 -- PURPOSE : locks the row
427 -- HISTORY :
428 -- 13-JUL-05 sdmahesh o Bug 4284035 - Replaced pn_term_templates with _ALL
429 -- table.Also changed the where clause in cursor
430 -------------------------------------------------------------------------------
431 PROCEDURE lock_row (
432 x_rowid IN VARCHAR2
433 ,x_term_template_id IN NUMBER
434 ,x_name IN VARCHAR2
435 ,x_set_of_books_id IN NUMBER
436 ,x_currency_code IN VARCHAR2
437 ,x_normalize IN VARCHAR2
438 ,x_schedule_day IN NUMBER
439 ,x_payment_purpose_code IN VARCHAR2
440 ,x_payment_term_type_code IN VARCHAR2
441 ,x_accrual_account_id IN NUMBER
442 ,x_project_id IN NUMBER
443 ,x_task_id IN NUMBER
444 ,x_organization_id IN NUMBER
445 ,x_expenditure_type IN VARCHAR2
446 ,x_expenditure_item_date IN DATE
447 ,x_vendor_id IN NUMBER
448 ,x_vendor_site_id IN NUMBER
449 ,x_customer_id IN NUMBER
450 ,x_customer_site_use_id IN NUMBER
451 ,x_cust_ship_site_id IN NUMBER
452 ,x_ap_ar_term_id IN NUMBER
453 ,x_cust_trx_type_id IN NUMBER
454 ,x_tax_group_id IN NUMBER
455 ,x_tax_code_id IN NUMBER
456 ,x_tax_classification_code IN VARCHAR2
457 ,x_distribution_set_id IN NUMBER
458 ,x_inv_rule_id IN NUMBER
459 ,x_account_rule_id IN NUMBER
460 ,x_salesrep_id IN NUMBER
461 ,x_tax_included IN VARCHAR2
462 ,x_po_header_id IN NUMBER
463 ,x_cust_po_number IN VARCHAR2
464 ,x_receipt_method_id IN NUMBER
465 ,x_location_id IN NUMBER
466 ,x_active IN VARCHAR2
467 ,x_description IN VARCHAR2
468 ,x_attribute_category IN VARCHAR2
469 ,x_attribute1 IN VARCHAR2
470 ,x_attribute2 IN VARCHAR2
471 ,x_attribute3 IN VARCHAR2
472 ,x_attribute4 IN VARCHAR2
473 ,x_attribute5 IN VARCHAR2
474 ,x_attribute6 IN VARCHAR2
475 ,x_attribute7 IN VARCHAR2
476 ,x_attribute8 IN VARCHAR2
477 ,x_attribute9 IN VARCHAR2
478 ,x_attribute10 IN VARCHAR2
479 ,x_attribute11 IN VARCHAR2
480 ,x_attribute12 IN VARCHAR2
481 ,x_attribute13 IN VARCHAR2
482 ,x_attribute14 IN VARCHAR2
483 ,x_attribute15 IN VARCHAR2
484 ) IS
485 CURSOR c1 IS
486 SELECT *
487 FROM pn_term_templates_all
488 WHERE term_template_id = x_term_template_id
489 FOR UPDATE OF term_template_id NOWAIT;
490
491 tlinfo c1%ROWTYPE;
492
493 BEGIN
494
495 PNP_DEBUG_PKG.debug (' PN_TERM_TEMPLATES_PKG.lock_row (+)');
496
497 OPEN c1;
498 FETCH c1 INTO tlinfo;
499
500 IF (c1%NOTFOUND) THEN
501 CLOSE c1;
502 RETURN;
503 END IF;
504
505 CLOSE c1;
506
507 IF NOT (tlinfo.term_template_id = x_term_template_id ) THEN
508 pn_var_rent_pkg.lock_row_exception('TERM_TEMPLATE_ID',tlinfo.term_template_id);
509 END IF;
510
511 IF NOT (tlinfo.name = x_name ) THEN
512 pn_var_rent_pkg.lock_row_exception('NAME',tlinfo.name);
513 END IF;
514
515 IF NOT (tlinfo.set_of_books_id = x_set_of_books_id ) THEN
516 pn_var_rent_pkg.lock_row_exception('SET_OF_BOOKS_ID',tlinfo.set_of_books_id);
517 END IF;
518
519 IF NOT ((tlinfo.currency_code = x_currency_code ) OR
520 (tlinfo.currency_code = null) AND (x_currency_code = null))
521 THEN
522 pn_var_rent_pkg.lock_row_exception('CURRENCY_CODE',tlinfo.currency_code);
523 END IF;
524
525 IF NOT ((tlinfo.normalize = x_normalize ) OR
526 (tlinfo.normalize = null) AND (x_normalize = null))
527 THEN
528 pn_var_rent_pkg.lock_row_exception('NORMALIZE',tlinfo.normalize);
529 END IF;
530
531 IF NOT ((tlinfo.schedule_day = x_schedule_day ) OR
532 (tlinfo.schedule_day = null) AND (x_schedule_day = null))
533 THEN
534 pn_var_rent_pkg.lock_row_exception('SCHEDULE_DAY',tlinfo.schedule_day);
535 END IF;
536
537 IF NOT ((tlinfo.payment_purpose_code = x_payment_purpose_code ) OR
538 (tlinfo.payment_purpose_code = null) AND (x_payment_purpose_code = null))
539 THEN
540 pn_var_rent_pkg.lock_row_exception('PAYMENT_PURPOSE_CODE',tlinfo.payment_purpose_code);
541 END IF;
542
543 IF NOT ((tlinfo.payment_term_type_code = x_payment_term_type_code ) OR
544 (tlinfo.payment_term_type_code = null) AND (x_payment_term_type_code = null))
545 THEN
546 pn_var_rent_pkg.lock_row_exception('PAYMENT_TERM_TYPE_CODE',tlinfo.payment_term_type_code);
547 END IF;
548
549 IF NOT ((tlinfo.accrual_account_id = x_accrual_account_id ) OR
550 (tlinfo.accrual_account_id = null) AND (x_accrual_account_id = null))
551 THEN
552 pn_var_rent_pkg.lock_row_exception('ACCRUAL_ACCOUNT_ID',tlinfo.accrual_account_id);
553 END IF;
554
555 IF NOT ((tlinfo.project_id = x_project_id ) OR
556 (tlinfo.project_id = null) AND (x_project_id = null))
557 THEN
558 pn_var_rent_pkg.lock_row_exception('PROJECT_ID',tlinfo.project_id);
559 END IF;
560
561 IF NOT ((tlinfo.task_id = x_task_id ) OR
562 (tlinfo.task_id = null) AND (x_task_id = null))
563 THEN
564 pn_var_rent_pkg.lock_row_exception('TASK_ID',tlinfo.task_id);
565 END IF;
566
567 IF NOT ((tlinfo.organization_id = x_organization_id ) OR
568 (tlinfo.organization_id = null) AND (x_organization_id = null))
569 THEN
570 pn_var_rent_pkg.lock_row_exception('ORGANIZATION_ID',tlinfo.organization_id);
571 END IF;
572
573 IF NOT ((tlinfo.expenditure_type = x_expenditure_type ) OR
574 (tlinfo.expenditure_type = null) AND (x_expenditure_type = null))
575 THEN
576 pn_var_rent_pkg.lock_row_exception('EXPENDITURE_TYPE',tlinfo.expenditure_type);
577 END IF;
578
579 IF NOT ((tlinfo.expenditure_item_date = x_expenditure_item_date ) OR
580 (tlinfo.expenditure_item_date = null) AND (x_expenditure_item_date = null))
581 THEN
582 pn_var_rent_pkg.lock_row_exception('EXPENDITURE_ITEM_DATE',tlinfo.expenditure_item_date);
583 END IF;
584
585 IF NOT ((tlinfo.vendor_id = x_vendor_id ) OR
586 (tlinfo.vendor_id = null) AND (x_vendor_id = null))
587 THEN
588 pn_var_rent_pkg.lock_row_exception('VENDOR_ID',tlinfo.vendor_id);
589 END IF;
590
591 IF NOT ((tlinfo.vendor_site_id = x_vendor_site_id ) OR
592 (tlinfo.vendor_site_id = null) AND (x_vendor_site_id = null))
593 THEN
594 pn_var_rent_pkg.lock_row_exception('VENDOR_SITE_ID',tlinfo.vendor_site_id);
595 END IF;
596
597 IF NOT ((tlinfo.customer_id = x_customer_id ) OR
598 (tlinfo.customer_id = null) AND (x_customer_id = null))
599 THEN
600 pn_var_rent_pkg.lock_row_exception('CUSTOMER_ID',tlinfo.customer_id);
601 END IF;
602
603 IF NOT ((tlinfo.customer_site_use_id = x_customer_site_use_id ) OR
604 (tlinfo.customer_site_use_id = null) AND (x_customer_site_use_id = null))
605 THEN
606 pn_var_rent_pkg.lock_row_exception('CUSTOMER_SITE_USE_ID',tlinfo.customer_site_use_id);
607 END IF;
608
609 IF NOT ((tlinfo.cust_ship_site_id = x_cust_ship_site_id ) OR
610 (tlinfo.cust_ship_site_id = null) AND (x_cust_ship_site_id = null))
611 THEN
612 pn_var_rent_pkg.lock_row_exception('CUST_SHIP_SITE_ID',tlinfo.cust_ship_site_id);
613 END IF;
614
615 IF NOT ((tlinfo.ap_ar_term_id = x_ap_ar_term_id ) OR
616 (tlinfo.ap_ar_term_id = null) AND (x_ap_ar_term_id = null))
617 THEN
618 pn_var_rent_pkg.lock_row_exception('AP_AR_TERM_ID',tlinfo.ap_ar_term_id);
619 END IF;
620
621 IF NOT ((tlinfo.cust_trx_type_id = x_cust_trx_type_id ) OR
622 (tlinfo.cust_trx_type_id = null) AND (x_cust_trx_type_id = null))
623 THEN
624 pn_var_rent_pkg.lock_row_exception('CUST_TRX_TYPE_ID',tlinfo.cust_trx_type_id);
625 END IF;
626
627 IF pn_r12_util_pkg.is_r12 AND
628 NOT ((tlinfo.tax_classification_code = x_tax_classification_code ) OR
629 (tlinfo.tax_classification_code = null) AND (x_tax_classification_code = null))
630 THEN
631 pn_var_rent_pkg.lock_row_exception('TAX_CLASSIFICATION_CODE',tlinfo.tax_classification_code);
632 END IF;
633
634 IF NOT pn_r12_util_pkg.is_r12 AND
635 NOT ((tlinfo.tax_group_id = x_tax_group_id ) OR
636 (tlinfo.tax_group_id = null) AND (x_tax_group_id = null))
637 THEN
638 pn_var_rent_pkg.lock_row_exception('TAX_GROUP_ID',tlinfo.tax_group_id);
639 END IF;
640
641 IF NOT pn_r12_util_pkg.is_r12 AND
642 NOT ((tlinfo.tax_code_id = x_tax_code_id ) OR
643 (tlinfo.tax_code_id = null) AND (x_tax_code_id = null))
644 THEN
645 pn_var_rent_pkg.lock_row_exception('TAX_CODE_ID',tlinfo.tax_code_id);
646 END IF;
647
648 IF NOT ((tlinfo.distribution_set_id = x_distribution_set_id ) OR
649 (tlinfo.distribution_set_id = null) AND (x_distribution_set_id = null))
650 THEN
651 pn_var_rent_pkg.lock_row_exception('DISTRIBUTION_SET_ID',tlinfo.distribution_set_id);
652 END IF;
653
654 IF NOT ((tlinfo.inv_rule_id = x_inv_rule_id ) OR
655 (tlinfo.inv_rule_id = null) AND (x_inv_rule_id = null))
656 THEN
657 pn_var_rent_pkg.lock_row_exception('INV_RULE_ID',tlinfo.inv_rule_id);
658 END IF;
659
660 IF NOT ((tlinfo.account_rule_id = x_account_rule_id ) OR
661 (tlinfo.account_rule_id = null) AND (x_account_rule_id = null))
662 THEN
663 pn_var_rent_pkg.lock_row_exception('ACCOUNT_RULE_ID',tlinfo.account_rule_id);
664 END IF;
665
666 IF NOT ((tlinfo.salesrep_id = x_salesrep_id ) OR
667 (tlinfo.salesrep_id = null) AND (x_salesrep_id = null))
668 THEN
669 pn_var_rent_pkg.lock_row_exception('SALESREP_ID',tlinfo.salesrep_id);
670 END IF;
671
672 IF NOT ((tlinfo.tax_included = x_tax_included ) OR
673 (tlinfo.tax_included = null) AND (x_tax_included = null))
674 THEN
675 pn_var_rent_pkg.lock_row_exception('TAX_INCLUDED',tlinfo.tax_included);
676 END IF;
677
678 IF NOT ((tlinfo.po_header_id = x_po_header_id ) OR
679 (tlinfo.po_header_id = null) AND (x_po_header_id = null))
680 THEN
681 pn_var_rent_pkg.lock_row_exception('PO_HEADER_ID',tlinfo.po_header_id);
682 END IF;
683
684 IF NOT ((tlinfo.cust_po_number = x_cust_po_number ) OR
685 (tlinfo.cust_po_number = null) AND (x_cust_po_number = null))
686 THEN
687 pn_var_rent_pkg.lock_row_exception('CUST_PO_NUMBER',tlinfo.cust_po_number);
688 END IF;
689
690 IF NOT ((tlinfo.receipt_method_id = x_receipt_method_id ) OR
691 (tlinfo.receipt_method_id = null) AND (x_receipt_method_id = null))
692 THEN
693 pn_var_rent_pkg.lock_row_exception('RECEIPT_METHOD_ID',tlinfo.receipt_method_id);
694 END IF;
695
696 IF NOT ((tlinfo.active = x_active ) OR
697 (tlinfo.active = null) AND (x_active = null))
698 THEN
699 pn_var_rent_pkg.lock_row_exception('ACTIVE',tlinfo.active);
700 END IF;
701
702 IF NOT ((tlinfo.description = x_description ) OR
703 (tlinfo.description = null) AND (x_description = null))
704 THEN
705 pn_var_rent_pkg.lock_row_exception('DESCRIPTION',tlinfo.description);
706 END IF;
707
708 IF NOT ((tlinfo.location_id = x_location_id ) OR
709 (tlinfo.location_id = null) AND (x_location_id = null))
710 THEN
711 pn_var_rent_pkg.lock_row_exception('LOCATION_ID',tlinfo.location_id);
712 END IF;
713
714 IF NOT ((tlinfo.attribute_category = x_attribute_category ) OR
715 (tlinfo.attribute_category = null) AND (x_attribute_category = null))
716 THEN
717 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE_CATEGORY',tlinfo.attribute_category);
718 END IF;
719
720 IF NOT ((tlinfo.attribute1 = x_attribute1 ) OR
721 (tlinfo.attribute1 = null) AND (x_attribute1 = null))
722 THEN
723 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE1',tlinfo.attribute1);
724 END IF;
725
726 IF NOT ((tlinfo.attribute2 = x_attribute2 ) OR
727 (tlinfo.attribute2 = null) AND (x_attribute2 = null))
728 THEN
729 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE2',tlinfo.attribute2);
730 END IF;
731
732 IF NOT ((tlinfo.attribute3 = x_attribute3 ) OR
733 (tlinfo.attribute3 = null) AND (x_attribute3 = null))
734 THEN
735 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE3',tlinfo.attribute3);
736 END IF;
737
738 IF NOT ((tlinfo.attribute4 = x_attribute4 ) OR
739 (tlinfo.attribute4 = null) AND (x_attribute4 = null))
740 THEN
741 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE4',tlinfo.attribute4);
742 END IF;
743
744 IF NOT ((tlinfo.attribute5 = x_attribute5 ) OR
745 (tlinfo.attribute5 = null) AND (x_attribute5 = null))
746 THEN
747 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE5',tlinfo.attribute5);
748 END IF;
749
750 IF NOT ((tlinfo.attribute6 = x_attribute6 ) OR
751 (tlinfo.attribute6 = null) AND (x_attribute6 = null))
752 THEN
753 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE6',tlinfo.attribute6);
754 END IF;
755
756 IF NOT ((tlinfo.attribute7 = x_attribute7 ) OR
757 (tlinfo.attribute7 = null) AND (x_attribute7 = null))
758 THEN
759 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE7',tlinfo.attribute7);
760 END IF;
761
762 IF NOT ((tlinfo.attribute8 = x_attribute8 ) OR
763 (tlinfo.attribute8 = null) AND (x_attribute8 = null))
764 THEN
765 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE8',tlinfo.attribute8);
766 END IF;
767
768 IF NOT ((tlinfo.attribute9 = x_attribute9 ) OR
769 (tlinfo.attribute9 = null) AND (x_attribute9 = null))
770 THEN
771 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE9',tlinfo.attribute9);
772 END IF;
773
774 IF NOT ((tlinfo.attribute10 = x_attribute10 ) OR
775 (tlinfo.attribute10 = null) AND (x_attribute10 = null))
776 THEN
777 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE10',tlinfo.attribute10);
778 END IF;
779
780 IF NOT ((tlinfo.attribute11 = x_attribute11 ) OR
781 (tlinfo.attribute11 = null) AND (x_attribute11 = null))
782 THEN
783 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE11',tlinfo.attribute11);
784 END IF;
785
786 IF NOT ((tlinfo.attribute12 = x_attribute12 ) OR
787 (tlinfo.attribute12 = null) AND (x_attribute12 = null))
788 THEN
789 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE12',tlinfo.attribute12);
790 END IF;
791
792 IF NOT ((tlinfo.attribute13 = x_attribute13 ) OR
793 (tlinfo.attribute13 = null) AND (x_attribute13 = null))
794 THEN
795 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE13',tlinfo.attribute13);
796 END IF;
797
798 IF NOT ((tlinfo.attribute14 = x_attribute14 ) OR
799 (tlinfo.attribute14 = null) AND (x_attribute14 = null))
800 THEN
801 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE14',tlinfo.attribute14);
802 END IF;
803
804 IF NOT ((tlinfo.attribute15 = x_attribute15 ) OR
805 (tlinfo.attribute15 = null) AND (x_attribute15 = null))
806 THEN
807 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE15',tlinfo.attribute15);
808 END IF;
809
810 PNP_DEBUG_PKG.debug (' PN_TERM_TEMPLATES_PKG.lock_row (-)');
811
812 END lock_row;
813
814 -------------------------------------------------------------------------------
815 -- PROCDURE : delete_row
816 -- INVOKED FROM : delete_row procedure
817 -- PURPOSE : deletes the row
818 -- HISTORY :
819 -- 21-JUN-05 sdmahesh o Bug 4284035 - Replaced pn_term_templates with _ALL table.
820 -------------------------------------------------------------------------------
821 PROCEDURE delete_row
822 (
823 x_rowid IN VARCHAR2
824 )
825 IS
826 BEGIN
827
828 DELETE FROM pn_term_templates_all
829 WHERE ROWID = x_rowid;
830
831 IF (SQL%NOTFOUND) THEN
832 RAISE NO_DATA_FOUND;
833 END IF;
834
835 END delete_row;
836
837 -------------------------------------------------------------------------------
838 -- PROCDURE : delete_row
839 -- INVOKED FROM : delete_row procedure
840 -- PURPOSE : deletes the row
841 -- NOTE : overloaded this procedure to take PK as In parameter
842 -- HISTORY :
843 -- 21-JUN-05 piagrawa o Bug 4284035 - Created
844 -------------------------------------------------------------------------------
845 PROCEDURE delete_row
846 (
847 x_term_template_id IN NUMBER
848 )
849 IS
850 BEGIN
851
852 DELETE FROM pn_term_templates_all
853 WHERE term_template_id = x_term_template_id;
854
855 IF (SQL%NOTFOUND) THEN
856 RAISE NO_DATA_FOUND;
857 END IF;
858 END delete_row;
859
860 -------------------------------------------------------------------------------
861 -- PROCDURE : check_unq_term_template
862 -- INVOKED FROM : insert_row procedure
863 -- PURPOSE : checks unique term template
864 -- HISTORY :
865 -- 13-JUL-05 sdmahesh o Bug 4284035 - Replaced pn_term_templates with _ALL
866 -- table.
867 -------------------------------------------------------------------------------
868 PROCEDURE check_unq_term_template
869 (
870 x_return_status IN OUT NOCOPY VARCHAR2
871 ,x_term_template_id IN NUMBER
872 ,x_name IN VARCHAR2
873 ,x_org_id IN NUMBER
874 )
875 IS
876 l_dummy NUMBER;
877 BEGIN
878 SELECT 1
879 INTO l_dummy
880 FROM DUAL
881 WHERE NOT EXISTS ( SELECT 1
882 FROM pn_term_templates_all
883 WHERE name = x_name
884 AND ((x_term_template_id IS NULL)
885 OR (term_template_id <> x_term_template_id)
886 AND org_id = x_org_id
887 ));
888 EXCEPTION
889 WHEN NO_DATA_FOUND THEN
890 fnd_message.set_name ('PN', 'PN_DUP_TERM_TEMPLATE');
891
892 -- fnd_message.set_token ('NAME', x_name);
893 x_return_status := 'E';
894 END check_unq_term_template;
895
896 END pn_term_templates_pkg;
[Home] [Help]