1 PACKAGE BODY PN_MASS_APPR_PKG AS
2 -- $Header: PNMASAPB.pls 120.3 2006/12/08 07:08:50 acprakas ship $
3
4 /* ========================== NOTE TO PROGRAMMER ==========================
5 1. Use the functions in pnp_debug_pkg for logging as follows:
6 debug - Display text message if in debug mode
7 log - To put in debug messages in the log file
8 put_log_msg - To put in messages in both the out and the log file
9
10 2. Retcode values
11 0 - success
12 1 - warning
13 2 - error
14 ========================== NOTE TO PROGRAMMER ========================== */
15
16 /* exceptions global to this package alone */
17 RATE_NOT_DEFINED EXCEPTION;
18 CURR_CONV_FAILED EXCEPTION;
19 INPUT_DATE_MISSING EXCEPTION;
20
21 --------------------------------------------------------------------------------
22 -- NAME : update_accounted_amount
23 -- DESCRIPTION : Updates the items in a schedule with the accounted amount.
24 -- Called before APPROVing schedules.
25 -- Rules followed for getting the accounted amount are:
26 -- if the conversion type in the item is USER then
27 -- accounted amount = actual amount * rate
28 -- (all amount values gotten from/for the item)
29 -- else
30 -- accounted amount is gotten by call to
31 -- pnp_util_func.export_curr_amount which in turn calls
32 -- gl_currency_api.convert_amount catching exceptions
33 -- gl_currency_api.no_rate, gl_currency_api.invalid_currency
34 --
35 -- Note that this procedure is similar to
36 -- PNT_PAYMENT_SCHEDULES_PKG.update_accounted_amount - but does
37 -- not have as many validations. Exception handling is done
38 -- differently too
39 --
40 -- PURPOSE : To update the accounted amount in the items in a schedule.
41 -- INVOKED FROM : pn_mass_appr_pkg.update_accounted_amount
42 -- ARGUMENTS :
43 -- p_schedule_id - IN - schedule ID the items in which need update
44 -- p_functional_curr - IN - functional curreny
45 -- p_conversion_type - IN - currency conversion type
46 -- p_item_currency - OUT - returns the currency of the item in case of error
47 -- REFERENCE : PN_COMMON.debug()
48 -- PNP_UTIL_FUNC.export_curr_amount
49 -- HISTORY :
50 --
51 -- 30-SEP-04 kkhegde o Created
52 -- 11-OCT-05 pikhar o ensure l_accounted_date does not have a timestamp.
53 -- 28-NOV-05 pikhar o replaced pn_payment_items with _all table
54 -- 08-DEC-06 acprakas o Bug5389144. Added code to modify Record History columns of pn_payment_items
55 -- and pn_payment_schedules with correct values.
56 --------------------------------------------------------------------------------
57 PROCEDURE update_accounted_amount (p_schedule_id IN NUMBER,
58 p_functional_curr IN VARCHAR2,
59 p_conversion_type IN VARCHAR2,
60 p_item_currency OUT NOCOPY VARCHAR2)
61 IS
62
63 l_accounted_date DATE;
64 l_accounted_amt NUMBER;
65 l_accounted_amt_norm NUMBER;
66
67 l_norm_payment_item_id NUMBER;
68 l_norm_accounted_amount NUMBER;
69 l_norm_actual_amount NUMBER;
70 l_norm_curr_code VARCHAR2(15);
71
72 l_exists_norm BOOLEAN;
73
74 /* cursors */
75 CURSOR payment_cursor IS
76 SELECT payment_item_id
77 ,payment_term_id
78 ,accounted_amount
79 ,actual_amount
80 ,currency_code
81 ,due_date
82 ,rate
83 FROM pn_payment_items_all
84 WHERE payment_schedule_id = p_schedule_id
85 AND payment_item_type_lookup_code = 'CASH';
86
87 CURSOR norm_cursor( p_term_id IN NUMBER
88 ,p_item_id IN NUMBER) IS
89 SELECT pi.payment_item_id
90 ,pi.accounted_amount
91 ,pi.actual_amount
92 ,pi.currency_code
93 FROM pn_payment_items_all pi
94 ,pn_payment_items_all pi1
95 WHERE pi.payment_schedule_id = p_schedule_id
96 AND pi.payment_term_id = p_term_id
97 AND pi.payment_item_type_lookup_code = 'NORMALIZED'
98 AND pi1.payment_schedule_id = pi.payment_schedule_id
99 AND pi1.payment_term_id = pi.payment_term_id
100 AND pi1.payment_item_type_lookup_code = 'CASH'
101 AND pi1.payment_item_id = p_item_id ;
102
103 BEGIN
104
105 PNP_DEBUG_PKG.debug('pn_mass_appr_pkg.update_accounted_amount (+)');
106
107 FOR payment_item_rec IN payment_cursor LOOP
108
109 /* initialize */
110 l_norm_payment_item_id := NULL;
111 l_norm_accounted_amount := NULL;
112 l_norm_actual_amount := NULL;
113 l_norm_curr_code := NULL;
114
115 p_item_currency := payment_item_rec.currency_code;
116
117 /* get data for normalized item corresponding to the cash item */
118 FOR norm_item_rec IN norm_cursor( payment_item_rec.payment_term_id
119 ,payment_item_rec.payment_item_id ) LOOP
120
121 l_norm_payment_item_id := norm_item_rec.payment_item_id;
122 l_norm_accounted_amount := norm_item_rec.accounted_amount;
123 l_norm_actual_amount := norm_item_rec.actual_amount;
124 l_norm_curr_code := norm_item_rec.currency_code;
125
126 l_exists_norm := TRUE;
127
128 END LOOP;
129
130 IF payment_item_rec.due_date > SYSDATE THEN
131 l_accounted_date := TRUNC(SYSDATE);
132 ELSE
133 l_accounted_date := TRUNC(payment_item_rec.due_date);
134 END IF;
135
136 IF UPPER(p_conversion_type) = 'USER' THEN
137
138 IF payment_item_rec.rate IS NULL THEN
139
140 /* if conversion type is USER and rate NULL then
141 raise RATE_NOT_DEFINED */
142 RAISE RATE_NOT_DEFINED;
143
144 ELSE
145
146 l_accounted_amt
147 := NVL(payment_item_rec.actual_amount,0) * NVL(payment_item_rec.rate,0);
148
149 IF l_exists_norm THEN
150 l_accounted_amt_norm
151 := NVL(l_norm_actual_amount,0) * NVL(PAYMENT_item_rec.rate,0);
152 END IF;
153
154 END IF;
155
156 ELSE
157
158 BEGIN
159
160 l_accounted_amt := pnp_util_func.export_curr_amount(
161 currency_code => payment_item_rec.currency_code,
162 export_currency_code => p_functional_curr,
163 export_date => l_accounted_date,
164 conversion_type => p_conversion_type,
165 actual_amount => NVL(payment_item_rec.actual_amount,0),
166 p_called_from => 'NOTPNTAUPMT'
167 );
168
169 IF l_exists_norm THEN
170 l_accounted_amt_norm := pnp_util_func.export_curr_amount(
171 currency_code => payment_item_rec.currency_code,
172 export_currency_code => p_functional_curr,
173 export_date => l_accounted_date,
174 conversion_type => p_conversion_type,
175 actual_amount => NVL(l_norm_actual_amount,0),
176 p_called_from => 'NOTPNTAUPMT'
177 );
178 END IF;
179
180 EXCEPTION
181 WHEN OTHERS THEN
182 /* if call to export_curr_amount raised exception then
183 tell the user that the conversion failed */
184 RAISE CURR_CONV_FAILED;
185
186 END;
187
188 END IF;
189
190 IF (NVL(payment_item_rec.accounted_amount,0) <> NVL(l_accounted_amt,0)) THEN
191
192 UPDATE pn_payment_items
193 SET accounted_amount = l_accounted_amt,
194 accounted_date = l_accounted_date,
195 last_update_date = SYSDATE, --Bug#5389144
196 last_updated_by = NVL(fnd_profile.value('USER_ID'),0), --Bug#5389144
197 last_update_login= NVL(fnd_profile.value('LOGIN_ID'),0) --Bug#5389144
198 WHERE payment_item_id = payment_item_rec.payment_item_id;
199
200 END IF;
201
202 IF l_exists_norm THEN
203
204 IF (NVL(l_norm_accounted_amount,0) <> NVL(l_accounted_amt_norm,0)) THEN
205
206 UPDATE pn_payment_items
207 SET accounted_amount = l_accounted_amt_norm,
208 accounted_date = l_accounted_date,
209 RATE = payment_item_rec.RATE,
210 CURRENCY_CODE = payment_item_rec.currency_code,
211 last_update_date = SYSDATE, --Bug#5389144
212 last_updated_by = NVL(fnd_profile.value('USER_ID'),0), --Bug#5389144
213 last_update_login= NVL(fnd_profile.value('LOGIN_ID'),0) --Bug#5389144
214 WHERE payment_item_id = l_norm_payment_item_id;
215
216 END IF;
217
218 END IF;
219
220 END LOOP;
221
222 PNP_DEBUG_PKG.debug('pn_mass_appr_pkg.update_accounted_amount (-)');
223
224 EXCEPTION
225 WHEN OTHERS THEN
226 RAISE;
227
228 END update_accounted_amount;
229
230 --------------------------------------------------------------------------------
231 -- NAME : approve
232 -- DESCRIPTION : Approves the DRAFT schedules that meet the give filter
233 -- criteria.
234 -- PURPOSE : Approves the draft schedules
235 -- INVOKED FROM : pn_mass_appr_pkg.pn_mass_app
236 -- ARGUMENTS : Same as that of appr_pkg.pn_mass_app() sans the dummy params
237 -- REFERENCE : PN_COMMON.debug()
238 -- HISTORY :
239 --
240 -- 27-SEP-04 Kiran o Created
241 -- 11-JAN-05 Anand o Removed on_hold = 'Y' condition in the cursors.
242 -- Bug # 4109792
243 -- 28-Nov-05 pikhar o Passed org_id in pn_mo_chace_utils.get_profile_value
244 -- 28-Nov-05 pikhar o replaced tables with _ALL tables
245 -- 01-DEC-05 pikhar o passed org_id in pnp_util_func.get_default_gl_period
246 -- and pnp_util_func.check_conversion_type
247 -- 08-DEC-06 acprakas o Bug5389144. Added code to modify Record History columns of pn_payment_items
248 -- and pn_payment_schedules with correct values.
249 --------------------------------------------------------------------------------
250 PROCEDURE approve( errbuf OUT NOCOPY VARCHAR2
251 ,retcode OUT NOCOPY VARCHAR2
252 ,p_schedule_from_date IN DATE
253 ,p_schedule_to_date IN DATE
254 ,p_trx_from_date IN DATE
255 ,p_trx_to_date IN DATE
256 ,p_lease_class_code IN VARCHAR2
257 ,p_set_of_books IN VARCHAR2
258 ,p_payment_period IN VARCHAR2
259 ,p_billing_period IN VARCHAR2
260 ,p_lease_from_number IN VARCHAR2
261 ,p_lease_to_number IN VARCHAR2
262 ,p_location_from_code IN VARCHAR2
263 ,p_location_to_code IN VARCHAR2
264 ,p_responsible_user IN NUMBER) IS
265
266 /* main cursors */
267 -------------------------------------------------------------------------------
268 -- Cursor used when Location Code To, Location Code From are passed.
269 -------------------------------------------------------------------------------
270 CURSOR mass_appr_loc_cur IS
271 SELECT DISTINCT
272 pps.payment_schedule_id s_payment_schedule_id,
273 pps.schedule_date s_schedule_date,
274 pps.period_name s_period_name,
275 pl.lease_class_code s_lease_class_code,
276 pl.lease_id s_lease_id,
277 pl.lease_num s_lease_number,
278 pl.name s_lease_name
279 FROM pn_payment_schedules_all pps,
280 pn_leases pl,
281 pn_lease_details_all pld,
282 pn_tenancies_all pt,
283 pn_locations_all ploc
284 WHERE pps.schedule_date BETWEEN NVL(p_schedule_from_date, pps.schedule_date)
285 AND NVL(p_schedule_to_date, pps.schedule_date)
286 AND pps.payment_status_lookup_code = 'DRAFT'
287 AND pl.lease_id = pps.lease_id
288 AND pl.lease_class_code = NVL(p_lease_class_code,pl.lease_class_code)
289 AND pl.lease_num >= NVL(p_lease_from_number, pl.lease_num)
290 AND pl.lease_num <= NVL(p_lease_to_number, pl.lease_num)
291 AND pld.lease_id = pps.lease_id
292 AND pld.responsible_user = NVL(p_responsible_user, pld.responsible_user)
293 AND pt.lease_id = pps.lease_id
294 AND ploc.location_id = pt.location_id
295 AND ploc.location_code >= NVL(p_location_from_code, ploc.location_code)
296 AND ploc.location_code <= NVL(p_location_to_code, ploc.location_code)
297 AND EXISTS
298 (SELECT NULL
299 FROM pn_payment_items_all item
300 WHERE item.payment_schedule_id = pps.payment_schedule_id
301 AND item.due_date BETWEEN NVL(p_trx_from_date, item.due_date)
302 AND NVL(p_trx_to_date, item.due_date)
303 )
304 ORDER BY pl.lease_id, pps.schedule_date;
305
306 -------------------------------------------------------------------------------
307 -- Cursor used when Location Code To, Location Code From are NOT passed.
308 -------------------------------------------------------------------------------
309 CURSOR mass_appr_cur IS
310 SELECT DISTINCT
311 pps.payment_schedule_id s_payment_schedule_id,
312 pps.schedule_date s_schedule_date,
313 pps.period_name s_period_name,
314 pl.lease_class_code s_lease_class_code,
315 pl.lease_id s_lease_id,
316 pl.lease_num s_lease_number,
317 pl.name s_lease_name
318 FROM pn_payment_schedules_all pps,
319 pn_leases pl,
320 pn_lease_details_all pld
321 WHERE pps.schedule_date BETWEEN NVL(p_schedule_from_date, pps.schedule_date)
322 AND NVL(p_schedule_to_date, pps.schedule_date)
323 AND pps.payment_status_lookup_code = 'DRAFT'
324 AND pl.lease_id = pps.lease_id
325 AND pl.lease_class_code = NVL(p_lease_class_code,pl.lease_class_code)
326 AND pl.lease_num >= NVL(p_lease_from_number, pl.lease_num)
327 AND pl.lease_num <= NVL(p_lease_to_number, pl.lease_num)
328 AND pld.lease_id = pps.lease_id
329 AND pld.responsible_user = NVL(p_responsible_user, pld.responsible_user)
330 AND EXISTS
331 (SELECT NULL
332 FROM pn_payment_items_all item
333 WHERE item.payment_schedule_id = pps.payment_schedule_id
334 AND item.due_date BETWEEN NVL(p_trx_from_date, item.due_date)
335 AND NVL(p_trx_to_date, item.due_date)
336 )
337 ORDER BY pl.lease_id, pps.schedule_date;
338
339 /* validation cursors */
340
341 -------------------------------------------------------------------------------
342 -- validate that the billing term has all the required info
343 -- Business Rules:
344 -- In a Billing Term
345 -- - customer and bill to site must be defined
346 -- - ship to site must be defined or shipping address rule must be defined
347 -- - Term Type must be defined
348 -- - Customer Transaction type must be defined
349 -------------------------------------------------------------------------------
350 CURSOR check_bill_term ( p_schedule_id IN NUMBER
351 ,p_ship_address_rule IN VARCHAR) IS
352 SELECT 'Y'
353 FROM DUAL
354 WHERE EXISTS (SELECT NULL
355 FROM pn_payment_terms_all ppt,
356 pn_payment_items_all ppi
357 WHERE ppi.payment_term_id = ppt.payment_term_id
358 AND ppi.payment_schedule_id = p_schedule_id
359 AND (ppt.customer_id IS NULL
360 OR ppt.customer_site_use_id IS NULL
361 OR (ppt.cust_ship_site_id IS NULL AND
362 p_ship_address_rule <> 'None'
363 )
364 OR ppt.ap_ar_term_id IS NULL
365 OR ppt.cust_trx_type_id IS NULL
366 )
367 );
368
369 -------------------------------------------------------------------------------
370 -- validate that the payment term has all the required info
371 -- Business Rules:
372 -- In a Payment Term vendor and vendor site must be defined
373 -------------------------------------------------------------------------------
374 CURSOR check_pay_term (p_schedule_id IN NUMBER) IS
375 SELECT 'Y'
376 FROM dual
377 WHERE EXISTS (SELECT NULL
378 FROM pn_payment_terms_all ppt,
379 pn_payment_items_all ppi
380 WHERE ppi.payment_term_id = ppt.payment_term_id
381 AND ppi.payment_schedule_id = p_schedule_id
382 AND (ppt.vendor_id IS NULL OR
383 ppt.vendor_site_id Is NULL
384 )
385 );
386
387 -------------------------------------------------------------------------------
388 -- validate that the actual amount in the items is not null
389 -- Business Rules:
390 -- When a schedule is to be approved, all the items belonging to the schedule
391 -- should have a not null actual amount.
392 -------------------------------------------------------------------------------
393 CURSOR check_act_amt (p_schedule_id IN NUMBER) IS
394 SELECT 'Y'
395 FROM dual
396 WHERE EXISTS(SELECT NULL
397 FROM pn_payment_items_all ppi
398 WHERE ppi.payment_schedule_id = p_schedule_id
399 AND ppi.actual_amount IS NULL);
400
401 -------------------------------------------------------------------------------
402 -- get the functional currency
403 -------------------------------------------------------------------------------
404 CURSOR get_functional_currency IS
405 SELECT currency_code
406 FROM gl_sets_of_books
407 WHERE set_of_books_id
408 = TO_NUMBER(pn_mo_cache_utils.get_profile_value('PN_SET_OF_BOOKS_ID'
409 ,pn_mo_cache_utils.get_current_org_id));
410
411 -------------------------------------------------------------------------------
412 -- get the shipping address rule
413 -- this will be used in check_bill_term
414 -------------------------------------------------------------------------------
415 CURSOR csr_ship_address_rule IS
416 SELECT NVL(ship_address_rule,'None') AS ship_address_rule
417 FROM RA_BATCH_SOURCES
418 WHERE batch_source_id = 24;
419
420 /* --- VARIABLES --- */
421 TYPE currency_tbl_type
422 IS TABLE OF PN_CURRENCIES.currency_code%TYPE
423 INDEX BY BINARY_INTEGER;
424
425 bad_currency_table currency_tbl_type;
426
427 /* counters */
428 l_sch_app NUMBER;
429 l_sch_rej NUMBER;
430 l_sch_tot NUMBER;
431
432 /* variables to hold validation values */
433 l_ship_cust_rule RA_BATCH_SOURCES.ship_address_rule%TYPE;
434 l_func_curr PN_CURRENCIES.currency_code%TYPE;
435 l_item_curr PN_CURRENCIES.currency_code%TYPE;
436 l_curr_conv_type PN_CURRENCIES.conversion_type%TYPE;
437
438 /* flags */
439 l_exist_item_null_amt BOOLEAN;
440 l_exist_bad_bill_term BOOLEAN;
441 l_exist_bad_pay_term BOOLEAN;
442 l_rate_err BOOLEAN;
443 l_curr_exists BOOLEAN;
444 l_sched_failed BOOLEAN;
445
446 /* variables to store the values selected in the main cursor */
447 l_payment_item_id PN_PAYMENT_ITEMS_ALL.payment_item_id%TYPE;
448 l_payment_schedule_id PN_PAYMENT_SCHEDULES_ALL.payment_schedule_id%TYPE;
449 l_payment_schedule_date PN_PAYMENT_SCHEDULES_ALL.schedule_date%TYPE;
450 l_lease_class_code PN_LEASES_ALL.lease_class_code%TYPE;
451 l_period_name GL_PERIOD_STATUSES.period_name%TYPE;
452 l_lease_id PN_LEASES_ALL.lease_id%TYPE;
453 l_lease_number PN_LEASES_ALL.lease_num%TYPE;
454 l_lease_name PN_LEASES_ALL.name%TYPE;
455
456 /* variables to handle updation and related processes */
457 l_application_id NUMBER;
458 l_applicable_period GL_PERIOD_STATUSES.period_name%TYPE;
459
460 BEGIN
461
462 PNP_DEBUG_PKG.debug ('PN_MASS_APPR_PKG.approve (+)');
463
464 /* init the counters */
465 l_sch_app := 0;
466 l_sch_rej := 0;
467 l_sch_tot := 0;
468
469 /* Get the Ship address rule from ra_batch_sources */
470 FOR rec IN csr_ship_address_rule LOOP
471 l_ship_cust_rule := rec.ship_address_rule;
472 END LOOP;
473
474 /* Get functional currency and its converstion type */
475 FOR curr_code_rec IN get_functional_currency LOOP
476 l_func_curr := curr_code_rec.currency_code;
477 END LOOP;
478
479 IF l_func_curr IS NULL THEN
480 /* need to tell the user functional currency is not defined */
481 fnd_message.set_name('PN', 'PN_FUNC_CURR_NOT_FOUND');
482 pnp_debug_pkg.put_log_msg
483 ('+------------------------------------------------------------------------------+');
484 pnp_debug_pkg.put_log_msg(fnd_message.get);
485 pnp_debug_pkg.put_log_msg
486 ('+------------------------------------------------------------------------------+');
487
488 RAISE NO_DATA_FOUND;
489 END IF;
490
491 l_curr_conv_type
492 := pnp_util_func.check_conversion_type(l_func_curr,pn_mo_cache_utils.get_current_org_id);
493
494 /* Initialize pl/sql problem currency table */
495 bad_currency_table.DELETE;
496
497 /* open the appropriate cursor based on whether
498 location code has been passed as a parameter or not
499 */
500 IF p_location_from_code IS NOT NULL OR
501 p_location_to_code IS NOT NULL THEN
502
503 OPEN mass_appr_loc_cur;
504
505 ELSE
506
507 OPEN mass_appr_cur;
508
509 END IF;
510
511 /* loop through the appropriate cursor
512 approve the schedules
513 */
514 LOOP
515
516 /* fetch from the appropriate cursor */
517 IF mass_appr_loc_cur%ISOPEN THEN
518
519 FETCH mass_appr_loc_cur
520 INTO l_payment_schedule_id
521 ,l_payment_schedule_date
522 ,l_period_name
523 ,l_lease_class_code
524 ,l_lease_id
525 ,l_lease_number
526 ,l_lease_name;
527
528 EXIT WHEN mass_appr_loc_cur%NOTFOUND;
529
530 ELSIF mass_appr_cur%ISOPEN then
531
532 FETCH mass_appr_cur
533 INTO l_payment_schedule_id
534 ,l_payment_schedule_date
535 ,l_period_name
536 ,l_lease_class_code
537 ,l_lease_id
538 ,l_lease_number
539 ,l_lease_name;
540
541 EXIT WHEN mass_appr_cur%NOTFOUND;
542
543 END IF;
544
545 /* if we are here, we have a schedule with us to process */
546
547 /* Increment the schedules total */
548 l_sch_tot := l_sch_tot + 1;
549
550 /* init validation result holders/flags */
551 l_exist_item_null_amt := FALSE;
552 l_exist_bad_bill_term := FALSE;
553 l_exist_bad_pay_term := FALSE;
554 l_sched_failed := FALSE;
555
556 /* verify that all the items in this schedule have an actual amount */
557 FOR rec IN check_act_amt(l_payment_schedule_id) LOOP
558 l_exist_item_null_amt := TRUE;
559 END LOOP;
560
561 IF l_exist_item_null_amt THEN
562
563 fnd_message.set_name('PN','PN_SCHED_REJECT_MSG');
564 fnd_message.set_token('L_LEASE_NUMBER',l_lease_number);
565 fnd_message.set_token('L_LEASE_NAME',l_lease_name);
566
567 pnp_debug_pkg.put_log_msg
568 ('+------------------------------------------------------------------------------+');
569 pnp_debug_pkg.put_log_msg(fnd_message.get);
570 pnp_debug_pkg.put_log_msg
571 ('+------------------------------------------------------------------------------+');
572
573 l_sched_failed := TRUE;
574
575 ELSE
576
577 /* verify if there exists a bad term that
578 is contributing to this schedule
579 */
580 IF l_lease_class_code IN ('THIRD_PARTY','SUB_LEASE') THEN
581
582 /* set the value of application ID, applicable period
583 - we will use this later to get period name */
584 l_application_id := 222;
585 l_applicable_period := p_billing_period;
586
587 /* check if we have a bad term */
588 FOR rec IN check_bill_term(l_payment_schedule_id,l_ship_cust_rule) LOOP
589 l_exist_bad_bill_term := TRUE;
590 END LOOP;
591
592 ELSIF l_lease_class_code = 'DIRECT' THEN
593
594 /* set the value of application ID, applicable period
595 - we will use this later to get period name */
596 l_application_id := 200;
597 l_applicable_period := p_payment_period;
598
599 /* check if we have a bad term */
600 FOR rec IN check_pay_term(l_payment_schedule_id) LOOP
601 l_exist_bad_pay_term := TRUE;
602 END LOOP;
603
604 END IF;
605
606 IF l_exist_bad_bill_term THEN
607
608 /* Set the schedule failed flag */
609 l_sched_failed := TRUE;
610
611 fnd_message.set_name('PN','PN_SCHED_BILL_REJ_MSG');
612 fnd_message.set_token('L_LEASE_NUMBER',l_lease_number);
613 fnd_message.set_token('L_LEASE_NAME',l_lease_name);
614
615 pnp_debug_pkg.put_log_msg
616 ('+------------------------------------------------------------------------------+');
617 pnp_debug_pkg.put_log_msg(fnd_message.get);
618 pnp_debug_pkg.put_log_msg
619 ('+------------------------------------------------------------------------------+');
620
621 ELSIF l_exist_bad_pay_term THEN
622
623 /* Set the schedule failed flag */
624 l_sched_failed := TRUE;
625
626 fnd_message.set_name('PN','PN_SCHED_PAY_REJ_MSG');
627 fnd_message.set_token('L_LEASE_NUMBER',l_lease_number);
628 fnd_message.set_token('L_LEASE_NAME',l_lease_name);
629
630 pnp_debug_pkg.put_log_msg
631 ('+------------------------------------------------------------------------------+');
632 pnp_debug_pkg.put_log_msg(fnd_message.get);
633 pnp_debug_pkg.put_log_msg
634 ('+------------------------------------------------------------------------------+');
635
636 ELSE
637
638 /* get the period name */
639 l_period_name := NVL(l_applicable_period,
640 NVL(l_period_name,
641 PNP_UTIL_FUNC.get_default_gl_period
642 (l_payment_schedule_date,
643 l_application_id,
644 pn_mo_cache_utils.get_current_org_id)
645 )
646 );
647
648 /* throw error of we cant get the period name */
649 IF l_period_name IS NULL THEN
650
651 /* Set the schedule failed flag */
652 l_sched_failed := TRUE;
653
654 fnd_message.set_name('PN','PN_GL_PRD_MSG');
655 fnd_message.set_token('L_LEASE_NUMBER',l_lease_number);
656 fnd_message.set_token('L_LEASE_NAME',l_lease_name);
657
658 pnp_debug_pkg.put_log_msg
659 ('+------------------------------------------------------------------------------+');
660 pnp_debug_pkg.put_log_msg(fnd_message.get);
661 pnp_debug_pkg.put_log_msg
662 ('+------------------------------------------------------------------------------+');
663
664 ELSE
665
666 /* if we reached here, looks like we are good to go! */
667
668 SAVEPOINT beforeupdate;
669
670 l_rate_err := FALSE;
671
672 BEGIN
673 update_accounted_amount
674 ( p_schedule_id => l_payment_schedule_id
675 ,p_functional_curr => l_func_curr
676 ,p_conversion_type => l_curr_conv_type
677 ,p_item_currency => l_item_curr);
678
679 EXCEPTION
680
681 WHEN RATE_NOT_DEFINED THEN
682 fnd_message.set_name('PN','PN_RATE_NOT_FOUND');
683 fnd_message.set_token('CURRENCY', l_func_curr);
684 pnp_debug_pkg.put_log_msg
685 ('+------------------------------------------------------------------------------+');
686 pnp_debug_pkg.put_log_msg(fnd_message.get);
687 pnp_debug_pkg.put_log_msg
688 ('+------------------------------------------------------------------------------+');
689
690 l_rate_err := TRUE;
691 ROLLBACK TO beforeupdate;
692
693 WHEN CURR_CONV_FAILED THEN
694 fnd_message.set_name('PN','PN_CONV_TYPE_NOT_FOUND');
695 pnp_debug_pkg.put_log_msg
696 ('+------------------------------------------------------------------------------+');
697 pnp_debug_pkg.put_log_msg(fnd_message.get);
698 pnp_debug_pkg.put_log_msg
699 ('+------------------------------------------------------------------------------+');
700
701 l_rate_err := TRUE;
702 ROLLBACK TO beforeupdate;
703
704 WHEN OTHERS THEN
705 RAISE;
706
707 END;
708
709 IF l_rate_err THEN
710
711 /** Record problem currency in plsql table **/
712 l_curr_exists := FALSE;
713
714 FOR i IN 0 .. (bad_currency_table.COUNT - 1) LOOP
715 IF (bad_currency_table(i) = l_item_curr) THEN
716 l_curr_exists := TRUE;
717 EXIT;
718 END IF;
719 END LOOP;
720
721 IF NOT l_curr_exists THEN
722 bad_currency_table(bad_currency_table.COUNT) := l_item_curr;
723 END IF;
724
725 /* Set the schedule failed flag */
726 l_sched_failed := TRUE;
727
728 ELSE
729
730 /* Update the export flags in the items */
731 UPDATE PN_PAYMENT_ITEMS
732 SET export_to_ap_flag = DECODE(l_lease_class_code,
733 'DIRECT','Y',
734 NULL),
735 export_to_ar_flag = DECODE(l_lease_class_code,
736 'THIRD_PARTY','Y',
737 'SUB_LEASE' ,'Y',
738 NULL),
739 last_update_date = SYSDATE, --Bug#5389144
740 last_updated_by = NVL(fnd_profile.value('USER_ID'),0), --Bug#5389144
741 last_update_login= NVL(fnd_profile.value('LOGIN_ID'),0) --Bug#5389144
742 WHERE payment_item_type_lookup_code = 'CASH'
743 AND payment_schedule_id = l_payment_schedule_id;
744
745 /* Approve the schedule */
746 UPDATE PN_PAYMENT_SCHEDULES
747 SET payment_status_lookup_code = 'APPROVED',
748 approved_by_user_id = fnd_profile.value('USER_ID'),
749 approval_date = SYSDATE,
750 period_name = l_period_name,
751 last_update_date = SYSDATE, --Bug#5389144
752 last_updated_by = NVL(fnd_profile.value('USER_ID'),0), --Bug#5389144
753 last_update_login= NVL(fnd_profile.value('LOGIN_ID'),0) --Bug#5389144
754 WHERE payment_schedule_id = l_payment_schedule_id;
755
756 END IF;
757
758 END IF;
759
760 END IF;
761
762 END IF;
763
764 /* print if approved or unapproved */
765 IF l_sched_failed THEN
766
767 fnd_message.set_name('PN', 'PN_APPR_SCHED_FAILED');
768 fnd_message.set_token('SCHEDULE_DATE', l_payment_schedule_date);
769 pnp_debug_pkg.put_log_msg(fnd_message.get);
770 pnp_debug_pkg.put_log_msg(' ');
771
772 /* increment the rejected counter */
773 l_sch_rej := l_sch_rej + 1;
774
775
776 ELSE
777
778 fnd_message.set_name('PN', 'PN_APPR_SCHED_SUCCESS');
779 fnd_message.set_token('SCHEDULE_DATE', l_payment_schedule_date);
780 pnp_debug_pkg.put_log_msg(fnd_message.get);
781 pnp_debug_pkg.put_log_msg(' ');
782
783 END IF;
784
785 END LOOP;
786
787 IF mass_appr_loc_cur%ISOPEN then
788 CLOSE mass_appr_loc_cur;
789 END IF;
790
791 IF mass_appr_cur%ISOPEN then
792 CLOSE mass_appr_cur;
793 END IF;
794
795 /* summary */
796 l_sch_app := l_sch_tot - l_sch_rej;
797
798 fnd_message.set_name('PN', 'PN_APPR_SCHED_SUMMARY');
799 fnd_message.set_token('APPROVED', l_sch_app);
800 fnd_message.set_token('FAILED', l_sch_rej);
801 fnd_message.set_token('TOTAL', l_sch_tot);
802
803 pnp_debug_pkg.put_log_msg(' ');
804 pnp_debug_pkg.put_log_msg
805 ('+==============================================================================+');
806 pnp_debug_pkg.put_log_msg(fnd_message.get);
807 pnp_debug_pkg.put_log_msg
808 ('+==============================================================================+');
809
810 IF bad_currency_table.COUNT > 0 THEN
811
812 pnp_debug_pkg.put_log_msg
813 ('+==============================================================================+');
814 FOR i IN 0 .. (bad_currency_table.COUNT - 1) LOOP
815 fnd_message.set_name('PN','PN_CURRENCY_CONV_FAIL');
816 fnd_message.set_token('FROM_CURRENCY', bad_currency_table(i));
817 fnd_message.set_token('TO_CURRENCY', l_func_curr);
818 pnp_debug_pkg.put_log_msg(fnd_message.get);
819 END LOOP;
820 pnp_debug_pkg.put_log_msg
821 ('+==============================================================================+');
822
823 END IF;
824
825 PNP_DEBUG_PKG.debug ('PN_MASS_APPR_PKG.approve (-)');
826
827 EXCEPTION
828 WHEN others THEN
829 retcode := '2';
830 RAISE;
831
832 END approve;
833
834
835 --------------------------------------------------------------------------------
836 --
837 -- NAME : unapprove
838 -- DESCRIPTION : Un-approves the APPROVED schedules that meet the filter
839 -- criteria.
840 -- PURPOSE : Un-Approves the APPROVED schedules
841 -- INVOKED FROM : pn_mass_appr_pkg.pn_mass_app
842 -- ARGUMENTS : same as pn_mass_appr_pkg.approve() sans the period related
843 -- params
844 -- REFERENCE : PN_COMMON.debug()
845 -- HISTORY :
846 --
847 -- 27-SEP-04 Kiran o Created
848 -- 11-JAN-05 Anand o Removed on_hold = 'Y' condition in the cursors.
849 -- Bug # 4109792
850 -- 28-NOV-05 pikhar o Replaced tables with _ALL tables
851 --------------------------------------------------------------------------------
852 PROCEDURE unapprove( errbuf OUT NOCOPY VARCHAR2
853 ,retcode OUT NOCOPY VARCHAR2
854 ,p_schedule_from_date IN DATE
855 ,p_schedule_to_date IN DATE
856 ,p_trx_from_date IN DATE
857 ,p_trx_to_date IN DATE
858 ,p_lease_class_code IN VARCHAR2
859 ,p_set_of_books IN VARCHAR2
860 ,p_lease_from_number IN VARCHAR2
861 ,p_lease_to_number IN VARCHAR2
862 ,p_location_from_code IN VARCHAR2
863 ,p_location_to_code IN VARCHAR2
864 ,p_responsible_user IN NUMBER) IS
865
866 /* main cursors */
867 CURSOR mass_unappr_loc_cur IS
868 SELECT DISTINCT
869 pps.payment_schedule_id s_payment_schedule_id,
870 pps.schedule_date s_schedule_date,
871 pl.lease_id s_lease_id
872 FROM pn_payment_schedules_all pps,
873 pn_leases pl,
874 pn_lease_details_all pld,
875 pn_tenancies_all pt,
876 pn_locations_all ploc
877 WHERE pps.schedule_date BETWEEN NVL(p_schedule_from_date, pps.schedule_date)
878 AND NVL(p_schedule_to_date, pps.schedule_date)
879 AND pps.payment_status_lookup_code = 'APPROVED'
880 AND pl.lease_id = pps.lease_id
881 AND pl.lease_class_code = NVL(p_lease_class_code,pl.lease_class_code)
882 AND pl.lease_num >= NVL(p_lease_from_number, pl.lease_num)
883 AND pl.lease_num <= NVL(p_lease_to_number, pl.lease_num)
884 AND pld.lease_id = pps.lease_id
885 AND pld.responsible_user = NVL(p_responsible_user, pld.responsible_user)
886 AND pt.lease_id = pps.lease_id
887 AND ploc.location_id = pt.location_id
888 AND ploc.location_code >= NVL(p_location_from_code, ploc.location_code)
889 AND ploc.location_code <= NVL(p_location_to_code, ploc.location_code)
890 AND EXISTS(SELECT NULL
891 FROM pn_payment_items_all item
892 WHERE item.payment_schedule_id = pps.payment_schedule_id
893 AND item.due_date BETWEEN NVL(p_trx_from_date,item.due_date)
894 AND NVL(p_trx_to_date,item.due_date))
895 AND NOT EXISTS (SELECT NULL
896 FROM pn_payment_items_all pi
897 WHERE pi.payment_schedule_id = pps.payment_schedule_id
898 AND (pi.TRANSFERRED_TO_AP_FLAG = 'Y' OR
899 pi.TRANSFERRED_TO_AR_FLAG = 'Y')
900 )
901 ORDER BY pl.lease_id, pps.schedule_date;
902
903 CURSOR mass_unappr_cur IS
904 SELECT DISTINCT
905 pps.payment_schedule_id s_payment_schedule_id,
906 pps.schedule_date s_schedule_date,
907 pl.lease_id s_lease_id
908 FROM pn_payment_schedules_all pps,
909 pn_leases pl,
910 pn_lease_details_all pld
911 WHERE pps.schedule_date BETWEEN NVL(p_schedule_from_date, pps.schedule_date)
912 AND NVL(p_schedule_to_date, pps.schedule_date)
913 AND pps.payment_status_lookup_code = 'APPROVED'
914 AND pl.lease_id = pps.lease_id
915 AND pl.lease_class_code = NVL(p_lease_class_code,pl.lease_class_code)
916 AND pl.lease_num >= NVL(p_lease_from_number, pl.lease_num)
917 AND pl.lease_num <= NVL(p_lease_to_number, pl.lease_num)
918 AND pld.lease_id = pps.lease_id
919 AND pld.responsible_user = NVL(p_responsible_user, pld.responsible_user)
920 AND EXISTS (SELECT NULL
921 FROM pn_payment_items_all item
922 WHERE item.payment_schedule_id = pps.payment_schedule_id
923 AND item.due_date BETWEEN NVL(p_trx_from_date,item.due_date)
924 AND NVL(p_trx_to_date,item.due_date))
925 AND NOT EXISTS (SELECT NULL
926 FROM pn_payment_items_all pi
927 WHERE pi.payment_schedule_id = pps.payment_schedule_id
928 AND (pi.TRANSFERRED_TO_AP_FLAG = 'Y' OR
929 pi.TRANSFERRED_TO_AR_FLAG = 'Y')
930 )
931 ORDER BY pl.lease_id, pps.schedule_date;
932
933 /* counters */
934 l_sch_unapp NUMBER;
935
936 /* variables */
937 l_payment_schedule_id PN_PAYMENT_SCHEDULES_ALL.payment_schedule_id%TYPE;
938 l_payment_schedule_date PN_PAYMENT_SCHEDULES_ALL.schedule_date%TYPE;
939 l_lease_id PN_LEASES_ALL.lease_id%TYPE;
940
941 BEGIN
942
943 PNP_DEBUG_PKG.debug ('PN_MASS_APPR_PKG.unapprove (+)');
944
945 /* init */
946 l_sch_unapp := 0;
947
948 /* open the appropriate cursor based on whether
949 location code has been passed as a parameter or not
950 */
951 IF p_location_from_code IS NOT NULL OR
952 p_location_to_code IS NOT NULL THEN
953
954 OPEN mass_unappr_loc_cur;
955
956 ELSE
957
958 OPEN mass_unappr_cur;
959
960 END IF;
961
962 /* loop through the appropriate cursor
963 unapprove the schedules
964 */
965 LOOP
966
967 /* fetch from the appropriate cursor */
968 IF mass_unappr_loc_cur%ISOPEN THEN
969
970 FETCH mass_unappr_loc_cur
971 INTO l_payment_schedule_id
972 ,l_payment_schedule_date
973 ,l_lease_id;
974
975 EXIT WHEN mass_unappr_loc_cur%NOTFOUND;
976
977 ELSIF mass_unappr_cur%ISOPEN then
978
979 FETCH mass_unappr_cur
980 INTO l_payment_schedule_id
981 ,l_payment_schedule_date
982 ,l_lease_id;
983
984 EXIT WHEN mass_unappr_cur%NOTFOUND;
985
986 END IF;
987
988 /* if we are here, we have a schedule with us to process */
989
990 /* Increment the schedules total */
991 l_sch_unapp := l_sch_unapp + 1;
992
993 /* Update the export flags in the items to NULL */
994 UPDATE PN_PAYMENT_ITEMS
995 SET export_to_ap_flag = NULL,
996 export_to_ar_flag = NULL
997 WHERE payment_item_type_lookup_code = 'CASH'
998 AND payment_schedule_id = l_payment_schedule_id;
999
1000 /* Approve the schedule */
1001 UPDATE PN_PAYMENT_SCHEDULES
1002 SET payment_status_lookup_code = 'DRAFT',
1003 approved_by_user_id = NULL,
1004 approval_date = NULL,
1005 period_name = NULL
1006 WHERE payment_schedule_id = l_payment_schedule_id;
1007
1008 fnd_message.set_name('PN', 'PN_UNAPPR_SCHED_SUCCESS');
1009 fnd_message.set_token('SCHEDULE_DATE', l_payment_schedule_date);
1010 pnp_debug_pkg.put_log_msg(fnd_message.get);
1011 pnp_debug_pkg.put_log_msg(' ');
1012
1013 END LOOP;
1014
1015 fnd_message.set_name('PN', 'PN_UNAPPR_SCHED_SUMMARY');
1016 fnd_message.set_token('UNAPPROVED', l_sch_unapp);
1017
1018 pnp_debug_pkg.put_log_msg(' ');
1019 pnp_debug_pkg.put_log_msg
1020 ('+==============================================================================+');
1021 pnp_debug_pkg.put_log_msg(fnd_message.get);
1022 pnp_debug_pkg.put_log_msg
1023 ('+==============================================================================+');
1024
1025 PNP_DEBUG_PKG.debug ('PN_MASS_APPR_PKG.unapprove (-)');
1026
1027 EXCEPTION
1028 WHEN others THEN
1029 retcode := '2';
1030 RAISE;
1031 END unapprove;
1032
1033
1034 --------------------------------------------------------------------------------
1035 -- NAME : pn_mass_app
1036 -- DESCRIPTION : The main procedure called from the SRS form for Mass Approve
1037 -- /Un-Approve program. Delegates the task to approve() or
1038 -- unapprove() based on the action type.
1039 -- PURPOSE : approve/Un-Approve schedules.
1040 -- INVOKED FROM : SRS screen.
1041 -- ARGUMENTS :
1042 -- REFERENCE : PN_COMMON.debug()
1043 -- HISTORY :
1044 --
1045 -- 30-SEP-04 Kiran o Reorganized - now the procedure delegates the task to
1046 -- approve() and unapprove() - (has been promoted to a
1047 -- managerial post ;))
1048 --------------------------------------------------------------------------------
1049 PROCEDURE pn_mass_app( errbuf OUT NOCOPY VARCHAR2
1050 ,retcode OUT NOCOPY VARCHAR2
1051 ,p_action_type IN VARCHAR2
1052 ,p_schedule_from_date IN VARCHAR2
1053 ,p_schedule_to_date IN VARCHAR2
1054 ,p_trx_from_date IN VARCHAR2
1055 ,p_trx_to_date IN VARCHAR2
1056 ,p_lease_class_code IN VARCHAR2
1057 ,p_set_of_books IN VARCHAR2
1058 ,p_payment_period_dummy IN VARCHAR2
1059 ,p_billing_period_dummy IN VARCHAR2
1060 ,p_payment_period IN VARCHAR2
1061 ,p_billing_period IN VARCHAR2
1062 ,p_lease_from_number IN VARCHAR2
1063 ,p_lease_to_number IN VARCHAR2
1064 ,p_location_from_code IN VARCHAR2
1065 ,p_location_to_code IN VARCHAR2
1066 ,p_responsible_user IN NUMBER) IS
1067
1068 /* convert the dates cannonical to date */
1069 l_sch_from_dt DATE := fnd_date.canonical_to_date(p_schedule_from_date);
1070 l_sch_to_dt DATE := fnd_date.canonical_to_date(p_schedule_to_date);
1071 l_trx_from_dt DATE := fnd_date.canonical_to_date(p_trx_from_date);
1072 l_trx_to_dt DATE := fnd_date.canonical_to_date(p_trx_to_date);
1073
1074 BEGIN
1075
1076 PNP_DEBUG_PKG.debug('pn_mass_appr_pkg.pn_mass_app (+)');
1077
1078 /* --- MESSAGE NEEDED --- */
1079 /* need to print the i/p params */
1080 fnd_message.set_name('PN','PN_MASAPB_PARAMS');
1081 fnd_message.set_token('P_ACTION_TYPE', p_action_type);
1082 fnd_message.set_token('P_SCHEDULE_FROM_DATE', p_schedule_from_date);
1083 fnd_message.set_token('P_SCHEDULE_TO_DATE', p_schedule_to_date);
1084 fnd_message.set_token('P_TRX_FROM_DATE', p_trx_from_date);
1085 fnd_message.set_token('P_TRX_TO_DATE', p_trx_to_date);
1086 fnd_message.set_token('P_LEASE_CLASS_CODE', p_lease_class_code);
1087 fnd_message.set_token('P_SET_OF_BOOKS', p_set_of_books);
1088 fnd_message.set_token('P_PAYMENT_PERIOD', p_payment_period);
1089 fnd_message.set_token('P_BILLING_PERIOD', p_billing_period);
1090 fnd_message.set_token('P_LEASE_FROM_NUMBER', p_lease_from_number);
1091 fnd_message.set_token('P_LEASE_TO_NUMBER', p_lease_to_number);
1092 fnd_message.set_token('P_LOCATION_FROM_CODE', p_location_from_code);
1093 fnd_message.set_token('P_LOCATION_TO_CODE', p_location_to_code);
1094 fnd_message.set_token('P_RESPONSIBLE_USER', p_responsible_user);
1095
1096 pnp_debug_pkg.put_log_msg
1097 ('+------------------------------------------------------------------------------+');
1098 pnp_debug_pkg.put_log_msg(fnd_message.get);
1099 pnp_debug_pkg.put_log_msg
1100 ('+------------------------------------------------------------------------------+');
1101
1102
1103 /* retcode values
1104 0 - success
1105 1 - warning
1106 2 - error
1107 */
1108 retcode := '0';
1109
1110 /* validate the inputs */
1111
1112 /* if a from date is entered, to date must be entered
1113 holds good for schedule date and transaction date */
1114
1115 IF (p_schedule_from_date IS NOT NULL AND p_schedule_to_date IS NULL) OR
1116 (p_schedule_from_date IS NULL AND p_schedule_to_date IS NOT NULL) OR
1117 (p_trx_from_date IS NULL AND p_trx_to_date IS NOT NULL) OR
1118 (p_trx_from_date IS NOT NULL AND p_trx_to_date IS NULL)
1119 THEN
1120
1121 raise INPUT_DATE_MISSING;
1122
1123 END IF;
1124
1125 /* either schedule date range or transaction date range must be entered */
1126 IF p_schedule_from_date IS NULL AND
1127 p_schedule_to_date IS NULL AND
1128 p_trx_from_date IS NULL AND
1129 p_trx_to_date IS NULL
1130 THEN
1131
1132 raise INPUT_DATE_MISSING;
1133
1134 END IF;
1135
1136 IF p_action_type = 'APPROVE' THEN
1137
1138 approve( errbuf => errbuf
1139 ,retcode => retcode
1140 ,p_schedule_from_date => l_sch_from_dt
1141 ,p_schedule_to_date => l_sch_to_dt
1142 ,p_trx_from_date => l_trx_from_dt
1143 ,p_trx_to_date => l_trx_to_dt
1144 ,p_lease_class_code => p_lease_class_code
1145 ,p_set_of_books => p_set_of_books
1146 ,p_payment_period => p_payment_period
1147 ,p_billing_period => p_billing_period
1148 ,p_lease_from_number => p_lease_from_number
1149 ,p_lease_to_number => p_lease_to_number
1150 ,p_location_from_code => p_location_from_code
1151 ,p_location_to_code => p_location_to_code
1152 ,p_responsible_user => p_responsible_user
1153 );
1154
1155 ELSIF p_action_type = 'UNAPPROVE' THEN
1156
1157 unapprove( errbuf => errbuf
1158 ,retcode => retcode
1159 ,p_schedule_from_date => l_sch_from_dt
1160 ,p_schedule_to_date => l_sch_to_dt
1161 ,p_trx_from_date => l_trx_from_dt
1162 ,p_trx_to_date => l_trx_to_dt
1163 ,p_lease_class_code => p_lease_class_code
1164 ,p_set_of_books => p_set_of_books
1165 ,p_lease_from_number => p_lease_from_number
1166 ,p_lease_to_number => p_lease_to_number
1167 ,p_location_from_code => p_location_from_code
1168 ,p_location_to_code => p_location_to_code
1169 ,p_responsible_user => p_responsible_user
1170 );
1171
1172 END IF;
1173
1174 PNP_DEBUG_PKG.debug('pn_mass_appr_pkg.pn_mass_app (-)');
1175
1176 EXCEPTION
1177 WHEN INPUT_DATE_MISSING THEN
1178 fnd_message.set_name('PN','PN_MASAPB_NO_DATE');
1179 pnp_debug_pkg.put_log_msg
1180 ('+------------------------------------------------------------------------------+');
1181 pnp_debug_pkg.put_log_msg(fnd_message.get);
1182 pnp_debug_pkg.put_log_msg
1183 ('+------------------------------------------------------------------------------+');
1184 retcode := '2';
1185
1186 WHEN OTHERS THEN
1187 retcode := '2';
1188 RAISE;
1189
1190 END pn_mass_app;
1191
1192 END pn_mass_appr_pkg;