1 PACKAGE BODY igi_exp_utils AS
2 -- $Header: igiexpqb.pls 120.6.12000000.2 2007/09/21 07:10:18 dvjoshi ship $
3 --
4
5 --
6 -- Procedure
7 -- Generate_Number
8 -- History
9 -- 27-NOV-2001 L Silveira Initial Version
10
11 --
12 PROCEDURE Generate_Number(pi_number_type IN VARCHAR2,
13 pi_number_class IN VARCHAR2,
14 pi_du_tu_type_id IN NUMBER,
15 pi_fiscal_year IN NUMBER,
16 po_du_tu_number OUT NOCOPY VARCHAR2,
17 po_error_message OUT NOCOPY VARCHAR2) IS
18
19 CURSOR c_num_scheme IS
20 SELECT num_scheme_id,
21 prefix,
22 suffix,
23 next_seq_val
24 FROM igi_exp_num_schemes
25 WHERE numbering_type = pi_number_type
26 AND numbering_class = pi_number_class
27 AND du_tu_type_id = pi_du_tu_type_id
28 AND fiscal_year = pi_fiscal_year
29 FOR UPDATE OF next_seq_val; --bug3589744 sdixit
30
31 l_num_scheme_id NUMBER;
32 l_prefix VARCHAR2(100);
33 l_suffix VARCHAR2(100);
34 l_next_seq_val NUMBER;
35
36 e_invalid_parameters EXCEPTION;
37 e_num_scheme_not_found EXCEPTION;
38 BEGIN
39 --
40 -- Validate passed parameter values
41 --
42 IF pi_number_type IS NULL OR
43 pi_number_class IS NULL OR
44 pi_du_tu_type_id IS NULL OR
45 pi_fiscal_year IS NULL
46 THEN
47 RAISE e_invalid_parameters;
48 END IF;
49
50 IF pi_number_type <> 'DU' AND pi_number_type <> 'TU'
51 THEN
52 RAISE e_invalid_parameters;
53 END IF;
54
55 IF pi_number_class <> 'O' AND pi_number_class <> 'L'
56 THEN
57 RAISE e_invalid_parameters;
58 END IF;
59
60 IF LENGTH(TO_CHAR(pi_fiscal_year)) <> 4
61 THEN
62 RAISE e_invalid_parameters;
63 END IF;
64
65 --
66 -- Fetch numbering scheme
67 --
68 OPEN c_num_scheme;
69
70 FETCH c_num_scheme INTO l_num_scheme_id,
71 l_prefix,
72 l_suffix,
73 l_next_seq_val;
74 IF c_num_scheme%NOTFOUND THEN
75 RAISE e_num_scheme_not_found;
76 END IF;
77
78 CLOSE c_num_scheme;
79
80 --
81 -- Update Next Sequence Value
82 --
83
84 UPDATE igi_exp_num_schemes_all
85 SET next_seq_val = (next_seq_val + 1)
86 WHERE num_scheme_id = l_num_scheme_id;
87
88 --
89 -- Build number
90 --
91 po_du_tu_number := l_prefix||TO_CHAR(l_next_seq_val)||l_suffix||
92 TO_CHAR(pi_fiscal_year);
93
94 EXCEPTION
95
96 WHEN e_invalid_parameters THEN
97 po_du_tu_number := '';
98 po_error_message := 'Error: Invalid parameters passed ('||
99 'Number Type:'||pi_number_type||','||
100 'Number Class:'||pi_number_class||','||
101 'DU/TU Type Id:'||TO_CHAR(pi_du_tu_type_id)||','||
102 'Fiscal Year:'||TO_CHAR(pi_fiscal_year)||')';
103
104 WHEN e_num_scheme_not_found THEN
105 IF c_num_scheme%ISOPEN THEN
106 CLOSE c_num_scheme;
107 END IF;
108
109 po_du_tu_number := '';
110 po_error_message := 'Error: Numbering Scheme not found ('||
111 'Number Type:'||pi_number_type||','||
112 'Number Class:'||pi_number_class||','||
113 'DU/TU Type Id:'||TO_CHAR(pi_du_tu_type_id)||','||
114 'Fiscal Year:'||TO_CHAR(pi_fiscal_year)||')';
115
116 WHEN OTHERS THEN
117 IF c_num_scheme%ISOPEN THEN
118 CLOSE c_num_scheme;
119 END IF;
120
121 po_du_tu_number := '';
122 --bug 3199481 fnd logging changes: sdixit: start block
123 --as display of the error is being handled in the form that calls this package,
124 -- just set the standard message here
125 FND_MESSAGE.SET_NAME('IGI','IGI_LOGGING_UNEXP_ERROR');
126 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
127 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
128 po_error_message := FND_MESSAGE.GET;
129 --bug 3199481 fnd logging changes: sdixit: end block
130
131
132 END Generate_Number;
133
134 --
135 -- Procedure
136 -- Get Fiscal Year
137 -- History
138 -- 03-DEC-2001 L Silveira Initial Version
139 --
140 PROCEDURE Get_Fiscal_Year(pi_gl_date IN DATE,
141 po_fiscal_year OUT NOCOPY NUMBER,
142 po_error_message OUT NOCOPY VARCHAR2) IS
143
144 CURSOR c_fiscal_year(p_sob_id NUMBER) IS
145 SELECT period_year
146 FROM gl_periods gp
147 ,gl_sets_of_books gsob
148 WHERE gp.period_set_name = gsob.period_set_name
149 AND gp.period_type = gsob.accounted_period_type
150 -- AND TRUNC(TO_DATE(pi_gl_date, 'DD-MON-RRRR'))
151 AND TRUNC(pi_gl_date)
152 BETWEEN TRUNC(gp.start_date)
153 AND TRUNC(gp.end_date)
154 AND gsob.set_of_books_id = p_sob_id;
155
156 l_sob_id gl_sets_of_books.set_of_books_id%TYPE;
157 l_sob_name gl_sets_of_books.name%TYPE;
158
159 e_invalid_params EXCEPTION;
160 e_sob_not_found EXCEPTION;
161 e_fiscal_year_not_found EXCEPTION;
162 l_current_org_id hr_operating_units.organization_id%type;
163
164 BEGIN
165 -- Validate input parameters
166 IF pi_gl_date IS NULL
167 THEN
168 RAISE e_invalid_params;
169 END IF;
170
171 -- Get the set of books attached to the responsibility
172 --FND_PROFILE.Get('GL_SET_OF_BKS_ID', l_sob_id);
173 /*Bug#5905190 - MOAC changes start*/
174 -- Get current org_id
175 l_current_org_id := mo_global.get_current_org_id();
176 IF l_current_org_id is NULL THEN
177 l_sob_id := NULL;
178 ELSE
179 mo_utils.Get_Set_Of_Books_Info(l_current_org_id,l_sob_id,l_sob_name);
180 END IF;
181
182 /*Bug#5905190 end */
183
184 IF l_sob_id IS NULL
185 THEN
186 RAISE e_sob_not_found;
187 END IF;
188
189 -- Get the fiscal year
190 OPEN c_fiscal_year(l_sob_id);
191
192 FETCH c_fiscal_year INTO po_fiscal_year;
193 IF c_fiscal_year%NOTFOUND
194 THEN
195 RAISE e_fiscal_year_not_found;
196 END IF;
197
198 CLOSE c_fiscal_year;
199 EXCEPTION
200 WHEN e_invalid_params THEN
201 po_fiscal_year := '';
202 po_error_message := 'Error: Invalid parameters passed ('||
203 'GL Date:'||TO_CHAR(pi_gl_date)||')';
204
205 WHEN e_sob_not_found THEN
206 po_fiscal_year := '';
207 po_error_message := 'Error: GL Set of Books not found ('||
208 'GL Date:'||TO_CHAR(pi_gl_date)||')';
209
210 WHEN e_fiscal_year_not_found THEN
211 IF c_fiscal_year%ISOPEN THEN
212 CLOSE c_fiscal_year;
213 END IF;
214
215 po_fiscal_year := '';
216 po_error_message := 'Error: GL Period Record not found ('||
217 'GL Date:'||TO_CHAR(pi_gl_date)||')';
218
219 WHEN OTHERS THEN
220 IF c_fiscal_year%ISOPEN THEN
221 CLOSE c_fiscal_year;
222 END IF;
223
224 po_fiscal_year := '';
225 --bug 3199481 fnd logging changes: sdixit: start block
226 --as display of the error is being handled in the form that calls this package,
227 -- just set the standard message here
228 FND_MESSAGE.SET_NAME('IGI','IGI_LOGGING_UNEXP_ERROR');
229 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
230 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
231 po_error_message := FND_MESSAGE.GET;
232 --bug 3199481 fnd logging changes: sdixit: end block
233 -- po_error_message := 'Unknown Error Code: '||SQLCODE||
234 -- ' Error Message: '||SQLERRM;
235
236 END Get_Fiscal_Year;
237
238 --
239 -- Procedure
240 -- Ar_Complete
241 -- History
242 -- 11-DEC-2001 A Smales Initial Version
243 --
244
245 Procedure Ar_Complete (p_customer_trx IN NUMBER,
246 p_result IN OUT NOCOPY NUMBER)
247 IS
248
249 v_count NUMBER;
250 v_status fnd_product_installations.status%TYPE;
251 v_source VARCHAR2(50) ;
252 v_next_pay NUMBER ;
253 l_debug_info VARCHAR2(2000);
254
255 CURSOR c_get_trx_info(p_trx_id RA_CUSTOMER_TRX.CUSTOMER_TRX_ID%TYPE)
256 IS
257 SELECT trx.previous_customer_trx_id
258 , trx.complete_flag
259 , ctt.accounting_affect_flag
260 , ctt.creation_sign
261 , ctt.allow_overapplication_flag
262 , ctt.natural_application_only_flag
263 FROM ra_customer_trx_all trx
264 , ra_cust_trx_types_all ctt
265 WHERE trx.customer_trx_id = p_trx_id
266 AND trx.cust_trx_type_id = ctt.cust_trx_type_id
267 AND trx.org_id = ctt.org_id;
268
269 l_previous_customer_trx_id ra_customer_trx.previous_customer_trx_id%TYPE;
270 l_complete_flag ra_customer_trx.complete_flag%TYPE;
271 l_trx_open_receivables_flag ra_cust_trx_types.accounting_affect_flag%TYPE;
272 l_creation_sign ra_cust_trx_types.creation_sign%TYPE;
273 l_allow_overapplication_flag ra_cust_trx_types.allow_overapplication_flag%TYPE;
274 l_natural_application_flag ra_cust_trx_types.natural_application_only_flag%TYPE;
275 p_error_message VARCHAR2(2000);
276
277
278 CURSOR c_get_prev_trx_info(p_trx_id RA_CUSTOMER_TRX.CUSTOMER_TRX_ID%TYPE)
279 IS
280 SELECT ctt.accounting_affect_flag
281 FROM ra_customer_trx_all trx
282 , ra_cust_trx_types_all ctt
283 WHERE trx.customer_trx_id = p_trx_id
284 AND trx.cust_trx_type_id = ctt.cust_trx_type_id
285 AND trx.org_id = ctt.org_id;
286
287 l_prev_open_receivables_flag ra_cust_trx_types.accounting_affect_flag%TYPE;
288
289 e_no_trx_info EXCEPTION;
290 e_no_prev_trx_info EXCEPTION;
291
292 BEGIN
293 v_count := 0;
294 v_source := NULL;
295 v_next_pay := NULL;
296 -----------------------------
297 l_debug_info := 'BEGIN ar_complete';
298 --dbms_output.put_line(l_debug_info);
299 ----------------------------------
300
301 SELECT status
302 INTO v_status
303 FROM fnd_product_installations
304 WHERE application_id = 300;
305
306 -----------------------------
307 l_debug_info := 'v_status is: ';
308 --dbms_output.put_line(l_debug_info||','||v_status);
309 ----------------------------------
310
311 FND_PROFILE.GET('SO_SOURCE_CODE',v_source);
312
313
314 -----------------------------
315 l_debug_info := ' FND_PROFILE.GET(SO_SOURCE_CODE is :';
316 --dbms_output.put_line(l_debug_info||','||v_source);
317 ----------------------------------
318
319 v_count := 0;
320
321 arp_trx_complete_chk.do_completion_checking(p_customer_trx,
322 v_source,
323 v_status,
324 v_count
325 );
326
327 -----------------------------
328 l_debug_info := 'open c_open_period';
329 --dbms_output.put_line(l_debug_info);
330 ----------------------------------
331 IF v_count = 0 THEN
332
333 -----------------------------
334 l_debug_info := ' v_count = 0';
335 --dbms_output.put_line(l_debug_info);
336 ----------------------------------
337 UPDATE ra_customer_trx
338 SET complete_flag = 'Y'
339 WHERE customer_trx_id = p_customer_trx;
340
341 -----------------------------
342 l_debug_info := 'UPDATE ra_customer_trx complete';
343 --dbms_output.put_line(l_debug_info);
344 ----------------------------------
345
346 OPEN c_get_trx_info(p_customer_trx);
347 FETCH c_get_trx_info INTO
348 l_previous_customer_trx_id
349 ,l_complete_flag
350 ,l_trx_open_receivables_flag
351 ,l_creation_sign
352 ,l_allow_overapplication_flag
353 ,l_natural_application_flag;
354
355 IF c_get_trx_info%NOTFOUND THEN
356 RAISE e_no_trx_info;
357 END IF;
358 CLOSE c_get_trx_info;
359
360
361 IF l_previous_customer_trx_id is not null THEN
362 -----------------------------
363 l_debug_info := 'l_previous_customer_trx_id is : ';
364 --dbms_output.put_line(l_debug_info||','||l_previous_customer_trx_id);
365 ----------------------------------
366 OPEN c_get_prev_trx_info(l_previous_customer_trx_id);
367 FETCH c_get_prev_trx_info
368 INTO l_prev_open_receivables_flag;
369
370 IF c_get_prev_trx_info%NOTFOUND THEN
371 RAISE e_no_prev_trx_info;
372 END IF;
373 CLOSE c_get_prev_trx_info;
374 END IF; --l_previous_customer_trx_id is not null
375
376 -----------------------------
377 l_debug_info := 'doing arp_process_header.post_commit ';
378 --dbms_output.put_line(l_debug_info);
379 ----------------------------------
380
381 arp_process_header.post_commit('IGIPEPDU',12.0
382 ,p_customer_trx
383 ,l_previous_customer_trx_id
384 ,l_complete_flag
385 ,l_trx_open_receivables_flag
386 ,l_prev_open_receivables_flag
387 ,l_creation_sign
388 ,l_allow_overapplication_flag
389 ,l_natural_application_flag
390 ,NULL);
391
392 p_result := 0; --success
393 ELSE
394 p_result := -5; -- failure
395 END IF; -- v_count = 0
396
397 -----------------------------
398 l_debug_info := 'p_result is :';
399 --dbms_output.put_line(l_debug_info||','|| p_result);
400 ----------------------------------
401 EXCEPTION
402
403 WHEN e_no_trx_info THEN p_result := -5;
404 IF c_get_trx_info%ISOPEN THEN
405 CLOSE c_get_trx_info;
406 END IF;
407
408 WHEN e_no_prev_trx_info THEN p_result := -5;
409 IF c_get_prev_trx_info%ISOPEN THEN
410 CLOSE c_get_prev_trx_info;
411 END IF;
412
413 WHEN OTHERS THEN
414 p_result := -5;
415 IF c_get_prev_trx_info%ISOPEN THEN
416 CLOSE c_get_prev_trx_info;
417 ELSIF c_get_trx_info%ISOPEN THEN
418 CLOSE c_get_trx_info;
419 END IF;
420
421 --bug 3199481 fnd logging changes: sdixit: start block
422 --as display of the error is being handled in the form that calls this package,
423 -- just set the standard message here
424 FND_MESSAGE.SET_NAME('IGI','IGI_LOGGING_UNEXP_ERROR');
425 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
426 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
427 p_error_message := FND_MESSAGE.GET;
428 --bug 3199481 fnd logging changes: sdixit: end block
429 -- p_error_message := 'Unknown Error Code: '||SQLCODE||
430 -- 'Error Message: '||SQLERRM;
431 END Ar_Complete;
432
433 --
434 -- Procedure
435 -- ValidateGLDate
436 -- History
437 -- 11-DEC-2001 A Smales Initial Version
438 -- Old logic
439 -- Checks to see if the date passed is valid exists in an open period
440 -- IF not checks if the date exists within the last open period
441 -- IF not assigns date to sysdate
442 -- IF p_app_id passed in is 200 then checks if encumbrance is on
443 -- IF on checks if the period year is greater than the encumbrance year
444 -- IF so, sets p_update_gl_date flag to 'N'
445 -- 8-APR-2003 SHSAXENA
446 -- New logic
447 /* =========================================================================================
448 ##
449 ## By default p_gl_date is sysdate.
450 ##
451 ## This logic is implemented based on the assumption that the fiscal year is as same as
452 ## the Calendar year
453 ##
454 ## IF Current fiscal year = DU's fiscal year then,
455 ## IF p_gl_date falls with in the CURRENT period which is OPEN,
456 ## then p_gl_date (sysdate) is returned.
457 ## IF p_gl_date falls with in the CURRENT period and the CURRENT period is not OPEN,
458 ## then pick up the latest open period of CURRENT fiscal year.
459 ## IF there is a latest OPEN Period
460 ## then return the END DATE.
461 ## IF there are no OPEN period in the Current fiscal year
462 ## then Raise exception.
463 ##
464 ## IF Current fiscal year <> DU's fiscal year then,
465 ## Pick up the END DATE of previous fiscal year.
466 ## Check Whether the END DATE falls with in the OPEN period.
467 ## IF END DATE is with in the OPEN period then
468 ## return END DATE.
469 ## IF END DATE is not with in the OPEN period then
470 ## pickup the latest OPEN period of the Current fiscal year.
471 ## return the END DATE of the latest OPEN period.
472 ## IF there are no OPEN period in the CURRENT fiscal year
473 ## then Raise Exception.
474 ##
475 ## ======================================================================================== */
476
477
478 PROCEDURE ValidateGLDate(p_app_id IN VARCHAR2,
479 p_gl_date IN OUT NOCOPY DATE,
480 p_update_gl_date OUT NOCOPY VARCHAR2,
481 p_du_id IN VARCHAR2) -- shsaxena Bug 2777575
482 Is
483
484 CURSOR c_get_encum_flag
485 IS
486 SELECT purch_encumbrance_flag
487 FROM financials_system_parameters;
488
489 CURSOR c_open_period(pv_sob_id gl_sets_of_books.set_of_books_id%TYPE,
490 pv_app_id igi_exp_du_type_headers.application_id%TYPE,
491 pv_gl_date DATE)
492 IS
493 SELECT gps.period_year,
494 gsob.latest_encumbrance_year
495 FROM gl_period_statuses gps,
496 gl_sets_of_books gsob
497 WHERE gps.application_id = pv_app_id
498 AND gps.set_of_books_id = pv_sob_id
499 AND gps.set_of_books_id = gsob.set_of_books_id
500 AND trunc(pv_gl_date) BETWEEN trunc(gps.start_date) AND trunc(gps.end_date) --Bug5705031
501 AND gps.closing_status IN ('O', 'F')
502 AND NVL(gps.adjustment_period_flag, 'N') = 'N';
503
504 CURSOR c_last_open_period(pv_sob_id gl_sets_of_books.set_of_books_id%TYPE,
505 pv_app_id igi_exp_du_type_headers.application_id%TYPE,
506 pv_period_year number)
507 IS
508 SELECT gps.period_year,
509 gsob.latest_encumbrance_year,
510 gps.end_date
511 FROM gl_period_statuses gps,
512 gl_sets_of_books gsob
513 WHERE gps.application_id = pv_app_id
514 AND gps.set_of_books_id = pv_sob_id
515 AND gps.set_of_books_id = gsob.set_of_books_id
516 AND gsob.latest_opened_period_name = gps.period_name
517 AND gps.period_year = pv_period_year; -- shsaxena Bug 2777575.
518
519 /* shsaxena Bug 2777575 START */
520 CURSOR Cur_current_fis_year (pv_sob_id gl_sets_of_books.set_of_books_id%TYPE,
521 pv_app_id igi_exp_du_type_headers.application_id%TYPE,
522 pv_gl_date DATE)
523 IS
524 SELECT gps.period_year
525 FROM gl_period_statuses gps,
526 gl_sets_of_books gsob
527 WHERE gps.application_id = pv_app_id
528 AND gps.set_of_books_id = pv_sob_id
529 AND gps.set_of_books_id = gsob.set_of_books_id
530 AND trunc(pv_gl_date) BETWEEN trunc(gps.start_date) AND trunc(gps.end_date);
531
532
533 CURSOR Cur_prev_year_end_date (pv_sob_id gl_sets_of_books.set_of_books_id%TYPE,
534 pv_app_id igi_exp_du_type_headers.application_id%TYPE,
535 pv_period_year number)
536 IS
537 SELECT gps.end_date
538 FROM gl_period_statuses gps
539 WHERE gps.application_id = pv_app_id
540 AND gps.set_of_books_id = pv_sob_id
541 AND gps.period_year = pv_period_year
542 AND gps.period_num =
543 (SELECT max(gps1.period_num) from gl_period_statuses gps1
544 WHERE gps1.application_id = pv_app_id
545 AND gps1.set_of_books_id = pv_sob_id
546 AND gps1.period_year = pv_period_year);
547
548
549 CURSOR Cur_du_fiscal_year (p_du_id NUMBER) IS
550 SELECT du_fiscal_year from igi_exp_dus_v
551 WHERE du_id = p_du_id;
552
553 l_current_fiscal_year igi_exp_dus_v.du_fiscal_year%TYPE;
554 l_du_fiscal_year igi_exp_dus_v.du_fiscal_year%TYPE;
555 /* shsaxena Bug 2777575 END */
556
557 l_latest_encumbrance_year gl_sets_of_books.latest_encumbrance_year%TYPE ;
558 l_period_year gl_period_statuses.period_year%TYPE ;
559 l_purch_encumbrance_flag financials_system_parameters.purch_encumbrance_flag%TYPE ;
560 l_sob_id gl_sets_of_books.set_of_books_id%TYPE ;
561 l_debug_info VARCHAR2(2000);
562 l_gl_date DATE;
563 p_error_message VARCHAR2(2000);
564
565 NO_OPEN_PERIOD Exception; -- shsaxena Bug 2777575.
566
567 l_sob_name gl_sets_of_books.name%TYPE;
568 l_current_org_id hr_operating_units.organization_id%type;
569
570
571 BEGIN
572
573 p_update_gl_date := 'Y';
574
575 -- get set of books id
576 --fnd_profile.get('GL_SET_OF_BKS_ID', l_sob_id) ; -- commented for Bug 5905190
577
578 /*Bug#5905190 - MOAC changes start*/
579 -- Get current org_id
580 l_current_org_id := mo_global.get_current_org_id();
581 IF l_current_org_id is NULL THEN
582 l_sob_id := NULL;
583 ELSE
584 mo_utils.Get_Set_Of_Books_Info(l_current_org_id,l_sob_id,l_sob_name);
585 END IF;
586
587 /*Bug#5905190 end */
588
589 /* shsaxena Bug 2777575 START */
590 -- Getting DU's fiscal year.
591 OPEN Cur_du_fiscal_year (p_du_id);
592 FETCH Cur_du_fiscal_year INTO l_du_fiscal_year;
593 CLOSE Cur_du_fiscal_year;
594
595 -- Getting the current fiscal year.
596 OPEN Cur_current_fis_year (l_sob_id, p_app_id, p_gl_date);
597 FETCH Cur_current_fis_year INTO l_current_fiscal_year;
598 CLOSE Cur_current_fis_year;
599
600 IF (l_current_fiscal_year = l_du_fiscal_year) THEN
601
602 OPEN c_open_period (l_sob_id, p_app_id, p_gl_date);
603 FETCH c_open_period INTO l_period_year, l_latest_encumbrance_year;
604
605 -- if current period is not open.
606 IF (c_open_period%NOTFOUND) THEN
607 CLOSE c_open_period;
608
609 OPEN c_last_open_period (l_sob_id, p_app_id, l_current_fiscal_year);
610 FETCH c_last_open_period INTO l_period_year, l_latest_encumbrance_year, l_gl_date;
611
612 -- if there is no latest open period for the current year.
613 IF (c_last_open_period%NOTFOUND) THEN
614
615 CLOSE c_last_open_period;
616 RAISE NO_OPEN_PERIOD; -- Exception
617 ELSE
618
619 IF (c_last_open_period%ISOPEN) THEN
620 CLOSE c_last_open_period;
621 END IF;
622
623 -- l_gl_date has the latest open period's end date.
624 p_gl_date := l_gl_date;
625
626 END IF;
627
628 ELSE
629
630 IF (c_open_period%ISOPEN) THEN
631 CLOSE c_open_period;
632 END IF;
633
634 -- assign the current period's date .
635 p_gl_date := TRUNC(sysdate);
636
637 END IF;
638
639 ELSE -- if fiscal year and du's fiscal year is not same then
640
641 -- Getting the end date of the previous fiscal year.
642 OPEN Cur_prev_year_end_date (l_sob_id, p_app_id, l_du_fiscal_year);
643 FETCH Cur_prev_year_end_date INTO l_gl_date;
644 CLOSE Cur_prev_year_end_date;
645
646 -- checking whether the end date of the previous fiscal year is OPEN.
647 OPEN c_open_period (l_sob_id, p_app_id, l_gl_date);
648 FETCH c_open_period INTO l_period_year, l_latest_encumbrance_year;
649
650 -- if end date of the previous fiscal year does not fall with in the OPEN period.
651 IF (c_open_period%NOTFOUND) THEN
652 CLOSE c_open_period;
653
654 -- Get the latest OPEN period of the current fiscal year.
655 OPEN c_last_open_period (l_sob_id, p_app_id, l_current_fiscal_year);
656 FETCH c_last_open_period INTO l_period_year, l_latest_encumbrance_year, l_gl_date;
657
658 -- if there is no latest open period for the current year.
659 IF (c_last_open_period%NOTFOUND) THEN
660
661 CLOSE c_last_open_period;
662 RAISE NO_OPEN_PERIOD; -- Exception
663
664 ELSE
665
666 IF (c_last_open_period%ISOPEN) THEN
667 CLOSE c_last_open_period;
668 END IF;
669
670 -- l_gl_date has the latest open period's end date.
671 p_gl_date := l_gl_date;
672
673 END IF;
674
675 ELSE -- then set the end date of the previous fiscal year's OPEN period.
676
677 IF (c_last_open_period%ISOPEN) THEN
678 CLOSE c_last_open_period;
679 END IF;
680
681 -- assign 31-DEC to p_gl_date.
682 p_gl_date := l_gl_date;
683
684 END IF;
685 END IF;
686 /* Shsaxena Bug 2777575 END */
687
688 IF p_app_id = 200 THEN -- check encumbrance for AP gl_date
689
690 ----------------------------------------------------
691 -- Get the period year for l_gl_date
692 ----------------------------------------------------
693
694 OPEN c_get_encum_flag ;
695 FETCH c_get_encum_flag INTO l_purch_encumbrance_flag ;
696 CLOSE c_get_encum_flag ;
697
698 ------------------------------------------------------------
699 -- If encumbrance is on, check that the GL date to be is not
700 -- in a period_year beyond the latest encumbrance year.
701 ------------------------------------------------------------
702
703 IF ( NVL(l_purch_encumbrance_flag, 'N') = 'Y') THEN
704 IF TO_NUMBER(l_period_year) > TO_NUMBER(l_latest_encumbrance_year) THEN
705 p_update_gl_date:= 'N'; -- update gl_date for AP doc's set to No
706 END IF; --GL date is not in a period_year beyond the latest encumberance year
707 END IF; -- if purchasing encumberance is on
708 END IF; --p_app_id = 200
709
710 EXCEPTION
711 WHEN NO_OPEN_PERIOD THEN -- shsaxena Bug 2777575
712 p_gl_date := NULL;
713
714 WHEN OTHERS THEN
715 IF c_get_encum_flag%ISOPEN THEN
716 CLOSE c_get_encum_flag ;
717 END IF ;
718 IF c_open_period%ISOPEN THEN
719 CLOSE c_open_period ;
720 END IF ;
721 IF c_last_open_period%ISOPEN THEN
722 CLOSE c_last_open_period ;
723 END IF;
724
725 --bug 3199481 fnd logging changes: sdixit: start block
726 --as display of the error is being handled in the form that calls this package,
727 -- just set the standard message here
728 FND_MESSAGE.SET_NAME('IGI','IGI_LOGGING_UNEXP_ERROR');
729 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
730 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
731 p_error_message := FND_MESSAGE.GET;
732 --bug 3199481 fnd logging changes: sdixit: end block
733 -- p_error_message := 'Unknown Error Code: '||SQLCODE||
734 -- ' Error Message: '||SQLERRM;
735 END ValidateGLDate ;
736
737 --
738 -- Procedure
739 -- CompleteDU
740 -- History
741 -- 11-DEC-2001 A Smales Initial Version
742 --
743
744
745 PROCEDURE Complete_Du(p_du_id IN NUMBER,
746 p_app_id IN NUMBER,
747 p_gl_date IN OUT NOCOPY DATE,
748 p_error_message IN OUT NOCOPY VARCHAR2,
749 p_trx_id OUT NOCOPY NUMBER
750 )
751 IS
752
753 l_dummy NUMBER;
754 l_ar_trans NUMBER;
755 l_ap_trans NUMBER;
756 l_gl_date DATE;
757 l_update_gl_date VARCHAR2(1);
758 p_result NUMBER;
759 l_debug_info VARCHAR2(2000);
760 l_gl_date DATE;
761 l_trx_id NUMBER;
762
763 CURSOR c_ap_invoices(pv_du_id igi_exp_dus_all.du_id%TYPE)
764 IS
765 SELECT a.invoice_id,
766 a.source,
767 a.cancelled_date,
768 a.gl_date -- shsaxena bug2777575.
769 FROM igi_exp_ap_trans i,
770 ap_invoices_all a
771 WHERE i.invoice_id = a.invoice_id
772 AND i.du_id = pv_du_id;
773
774 CURSOR c_ar_trx(pv_du_id igi_exp_dus_all.du_id%TYPE)
775 IS
776 SELECT rct.customer_trx_id
777 , rct.trx_number
778 , rctt.name
779 , arl.meaning
780 FROM ra_customer_trx_all rct
781 , igi_exp_ar_trans i
782 , ar_lookups arl
783 , ra_cust_trx_types_all rctt
784 WHERE i.du_id = pv_du_id
785 AND rct.customer_trx_id = i.customer_trx_id
786 AND rctt.cust_trx_type_id = rct.cust_trx_type_id
787 AND rctt.org_id = rct.org_id
788 AND arl.lookup_code = rct.status_trx
789 AND arl.lookup_type ='INVOICE_TRX_STATUS' ;
790
791 e_no_open_gl_date EXCEPTION; -- shsaxena bug.2777575
792 e_null_param EXCEPTION;
793 e_invalid_p_du_id EXCEPTION;
794 e_ar_trans EXCEPTION;
795 e_ap_trans EXCEPTION;
796 e_invalid_p_app_id EXCEPTION;
797 e_ar_complete_flag_failed EXCEPTION;
798
799 BEGIN
800 l_dummy := 0;
801 l_ar_trans := 0;
802 l_ap_trans := 0;
803 p_result := 0;
804 ------------------------------------------------
805 -- Validate passed parameter values
806 -- check for NULL
807 ------------------------------------------------
808 IF p_du_id IS NULL THEN -- null p_du_id
809 RAISE e_null_param ;
810 END IF;
811
812 IF p_app_id IS NULL THEN -- null p_app_id
813 RAISE e_null_param ;
814 END IF;
815 ------------------------------------------------
816 -- Validate passed parameter values
817 -- check for Valid/Invalid p_du_id
818 ------------------------------------------------
819 -----------------------------
820 l_debug_info := 'check valid du';
821 --dbms_output.put_line(l_debug_info);
822 ----------------------------------
823 SELECT COUNT(1)
824 INTO l_dummy
825 FROM igi_exp_dus
826 WHERE du_id = p_du_id;
827
828 IF l_dummy = 0 THEN -- invalid p_du_id
829 RAISE e_invalid_p_du_id;
830 ELSE -- valid p_du_id
831
832 ------------------------------------------------------
833 -- Call Procedure ValidateGLDate to check GL Date
834 -------------------------------------------------------
835 -----------------------------
836 l_debug_info := 'check gl_dates';
837 --dbms_output.put_line(l_debug_info);
838 ----------------------------------
839
840
841 ValidateGLDate(p_app_id,
842 p_gl_date,
843 l_update_gl_date,
844 p_du_id); -- shsaxena Bug 2777575.
845
846 -- shsaxena Bug 2777575.
847 -- checking for a valid date in p_gl_date.
848 IF (p_gl_date is NULL) THEN
849 Raise e_no_open_gl_date;
850 END IF;
851
852
853 -----------------------------
854 l_debug_info := 'p_gl_date = ';
855 --dbms_output.put_line(l_debug_info||','||p_gl_date);
856 ----------------------------------
857 -----------------------------
858 l_debug_info := 'l_update_gl_date = ';
859 --dbms_output.put_line('l_update_gl_date : '||l_update_gl_date);
860 ----------------------------------
861 ------------------------------------------------
862 -- Validate passed parameter values
863 -- check for Valid/Invalid p_app_id
864 ------------------------------------------------
865
866 -----------------------------
867 l_debug_info := 'check valid p_app_id';
868 --dbms_output.put_line(l_debug_info||' '||p_app_id );
869 ----------------------------------
870 IF p_app_id = 200 THEN -- this is a AP Dialog Unit
871
872 -------------------------------------------------------
873 -- Check the documents contained within the Dialog Unit
874 -- are all within the igi_exp_ap_trans table
875 -------------------------------------------------------
876 -----------------------------
877 l_debug_info := 'p_app_id = 200';
878 --dbms_output.put_line(l_debug_info);
879 ----------------------------------
880 SELECT COUNT(1)
881 INTO l_ar_trans
882 FROM igi_exp_ar_trans_all
883 WHERE du_id = p_du_id;
884
885 IF l_ar_trans > 0 THEN -- there are AR transactions within the AP Dialog unit
886 RAISE e_ar_trans;
887 ELSE
888
889 -----------------------------
890 l_debug_info := 'check for ar trans in ap du ';
891 --dbms_output.put_line(l_debug_info);
892 ----------------------------------
893 -----------------------------------------------
894 -- only AP documents within the AP Dialog Unit
895 -- Dialog unit exists within igi_exp_dus
896 -- p_app_id is 200
897 --
898 -- Loop through invoices in Dialog Unit
899 -- 1. Release the EXP hold for all invoices within the
900 -- Dialog Unit
901 -- 2. Update the GL date
902 -------------------------------------------------------
903
904 FOR r_ap_invoices IN c_ap_invoices(p_du_id)LOOP
905 -----------------------------
906 l_debug_info := 'in ap loop';
907 --dbms_output.put_line(l_debug_info);
908 ----------------------------------
909 --------------------------------------------------------
910 -- 1. Release the EXP hold for this invoice
911 --------------------------------------------------------
912 -----------------------------
913 l_debug_info := 'release holds';
914 --dbms_output.put_line(l_debug_info);
915 ----------------------------------
916
917 IGI_EXP_HOLDS.Place_Release_Hold(r_ap_invoices.invoice_id,
918 '', -- invoice amount Bug 2469158
919 r_ap_invoices.source,
920 r_ap_invoices.cancelled_date,
921 'R',
922 'AWAIT EXP APP',
923 'IGI EXP WORKFLOW'
924 );
925
926 --------------------------------------------------------------------
927 -- 2. If l_update_gl_date = 'Y' Update the GL Date for this invoice
928 --------------------------------------------------------------------
929
930 IF l_update_gl_date = 'Y' THEN
931
932 /* shsaxena bug.2777575 START */
933
934 UPDATE ap_invoice_distributions apd
935 SET apd.accounting_date = TRUNC(p_gl_date),
936 apd.last_update_login = NVL(fnd_profile.value('LOGIN_ID'),-1),
937 apd.last_update_date = SYSDATE,
938 apd.last_updated_by = NVL(fnd_profile.value('USER_ID'),-1)
939 WHERE apd.invoice_id = r_ap_invoices.invoice_id
940 AND apd.posted_flag = 'N';
941
942
943 /* dvjoshi bug#5905190 START
944
945 UPDATE ap_invoice_lines apl
946 SET apl.gl_date = TRUNC(p_gl_date),
947 apl.last_update_login = NVL(fnd_profile.value('LOGIN_ID'),-1),
948 apl.last_update_date = SYSDATE,
949 apl.last_updated_by = NVL(fnd_profile.value('USER_ID'),-1)
950 WHERE apl.invoice_id = r_ap_invoices.invoice_id
951 AND EXISTS
952 (SELECT 'x' FROM ap_invoice_distributions aid
953 WHERE aid.invoice_id = apl.invoice_id
954 AND aid.posted_flag = 'N');
955
956 dvjoshi bug#5905190 END */
957
958 UPDATE ap_invoices api
959 SET api.gl_date = TRUNC(p_gl_date),
960 api.last_update_login = NVL(fnd_profile.value('LOGIN_ID'),-1),
961 api.last_update_date = SYSDATE,
962 api.last_updated_by = NVL(fnd_profile.value('USER_ID'),-1)
963 WHERE api.invoice_id = r_ap_invoices.invoice_id
964 AND EXISTS
965 (SELECT 'x' FROM ap_invoice_distributions aid
966 WHERE aid.invoice_id = api.invoice_id
967 AND aid.posted_flag = 'N');
968
969 /* shsaxena bug.2777575 END */
970
971 END IF; --l_update_gl_date = 'Y'
972
973 END LOOP;
974 END IF; -- there are AR transactions within the AP Dialog unit
975
976
977 ELSIF p_app_id = 222 THEN -- this is an AR Dialog Unit
978 -------------------------------------------------------
979 -- Check the documents contained within the Dialog Unit
980 -- are all within the igi_exp_ar_trans table
981 -------------------------------------------------------
982 SELECT COUNT(1)
983 INTO l_ap_trans
984 FROM igi_exp_ap_trans
985 WHERE du_id = p_du_id;
986
987 IF l_ap_trans > 0 THEN -- there are AP transactions within the AR Dialog unit
988 RAISE e_ap_trans;
989 ELSE
990 -----------------------------------------------
991 -- only AR documents within the AR Dialog Unit
992 -- Dialog unit exists within igi_exp_dus
993 -- p_app_id is 222
994 --
995 -- Loop through AR transactions in the Dialog Unit
996 -- 1. Update the complete flag
997 -- 2. Update the GL date
998 -------------------------------------------------------
999
1000 FOR r_ar_trx IN c_ar_trx(p_du_id)LOOP
1001
1002 -------------------------------------------------------
1003 -- 1. Update the complete flag for this transaction
1004 -------------------------------------------------------
1005 Ar_Complete(r_ar_trx.customer_trx_id,
1006 p_result);
1007 -----------------------------
1008 l_debug_info := 'P_result is ';
1009 --dbms_output.put_line(l_debug_info||' '||p_result);
1010 ----------------------------------
1011
1012 IF p_result = -5 THEN
1013 l_trx_id :=r_ar_trx.customer_trx_id;
1014 RAISE e_ar_complete_flag_failed;
1015 END IF;
1016
1017 -------------------------------------------------------
1018 -- 2. Update the GL Date for this transaction
1019 -------------------------------------------------------
1020
1021 /* shsaxena bug.2777575 START */
1022
1023 UPDATE ra_cust_trx_line_gl_dist rgd
1024 SET rgd.gl_date = TRUNC(p_gl_date),
1025 rgd.last_update_login = NVL(fnd_profile.value('LOGIN_ID'),-1),
1026 rgd.last_update_date = SYSDATE,
1027 rgd.last_updated_by = NVL(fnd_profile.value('USER_ID'),-1)
1028 WHERE rgd.customer_trx_id = r_ar_trx.customer_trx_id
1029 AND rgd.gl_posted_date IS NULL;
1030
1031 UPDATE ra_customer_trx rct
1032 SET rct.trx_date = TRUNC(p_gl_date),
1033 rct.last_update_login = NVL(fnd_profile.value('LOGIN_ID'),-1),
1034 rct.last_update_date = SYSDATE,
1035 rct.last_updated_by = NVL(fnd_profile.value('USER_ID'),-1)
1036 WHERE rct.customer_trx_id = r_ar_trx.customer_trx_id
1037 AND EXISTS
1038 (SELECT 'x' FROM ra_cust_trx_line_gl_dist rgd
1039 WHERE rgd.customer_trx_id = rct.customer_trx_id
1040 AND rgd.gl_posted_date IS NULL);
1041
1042 /* shsaxena bug.2777575 END */
1043
1044 END LOOP;
1045 END IF; -- there are AP doc's in this AR dialog unit
1046 ELSE -- this is an invalid application id
1047 RAISE e_invalid_p_app_id;
1048 END IF; -- this is an invalid application id
1049 END IF; --l_dummy =0
1050
1051 p_error_message:= 'Success' ;
1052
1053 EXCEPTION
1054 WHEN e_no_open_gl_date THEN -- shsaxena bug.2777575.
1055 p_error_message := 'Error: There is no Open GL date for the current fiscal year.' ||
1056 'Please Open the GL period and Approve the DU. ';
1057
1058 WHEN e_null_param THEN
1059 p_error_message := 'Error: Parameter was passed was null p_du_id: '||TO_CHAR(p_du_id)||
1060 ' p_app_id: '||TO_CHAR(p_app_id);
1061
1062
1063 WHEN e_invalid_p_du_id THEN
1064 p_error_message := 'Error: Parameter p_du_id '||TO_CHAR(p_du_id) ||
1065 ' does not exist within the table igi_exp_dus.'||
1066 ' This is an invalid Dialog Unit';
1067
1068 WHEN e_ar_trans THEN
1069 p_trx_id :=l_trx_id;
1070 p_error_message := 'Error: The dialog unit has an application_id of 200, indicating that '||
1071 'the dialog unit contains only AP document. However, there is an AR '||
1072 'transaction held within this dialog unit';
1073
1074 WHEN e_ap_trans THEN
1075 p_error_message := 'Error: The dialog unit has an application_id of 222, indicating '||
1076 'that the dialog unit contains only AR document. However, there is '||
1077 'an AP transaction held within this dialog unit';
1078
1079 WHEN e_ar_complete_flag_failed THEN
1080 p_trx_id :=l_trx_id;
1081 IF c_ar_trx%ISOPEN THEN
1082 CLOSE c_ar_trx;
1083 END IF;
1084
1085 p_error_message := 'Error: The procedure ar_complete within package igi_exp_complete_du '||
1086 'did not complete successfully. The customer_trx_id: ' || l_trx_id||
1087 ' has not been completed and the gl date has not been updated';
1088
1089
1090 WHEN e_invalid_p_app_id THEN
1091
1092 p_error_message := 'Error: The p_app_id is not 200 or 222 and therefore is invalid.';
1093
1094
1095 WHEN OTHERS THEN
1096
1097 IF c_ap_invoices%ISOPEN THEN
1098 CLOSE c_ap_invoices;
1099 ELSIF c_ar_trx%ISOPEN THEN
1100 CLOSE c_ar_trx;
1101 END IF;
1102
1103 --dbms_output.put_line('in OTHERS exception');
1104 --bug 3199481 fnd logging changes: sdixit: start block
1105 --as display of the error is being handled in the form that calls this package,
1106 -- just set the standard message here
1107 FND_MESSAGE.SET_NAME('IGI','IGI_LOGGING_UNEXP_ERROR');
1108 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
1109 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
1110 p_error_message := FND_MESSAGE.GET;
1111 --bug 3199481 fnd logging changes: sdixit: end block
1112 -- p_error_message := 'Unknown Error Code: '||SQLCODE||
1113 -- ' Error Message: '||SQLERRM;
1114
1115 END Complete_Du;
1116
1117 END igi_exp_utils;
[Home] [Help]