1 PACKAGE BODY igc_cbc_po_grp AS
2 -- $Header: IGCBCPOB.pls 120.25.12000000.2 2007/09/07 11:24:39 smannava ship $
3 --
4 -- Global Variables
5 g_org_id NUMBER := to_number(fnd_profile.value('ORG_ID'));
6 G_PKG_NAME CONSTANT VARCHAR2(30) := 'IGC_CBC_PO_GRP';
7 --
8
9 -- Some Common Cursors
10 -- cursor to return max and min gl dates for PO distributions
11
12 CURSOR c_po_dates(p_document_id po_distributions.po_header_id%TYPE)
13 IS
14 SELECT max(pod.gl_encumbered_date)
15 ,min(pod.gl_encumbered_date)
16 FROM po_distributions pod,
17 po_lines pol,
18 po_line_locations poll
19 WHERE pol.po_header_id = p_document_id
20 AND NVL(pol.closed_code,'X') <> 'FINALLY CLOSED'
21 AND NVL(poll.closed_code,'X') <> 'FINALLY CLOSED'
22 AND poll.shipment_type IN ('STANDARD','PLANNED')
23 AND pod.line_location_id = poll.line_location_id
24 AND pod.po_line_id = pol.po_line_id
25 AND poll.po_line_id = pol.po_line_id
26 AND nvl(poll.cancel_flag,'N') = 'N'
27 AND nvl(pol.cancel_flag,'N') = 'N'
28 AND nvl(pod.prevent_encumbrance_flag,'N') = 'N'
29 AND GREATEST( Decode (poll.accrue_on_receipt_flag,
30 'N', Nvl(pod.quantity_ordered,0) -
31 Greatest (nvl(pod.quantity_billed,0),
32 Nvl(pod.unencumbered_quantity,0)),
33 'Y', nvl(pod.quantity_ordered,0) -
34 Greatest (Nvl(pod.quantity_delivered,0),
35 Nvl(pod.unencumbered_quantity,0)), 0) ,0) > 0 ;
36
37
38 -- cursor to return max and min gl dates for requisition distributions
39
40 CURSOR c_req_dates(p_document_id po_requisition_lines.requisition_header_id%TYPE)
41 IS
42 SELECT max(gl_encumbered_date)
43 ,min(gl_encumbered_date)
44 FROM po_req_distributions dists
45 ,po_requisition_lines lines
46 WHERE dists.requisition_line_id = lines.requisition_line_id
47 AND lines.requisition_header_id = p_document_id
48 AND NVL(lines.closed_code,'X') <> 'FINALLY CLOSED'
49 AND NVL(lines.cancel_flag,'N') = 'N'
50 AND Nvl(lines.line_location_id,-999) = -999
51 AND lines.source_type_code = 'VENDOR';
52 -- AND NVL(dists.prevent_encumbrance_flag,'N') = 'N';
53
54 -- cursor to return max and min gl dates for releases
55 CURSOR c_rel_dates(p_document_id po_distributions.po_release_id%TYPE)
56 IS
57 SELECT max(pod.gl_encumbered_date)
58 ,min(pod.gl_encumbered_date)
59 FROM po_distributions pod,
60 po_line_locations poll
61 WHERE pod.po_release_id = p_document_id
62 AND poll.po_release_id = p_document_id
63 AND NVL(poll.closed_code,'X') <> 'FINALLY CLOSED'
64 AND pod.line_location_id = poll.line_location_id
65 -- ssmales 02-Apr-03 bug 2876775 cancel flag clause below needs nvl
66 -- AND poll.cancel_flag = 'N'
67 AND NVL(poll.cancel_flag,'N') = 'N'
68 AND poll.shipment_type IN ('BLANKET','SCHEDULED')
69 AND NVL(pod.prevent_encumbrance_flag,'N') = 'N'
70 AND GREATEST( Decode (poll.accrue_on_receipt_flag,
71 'N', Nvl(pod.quantity_ordered,0) -
72 Greatest (nvl(pod.quantity_billed,0),
73 Nvl(pod.unencumbered_quantity,0)),
74 'Y', nvl(pod.quantity_ordered,0) -
75 Greatest (Nvl(pod.quantity_delivered,0),
76 Nvl(pod.unencumbered_quantity,0)), 0) ,0) > 0 ;
77
78 -- Cursor to check if the BPA should be encumbered.
79 CURSOR c_chk_bpa_enc (p_po_header_id NUMBER)
80 IS
81 SELECT encumbrance_required_flag
82 FROM po_headers
83 WHERE po_header_id = p_po_header_id;
84 --
85 -- PUBLIC ROUTINES
86 --
87 --
88
89 -- *************************************************************************
90 -- Get_Fiscal_Year
91 -- *************************************************************************
92
93 FUNCTION Get_Fiscal_Year(p_date IN DATE,
94 p_sob_id IN NUMBER)
95 RETURN number IS
96
97 -- Define cursor to extract the fiscal year for p_date
98
99 CURSOR c_fiscal_year(p_sob_id NUMBER) IS
100 SELECT period_year
101 FROM gl_periods gp,
102 gl_sets_of_books gsob
103 WHERE gp.period_set_name = gsob.period_set_name
104 AND gp.period_type = gsob.accounted_period_type
105 AND trunc(p_date) BETWEEN trunc(gp.start_date)
106 AND trunc(gp.end_date)
107 AND gsob.set_of_books_id = p_sob_id;
108
109 -- Define local variables
110
111 l_fiscal_year NUMBER;
112
113 -- Define exceptions
114
115 e_fiscal_year_not_found EXCEPTION;
116
117 BEGIN
118
119 -- Get the fiscal year
120
121 OPEN c_fiscal_year(p_sob_id);
122
123 IF (c_fiscal_year%NOTFOUND) THEN
124 RAISE e_fiscal_year_not_found;
125 END IF;
126
127 FETCH c_fiscal_year INTO l_fiscal_year;
128
129 CLOSE c_fiscal_year;
130
131 RETURN l_fiscal_year;
132
133 EXCEPTION
134 WHEN e_fiscal_year_not_found THEN
135 close c_fiscal_year;
136 l_fiscal_year := NULL;
137 RETURN l_fiscal_year;
138
139 END Get_Fiscal_Year;
140
141 -- *************************************************************************
142 -- Is_CBC_enabled
143 -- *************************************************************************
144
145 Procedure is_cbc_enabled(p_api_version IN NUMBER
146 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
147 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
148 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
149 ,x_return_status OUT NOCOPY VARCHAR2
150 ,x_msg_count OUT NOCOPY NUMBER
151 ,x_msg_data OUT NOCOPY VARCHAR2
152 ,x_cbc_enabled OUT NOCOPY VARCHAR2 )
153 IS
154 CURSOR c_is_cbc_on IS
155 SELECT cbc_po_enable
156 FROM igc_cc_bc_enable a,
157 financials_system_parameters b
158 WHERE a.set_of_books_id = b.set_of_books_id;
159
160 l_api_version CONSTANT NUMBER := 1.0;
161 l_api_name CONSTANT VARCHAR2(30) := 'IS_CBC_ENABLED';
162 l_cbc_enable VARCHAR2(1);
163
164 BEGIN
165
166 -- Initialize package variable
167 igc_cbc_po_grp.g_is_cbc_po_enabled := 'N';
168
169 -- Standard call to check for call compatibility
170
171 IF (NOT FND_API.Compatible_API_Call(l_api_version
172 ,p_api_version
173 ,l_api_name
174 ,G_PKG_NAME))
175 THEN
176 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
177 END IF;
178
179
180 -- Check p_init_msg_list
181
182 IF FND_API.to_Boolean(p_init_msg_list) THEN
183
184 FND_MSG_PUB.initialize;
185
186 END IF;
187
188 -- Initialize API return status to success
189
190 x_return_status := FND_API.G_RET_STS_SUCCESS;
191
192 OPEN c_is_cbc_on;
193 FETCH c_is_cbc_on INTO l_cbc_enable;
194 CLOSE c_is_cbc_on;
195
196 IF NVL(l_cbc_enable,'N')= 'N' THEN
197 x_cbc_enabled := 'N';
198 -- Also set the database package variable
199 igc_cbc_po_grp.g_is_cbc_po_enabled := 'N';
200 ELSE
201 x_cbc_enabled := 'Y';
202 igc_cbc_po_grp.g_is_cbc_po_enabled := 'Y';
203 END IF;
204
205 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
206 p_data => x_msg_data);
207
208 EXCEPTION
209 WHEN NO_DATA_FOUND THEN
210 x_cbc_enabled := 'N';
211 igc_cbc_po_grp.g_is_cbc_po_enabled := 'N';
212 IF c_is_cbc_on%ISOPEN
213 THEN
214 CLOSE c_is_cbc_on;
215 END IF;
216
217 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
218 IF c_is_cbc_on%ISOPEN
219 THEN
220 CLOSE c_is_cbc_on;
221 END IF;
222 x_cbc_enabled := 'N';
223 igc_cbc_po_grp.g_is_cbc_po_enabled := 'N';
224 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
225 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
226 p_data => x_msg_data);
227
228
229 WHEN OTHERS THEN
230 IF c_is_cbc_on%ISOPEN
231 THEN
232 CLOSE c_is_cbc_on;
233 END IF;
234
235 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
236 x_cbc_enabled := 'N';
237 igc_cbc_po_grp.g_is_cbc_po_enabled := 'N';
238
239 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
240 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name);
241 END IF;
242
243 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
244 p_data => x_msg_data);
245 END is_cbc_enabled;
246
247 -- *************************************************************************
248 -- cbc_po_enabled_flag
249 -- *************************************************************************
250 -- This function returns the value stored in variable is_cbc_po_enabled
251 -- so that it can be used by forms.
252 FUNCTION cbc_po_enabled_flag
253 RETURN VARCHAR2
254 IS
255
256 BEGIN
257 RETURN Nvl(igc_cbc_po_grp.g_is_cbc_po_enabled,'N');
258
259 END;
260
261 -- *************************************************************************
262 -- set_fundchk_cancel_flag
263 -- *************************************************************************
264 -- Procedure to set the package variable to the forms and librarires
265 PROCEDURE set_fundchk_cancel_flag (p_value IN VARCHAR2)
266 IS
267 BEGIN
268
269 igc_cbc_po_grp.g_fundchk_cancel_flag := p_value;
270
271 END set_fundchk_cancel_flag;
272
273
274 -- *************************************************************************
275 -- fundchk_cancel_flag
276 -- *************************************************************************
277 -- This function returns the value stored in variable g_fundchk_cancel_flag
278 -- so that it can be used by forms.
279 FUNCTION fundchk_cancel_flag
280 RETURN VARCHAR2
281 IS
282
283 BEGIN
284 RETURN Nvl(igc_cbc_po_grp.g_fundchk_cancel_flag,'N');
285
286 END;
287
288 -- *************************************************************************
289 -- cbc_header_validations
290 -- *************************************************************************
291
292 -- This procedure is used to do single year validations which will stop users
293 -- from creating documents with distributions spanning multiple fiscal years
294 -- therefore when documents are created the system must check whether all GL
295 -- dates are in the same fiscal year or not.
296
297 PROCEDURE cbc_header_validations(
298 p_api_version IN NUMBER
299 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
300 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
301 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
302 ,x_return_status OUT NOCOPY VARCHAR2
303 ,x_msg_count OUT NOCOPY NUMBER
304 ,x_msg_data OUT NOCOPY VARCHAR2
305 ,p_document_id IN NUMBER
306 ,p_document_type IN VARCHAR2
307 ,p_document_sub_type IN VARCHAR2) IS
308
309 l_api_version CONSTANT NUMBER := 1.0;
310 l_api_name CONSTANT VARCHAR2(30) := 'CBC_HEADER_VALIDATIONS';
311
312 l_sob_id financials_system_parameters.set_of_books_id%TYPE;
313 l_document_id NUMBER := to_number(p_document_id);
314 l_max_gl_date po_distributions.gl_encumbered_date%TYPE;
315 l_min_gl_date po_distributions.gl_encumbered_date%TYPE;
316 l_max_fiscal_year gl_periods.period_year%TYPE;
317 l_min_fiscal_year gl_periods.period_year%TYPE;
318 l_req_encumbrance_flag financials_system_parameters.req_encumbrance_flag%TYPE;
319 l_purch_encumbrance_flag financials_system_parameters.purch_encumbrance_flag%TYPE;
320 e_document_not_found EXCEPTION;
321
322 BEGIN
323
324 -- standard call to check for call compatibility.
325
326 IF (NOT FND_API.Compatible_API_Call( l_api_version
327 ,p_api_version
328 ,l_api_name
329 ,G_PKG_NAME))
330 THEN
331 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
332 END IF;
333
334 -- check p_init_msg_list
335
336 IF FND_API.to_Boolean(p_init_msg_list) THEN
337 FND_MSG_PUB.initialize;
338 END IF;
339
340 -- initialize API return status to success
341
342 x_return_status := FND_API.G_RET_STS_SUCCESS;
343
344 -- Find the set of books id and encumbrance flags for requisitions and purchasing
345
346 SELECT set_of_books_id,req_encumbrance_flag,purch_encumbrance_flag
347 INTO l_sob_id,l_req_encumbrance_flag,l_purch_encumbrance_flag
348 FROM financials_system_parameters;
349
350
351 IF (p_document_type = 'PO')
352 AND p_document_sub_type IN ('STANDARD','PLANNED')
353 AND NVL(l_purch_encumbrance_flag,'N')='Y' THEN
354
355 -- Get the minimum and maximum gl dates for the PO distributions
356
357 OPEN c_po_dates(l_document_id);
358 FETCH c_po_dates INTO l_max_gl_date, l_min_gl_date;
359 CLOSE c_po_dates;
360
361 ELSIF (p_document_type LIKE 'REQ%')
362 AND NVL(l_req_encumbrance_flag,'N')='Y' THEN
363
364 -- Get the minimum and maximum gl dates for the requisition
365 -- distributions
366
367 OPEN c_req_dates(l_document_id);
368 FETCH c_req_dates INTO l_max_gl_date, l_min_gl_date;
369 CLOSE c_req_dates;
370
371 ELSIF (p_document_type LIKE 'REL%')
372 AND p_document_sub_type IN ('SCHEDULED','BLANKET')
373 AND NVL(l_purch_encumbrance_flag,'N') = 'Y' THEN
374
375 -- Get the minimum and maximum gl dates for the release
376 -- distributions
377
378 OPEN c_rel_dates(l_document_id);
379 FETCH c_rel_dates INTO l_max_gl_date, l_min_gl_date;
380 CLOSE c_rel_dates;
381
382 ELSE
383 -- we are not interested in this type of document therefore return successes
384 Return;
385
386 END IF;
387
388
389 -- Get the fiscal year for the min and max gl dates
390
391 l_max_fiscal_year :=
392 igc_cbc_po_grp.get_fiscal_year(l_max_gl_date,l_sob_id);
393 l_min_fiscal_year :=
394 igc_cbc_po_grp.get_fiscal_year(l_min_gl_date,l_sob_id);
395
396
397 -- The fiscal years are not the same then return error.
398 IF (l_max_fiscal_year <> l_min_fiscal_year) THEN
399
400 FND_MESSAGE.Set_Name('IGC','IGC_MULT_FISCAL_YEARS');
401 FND_MSG_PUB.Add;
402 x_return_status := FND_API.G_RET_STS_ERROR;
403
404 END IF;
405
406 FND_MSG_PUB.Count_and_Get( p_count => x_msg_count
407 ,p_data => x_msg_data);
408
409 EXCEPTION
410 WHEN e_document_not_found THEN
411 fnd_message.set_name('IGC','IGC_DOCUMENT_NOT_FOUND');
412 fnd_message.set_token('DOC_ID',p_document_id);
413 FND_MSG_PUB.Add;
414 x_return_status := FND_API.G_RET_STS_ERROR;
415 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
416 ,p_data => x_msg_data);
417
418 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
419 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
420 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
421 ,p_data => x_msg_data);
422
423 WHEN OTHERS THEN
424 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
425
426 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
427 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME
428 ,l_api_name);
429 END IF;
430
431 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
432 ,p_data => x_msg_data);
433
434 END cbc_header_validations;
435
436
437 -- *************************************************************************
438 -- valid_cbc_acct_date
439 -- *************************************************************************
440 -- This procedure validates the CBC Accounting Date.
441
442 PROCEDURE valid_cbc_acct_date( p_api_version IN NUMBER
443 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
444 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
445 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
446 ,x_return_status OUT NOCOPY VARCHAR2
447 ,x_msg_count OUT NOCOPY NUMBER
448 ,x_msg_data OUT NOCOPY VARCHAR2
449 ,p_document_id IN NUMBER
450 ,p_document_type IN VARCHAR2
451 ,p_document_sub_type IN VARCHAR2
452 ,p_cbc_acct_date IN DATE) IS
453
454 l_api_version CONSTANT NUMBER := 1.0;
455 l_api_name CONSTANT VARCHAR2(30) := 'Valid_CBC_Acct_Date';
456 l_sob_id financials_system_parameters.set_of_books_id%TYPE;
457 l_req_encumbrance_flag financials_system_parameters.req_encumbrance_flag%TYPE;
458 l_purch_encumbrance_flag financials_system_parameters.purch_encumbrance_flag%TYPE;
459 l_fiscal_year NUMBER;
460 l_prev_fiscal_year NUMBER;
461 l_dist_max_fiscal_year NUMBER;
462 l_dist_min_fiscal_year NUMBER;
463 l_max_req_fiscal_year NUMBER;
464 l_min_req_fiscal_year NUMBER;
465 l_gl_prd_sts VARCHAR2(1);
466 l_po_prd_sts VARCHAR2(1);
467 l_prev_cbc_acct_date DATE;
468 l_max_gl_date po_distributions.gl_encumbered_date%TYPE;
469 l_min_gl_date po_distributions.gl_encumbered_date%TYPE;
470 l_max_cbc_acc_date po_requisition_headers.cbc_accounting_date%TYPE;
471 l_min_cbc_acc_date po_requisition_headers.cbc_accounting_date%TYPE;
472 l_po_cbc_acct_date DATE;
473
474 -- Added for PRC.FP.J, 3173178
475 l_max_bpa_fiscal_year NUMBER;
476 l_min_bpa_fiscal_year NUMBER;
477 l_max_bpa_accounting_date po_headers.cbc_accounting_date%TYPE;
478 l_min_bpa_accounting_date po_headers.cbc_accounting_date%TYPE;
479
480 -- cursor to find the GL period status
481
482 CURSOR c_get_gl_prd_sts( p_sob_id IN NUMBER
483 ,p_date IN DATE
484 ,p_appl_name IN VARCHAR2)
485 IS
486 SELECT gps.closing_status
487 FROM gl_period_statuses gps,
488 fnd_application app
489 WHERE gps.application_id = app.application_id
490 AND app.application_short_name = p_appl_name
491 AND gps.set_of_books_id = p_sob_id
492 AND p_date BETWEEN gps.start_date AND gps.end_date
493 AND gps.adjustment_period_flag = 'N';
494
495
496 CURSOR c_bpa_dates (p_document_id IN NUMBER)
497 IS
498 SELECT MAX(pod.gl_encumbered_date),
499 MIN(pod.gl_encumbered_date)
500 FROM po_distributions pod
501 WHERE pod.po_header_id = p_document_id
502 AND pod.distribution_type = 'AGREEMENT';
503
504 l_bpa_enc_required_flag VARCHAR2(1);
505
506 BEGIN
507
508 -- Standard Call to check for call compatibility
509
510 IF (NOT FND_API.Compatible_API_Call( l_api_version
511 ,p_api_version
512 ,l_api_name
513 ,G_PKG_NAME))
514 THEN
515 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
516 END IF;
517
518 -- Check p_init_msg_list
519
520 IF FND_API.to_Boolean( p_init_msg_list)
521 THEN
522 FND_MSG_PUB.initialize;
523 END IF;
524
525 -- Initialize API return status to success
526
527 x_return_status := FND_API.G_RET_STS_SUCCESS;
528
529
530 -- Get the set of books id and encumbrance flags for requisitions and purchasing
531
532 SELECT req_encumbrance_flag
533 ,purch_encumbrance_flag
534 ,set_of_books_id
535 INTO l_req_encumbrance_flag
536 ,l_purch_encumbrance_flag
537 ,l_sob_id
538 FROM financials_system_parameters;
539
540 -- Check if we need to perform any validations
541
542 IF NOT((p_document_type = 'PO'
543 AND p_document_sub_type IN ('STANDARD','PLANNED')
544 AND NVL(l_purch_encumbrance_flag,'N') = 'Y')
545
546 OR
547
548 (p_document_type LIKE 'REQ%'
549 AND NVL(l_req_encumbrance_flag,'N') = 'Y')
550
551 OR
552
553 (p_document_type LIKE 'REL%'
554 AND p_document_sub_type IN ('SCHEDULED','BLANKET')
555 AND NVL(l_purch_encumbrance_flag,'N') = 'Y')
556
557 OR
558
559 -- Added for 3173178, BPAs will be encumbered
560 (p_document_type IN ('PA','PO')
561 AND p_document_sub_type = 'BLANKET'
562 AND Nvl( l_req_encumbrance_flag,'N') = 'Y'))
563
564 THEN
565
566 -- Accounting Date should not be validated for the above
567
568 Return;
569
570 END IF;
571
572 -- Added for 3173178, check if this particular BPA should be encumbered.
573 IF p_document_type IN ('PA','PO')
574 AND p_document_sub_type = 'BLANKET'
575 THEN
576 OPEN c_chk_bpa_enc(p_document_id);
577 FETCH c_chk_bpa_enc INTO l_bpa_enc_required_flag;
578 CLOSE c_chk_bpa_enc;
579
580 IF Nvl(l_bpa_enc_required_flag,'N') = 'N'
581 THEN
582 RETURN;
583 END IF;
584 END IF;
585
586
587 -- Ensure the accounting date is not null
588
589 IF (p_cbc_acct_date is NULL) THEN
590 FND_MESSAGE.SET_NAME('IGC','IGC_PO_ACCT_DATE_NULL');
591 FND_MSG_PUB.Add;
592 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
593 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
594 ,p_data => x_msg_data);
595 RETURN;
596 END IF;
597
598 -- Get the Fiscal year for the entered accounting date
599
600 l_fiscal_year := IGC_CBC_PO_GRP.GET_FISCAL_YEAR(p_cbc_acct_date, l_sob_id);
601
602 -- Check Fiscal year is not null
603
604 IF l_fiscal_year is NULL THEN
605
606 FND_MESSAGE.SET_NAME('IGC','IGC_FISCAL_YEAR_NOT_FOUND');
607 FND_MESSAGE.SET_TOKEN('CBC_DATE',p_cbc_acct_date);
608 FND_MSG_PUB.Add;
609 x_return_status := FND_API.G_RET_STS_ERROR;
610 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
611 ,p_data => x_msg_data);
612 Return;
613
614 END IF;
615
616 -- Get status of PO Period
617
618 OPEN c_get_gl_prd_sts( l_sob_id, p_cbc_acct_date, 'PO');
619 FETCH c_get_gl_prd_sts into l_po_prd_sts;
620 CLOSE c_get_gl_prd_sts;
621
622 -- Check the accounting date is an open purchasing period
623
624 IF l_po_prd_sts <> 'O'
625 THEN
626 FND_MESSAGE.SET_NAME('IGC','IGC_PO_PERIOD_NOT_OPEN');
627 FND_MSG_PUB.ADD;
628 x_return_status := FND_API.G_RET_STS_ERROR;
629 END IF;
630
631 -- Get status of GL Period
632
633 OPEN c_get_gl_prd_sts( l_sob_id, p_cbc_acct_date, 'SQLGL');
634 FETCH c_get_gl_prd_sts into l_gl_prd_sts;
635 CLOSE c_get_gl_prd_sts;
636
637 -- Check the accounting date is in an open or future entry GL period
638
639 IF l_gl_prd_sts NOT IN ('O','F') THEN
640
641 FND_MESSAGE.SET_NAME('IGC', 'IGC_GL_PERIOD_NOT_OPEN_OR_FE');
642 FND_MSG_PUB.ADD;
643 x_return_status := FND_API.G_RET_STS_ERROR;
644
645 END IF;
646
647 -- get the previous cbc accounting date
648
649 IF p_document_type IN ('PA', 'PO') THEN
650
651 SELECT cbc_accounting_date
652 INTO l_prev_cbc_acct_date
653 FROM po_headers
654 WHERE po_header_id = p_document_id;
655
656 ELSIF (p_document_type LIKE 'REL%') THEN
657
658 SELECT cbc_accounting_date
659 INTO l_prev_cbc_acct_date
660 FROM po_releases
661 WHERE po_release_id = p_document_id;
662
663 ELSIF (p_document_type LIKE 'REQ%') THEN
664
665 SELECT cbc_accounting_date
666 INTO l_prev_cbc_acct_date
667 FROM po_requisition_headers
668 WHERE requisition_header_id = p_document_id;
669
670 END IF;
671
672 IF l_prev_cbc_acct_date is NOT NULL THEN
673
674 -- Check that the accounting date is equal to or
675 -- later than the previous accounting date
676
677 IF (p_cbc_acct_date < l_prev_cbc_acct_date) THEN
678
679 FND_MESSAGE.SET_NAME('IGC','IGC_CC_ACCT_DATE_BEFORE_PREV');
680 FND_MESSAGE.SET_TOKEN('ACCT_DATE',p_cbc_acct_date);
681 FND_MESSAGE.SET_TOKEN('PREV_DATE',l_prev_cbc_acct_date);
682 FND_MSG_PUB.Add;
683 x_return_status := FND_API.G_RET_STS_ERROR;
684
685 END IF;
686
687 -- Get the previous accounting date fiscal year
688
689 l_prev_fiscal_year := igc_cbc_po_grp.get_fiscal_year( l_prev_cbc_acct_date, l_sob_id);
690
691 -- The fiscal year must be the same as the previous accounting date fiscal year
692
693 IF (l_prev_fiscal_year <> l_fiscal_year) THEN
694
695 FND_MESSAGE.SET_NAME('IGC','IGC_FISCAL_YEAR_DIFF');
696 FND_MESSAGE.SET_TOKEN('ACCT_DATE',p_cbc_acct_date);
697 FND_MESSAGE.SET_TOKEN('PREV_DATE',l_prev_cbc_acct_date);
698 FND_MSG_PUB.Add;
699 x_return_status := FND_API.G_RET_STS_ERROR;
700
701 END IF;
702
703 END IF;
704
705 -- Now check that this fiscal year is the same as the fiscal year
706 -- of the GL dates on all the distributions for this document.
707 -- All the distributions should be in the same fiscal year
708
709 IF p_document_type = 'PO'
710 AND p_document_sub_type IN ('STANDARD', 'PLANNED') -- Bug 3173178
711 THEN
712 -- Get the minimum and maximum gl dates for the PO distributions
713
714 OPEN c_po_dates(p_document_id);
715 FETCH c_po_dates INTO l_max_gl_date, l_min_gl_date;
716 CLOSE c_po_dates;
717
718 ELSIF p_document_type LIKE 'REQ%'
719 THEN
720 -- Get the minimum and maximum gl dates for the requisition
721 -- distributions
722
723 OPEN c_req_dates(p_document_id);
724 FETCH c_req_dates INTO l_max_gl_date, l_min_gl_date;
725 CLOSE c_req_dates;
726
727 ELSIF p_document_type LIKE 'REL%'
728 THEN
729 -- Get the minimum and maximum gl dates for the release
730 -- distributions
731
732 OPEN c_rel_dates(p_document_id);
733 FETCH c_rel_dates INTO l_max_gl_date, l_min_gl_date;
734 CLOSE c_rel_dates;
735
736 ELSIF p_document_type IN ('PA','PO')
737 AND p_document_sub_type = 'BLANKET'
738 THEN
739 OPEN c_bpa_dates (p_document_id);
740 FETCH c_bpa_dates INTO l_max_gl_date, l_min_gl_date;
741 CLOSE c_bpa_dates;
742
743 END IF;
744
745 l_dist_max_fiscal_year := igc_cbc_po_grp.get_fiscal_year(l_max_gl_date,
746 l_sob_id);
747
748 l_dist_min_fiscal_year := igc_cbc_po_grp.get_fiscal_year(l_min_gl_date,
749 l_sob_id);
750
751 -- If the accounting date fiscal year does not match the fiscal year
752 -- on the distributions then raise error.
753 IF l_fiscal_year <> l_dist_max_fiscal_year
754 AND l_fiscal_year <> l_dist_min_fiscal_year
755 THEN
756
757 FND_MESSAGE.SET_NAME('IGC','IGC_ACCT_DATE_FY_AFTER_DISTS');
758 FND_MESSAGE.SET_TOKEN('ACCT_YEAR',l_fiscal_year);
759 FND_MESSAGE.SET_TOKEN('DIST_YEAR',l_dist_max_fiscal_year);
760 FND_MSG_PUB.Add;
761 x_return_status := FND_API.G_RET_STS_ERROR;
762
763 END IF;
764
765 -- Accounting date should preferrably be before the least GL date
766 IF (p_cbc_acct_date > l_min_gl_date) THEN
767
768 FND_MESSAGE.SET_NAME('IGC','IGC_ACCT_DATE_AFTER_GL_DATES');
769 FND_MSG_PUB.Add;
770
771 END IF;
772
773
774 -- Bug 2782411 - only check for related documents if the cbc_accounting_date is null
775 -- If statement below added
776 IF l_prev_cbc_acct_date IS NULL THEN
777
778 -- If this is a purchase order or a release which was created FROM requisitions,
779 -- need to ckeck the accounting date on the associated requisitions
780 -- is lesser or equal to the accounting date being validated.
781 -- The fiscal years of all the accounting dates should be the same.
782
783 IF (p_document_type = 'PO')
784 AND p_document_sub_type IN ('STANDARD', 'PLANNED')
785 THEN
786 SELECT max(prh.cbc_accounting_date),
787 min(prh.cbc_accounting_date)
788 INTO l_max_cbc_acc_date,
789 l_min_cbc_acc_date
790 FROM po_requisition_headers prh,
791 po_requisition_lines prl,
792 po_line_locations poll
793 WHERE poll.po_header_id = p_document_id
794 AND poll.line_location_id = prl.line_location_id
795 AND prl.requisition_header_id = prh.requisition_header_id
796 AND NVL(prl.closed_code,'X') <> 'FINALLY CLOSED'
797 AND NVL(prl.cancel_flag,'N') = 'N'
798 AND NVL(poll.closed_code,'X') <> 'FINALLY CLOSED'
799 AND NVL(poll.cancel_flag,'N') = 'N'
800 AND prl.source_type_code = 'VENDOR';
801
802
803 ELSIF (p_document_type LIKE 'REL%') THEN
804
805 SELECT max(prh.cbc_accounting_date),
806 min(prh.cbc_accounting_date)
807 INTO l_max_cbc_acc_date,
808 l_min_cbc_acc_date
809 FROM po_requisition_headers prh,
810 po_requisition_lines prl,
811 po_line_locations poll
812 WHERE poll.po_release_id = p_document_id
813 AND prl.line_location_id = poll.line_location_id
814 AND prl.requisition_header_id = prh.requisition_header_id
815 AND NVL(prl.closed_code,'X') <> 'FINALLY CLOSED'
816 AND NVL(prl.cancel_flag,'N') = 'N'
817 AND NVL(poll.closed_code,'X') <> 'FINALLY CLOSED'
818 AND NVL(poll.cancel_flag,'N') = 'N'
819 AND prl.source_type_code = 'VENDOR';
820
821 END IF;
822
823 IF l_max_cbc_acc_date is NOT NULL
824 AND l_min_cbc_acc_date IS NOT NULL
825 THEN
826 IF p_cbc_acct_date < l_max_cbc_acc_date
827 THEN
828 FND_MESSAGE.SET_NAME('IGC','IGC_ACCT_DATE_BEFORE_REQ_DATE');
829 FND_MESSAGE.SET_TOKEN('ACCT_DATE',p_cbc_acct_date);
830 FND_MSG_PUB.Add;
831 x_return_status := FND_API.G_RET_STS_ERROR;
832
833 END IF;
834
835 l_max_req_fiscal_year := igc_cbc_po_grp.get_fiscal_year(
836 l_max_cbc_acc_date, l_sob_id);
837 l_min_req_fiscal_year := igc_cbc_po_grp.get_fiscal_year(
838 l_min_cbc_acc_date, l_sob_id);
839
840 -- Check if the fiscal year is the same for the accounting date on
841 -- the associated documents.
842 IF (l_fiscal_year <> l_max_req_fiscal_year) OR
843 (l_fiscal_year <> l_min_req_fiscal_year )
844 THEN
845 FND_MESSAGE.SET_NAME('IGC','IGC_ACCT_DATE_FY_DIFF_REQ');
846 FND_MESSAGE.SET_TOKEN('ACCT_YEAR',l_fiscal_year);
847 FND_MSG_PUB.Add;
848 x_return_status := FND_API.G_RET_STS_ERROR;
849 END IF;
850 END IF;
851
852 -- Will Check Releases Accounting Date with that of PO Headers,
853 -- If it is less than the cbc accounting date in PO Headers then it will be rejected.
854
855 IF p_document_type LIKE 'REL%' AND
856 p_document_sub_type IN ( 'SCHEDULED', 'BLANKET') THEN
857
858 select po.cbc_accounting_date
859 into l_po_cbc_acct_date
860 from po_headers po, po_releases por
861 where po.po_header_id = por.po_header_id
862 and por.po_release_id = p_document_id;
863
864 IF p_cbc_acct_date < l_po_cbc_acct_date THEN
865
866 FND_MESSAGE.SET_NAME('IGC','IGC_ACCT_DATE_BEFORE_PO_DATE');
867 FND_MESSAGE.SET_TOKEN('ACCT_DATE', p_cbc_acct_date);
868 FND_MSG_PUB.Add;
869 x_return_status := FND_API.G_RET_STS_ERROR;
870
871 END IF;
872
873 -- ssmales 05-Feb-2003 bug 2784922 start
874 IF l_fiscal_year <> igc_cbc_po_grp.get_fiscal_year(
875 l_po_cbc_acct_date, l_sob_id) THEN
876 FND_MESSAGE.SET_NAME('IGC','IGC_ACCT_DATE_FY_DIFF_PO');
877 FND_MESSAGE.SET_TOKEN('ACCT_DATE', l_fiscal_year);
878 FND_MSG_PUB.Add;
879 x_return_status := FND_API.G_RET_STS_ERROR;
880 END IF;
881 -- ssmales 05-Feb-2003 bug 2784922 end
882
883 END IF;
884
885 -- Added for PRC.FP.J, 3173178, Start
886 -- Checks if the documents are sourced from an encumbered BPA.
887 -- In that case, the accounting date on the current document
888 -- should be in the same fiscal year as the accounting date
889 -- of the BPA and should be later than the accounting date of the BPA.
890 IF p_document_type LIKE 'REQ%'
891 THEN
892 -- Req sourced from BPA
893 SELECT MAX(poh.cbc_accounting_date),
894 MIN(poh.cbc_accounting_date)
895 INTO l_max_bpa_accounting_date,
896 l_min_bpa_accounting_date
897 FROM po_headers poh,
898 Po_requisition_lines prl
899 WHERE prl.requisition_header_id = p_document_id
900 AND prl.blanket_po_header_id = poh.po_header_id
901 AND prl.blanket_po_header_id IS NOT NULL
902 AND poh.type_lookup_code = 'BLANKET'
903 AND poh.encumbrance_required_flag = 'Y'
904 AND NVL(prl.closed_code,'X') <> 'FINALLY CLOSED'
905 AND NVL(prl.cancel_flag,'N') = 'N';
906
907 ELSIF p_document_type = 'PO'
908 AND p_document_sub_type = 'STANDARD'
909 THEN
910 -- PO sourced from BPA.
911 SELECT MAX(bpa_h.cbc_accounting_date),
912 MIN(bpa_h.cbc_accounting_date)
913 INTO l_max_bpa_accounting_date,
914 l_min_bpa_accounting_date
915 FROM po_headers bpa_h,
916 Po_lines pol,
917 Po_line_locations poll,
918 Po_distributions bpa_d
919 WHERE pol.po_header_id = p_document_id
920 AND poll.po_line_id = pol.po_line_id
921 AND NVL(pol.closed_code,'X') <> 'FINALLY CLOSED'
922 AND NVL(pol.cancel_flag,'N') = 'N'
923 AND NVL(poll.closed_code,'X') <> 'FINALLY CLOSED'
924 AND NVL(poll.cancel_flag,'N') = 'N'
925 AND pol.from_header_id IS NOT NULL
926 AND pol.from_header_id = bpa_d.po_header_id
927 AND bpa_d.distribution_type = 'AGREEMENT'
928 AND bpa_d.po_header_id = bpa_h.po_header_id
929 AND bpa_h.type_lookup_code = 'BLANKET'
930 AND bpa_h.encumbrance_required_flag = 'Y';
931
932 END IF;
933
934 IF l_max_bpa_accounting_date is NOT NULL
935 AND l_min_bpa_accounting_date IS NOT NULL
936 THEN
937 IF p_cbc_acct_date < l_max_bpa_accounting_date
938 THEN
939 FND_MESSAGE.SET_NAME('IGC', 'IGC_ACCT_DATE_BEFORE_BPA_DATE');
940 FND_MESSAGE.SET_TOKEN('ACCT_DATE', p_cbc_acct_date);
941 FND_MSG_PUB.Add;
942 X_return_Status := FND_API.G_RET_STS_ERROR;
943 END IF;
944
945 l_max_bpa_fiscal_year := igc_cbc_po_grp.get_fiscal_year
946 (l_max_bpa_accounting_date, l_sob_id);
947 l_min_bpa_fiscal_year := igc_cbc_po_grp.get_fiscal_year
948 ( l_min_bpa_accounting_date, l_sob_id);
949
950 IF (l_fiscal_year <> l_max_bpa_fiscal_year)
951 OR (l_fiscal_year <> l_min_bpa_fiscal_year)
952 THEN
953 FND_MESSAGE.SET_NAME('IGC', 'IGC_ACCT_DATE_FY_DIFF_BPA');
954 FND_MESSAGE.SET_TOKEN('ACCT_YEAR', l_fiscal_year);
955 FND_MSG_PUB.Add;
956 X_return_Status := FND_API.G_RET_STS_ERROR;
957 END IF;
958 END IF;
959
960 -- Added for PRC.FP.J, 3173178, End
961
962 -- Bug 2782411 - only check for related documents if the cbc_accounting_date is null
963 -- End If statement below added
964 END IF;
965
966 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
967 p_data => x_msg_data);
968
969 EXCEPTION
970 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
971 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
972 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
973 p_data => x_msg_data);
974
975 WHEN OTHERS THEN
976 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
977
978 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
979 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name);
980 END IF;
981
982 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
983 p_data => x_msg_data);
984
985
986 END valid_cbc_acct_date;
987
988 -- *************************************************************************
989 -- get_cbc_acct_date
990 -- *************************************************************************
991 -- This procedure returns the CBC Accounting Date that is stored in the database
992 -- If one is not found and if a default value is required it determines
993 -- and returns a default value.
994
995 PROCEDURE get_cbc_acct_date
996 (
997 p_api_version IN NUMBER,
998 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
999 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1000 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1001 x_return_status OUT NOCOPY VARCHAR2 ,
1002 x_msg_count OUT NOCOPY NUMBER ,
1003 x_msg_data OUT NOCOPY VARCHAR2 ,
1004 p_document_id IN NUMBER,
1005 p_document_type IN VARCHAR2,
1006 p_document_sub_type IN VARCHAR2,
1007 p_default IN VARCHAR2,
1008 x_cbc_acct_date OUT NOCOPY DATE
1009 ) AS
1010
1011 l_api_version CONSTANT NUMBER := 1.0 ;
1012 l_api_name CONSTANT VARCHAR2(30) := 'Get_CBC_Acct_Date' ;
1013
1014 e_igc_doc_not_found EXCEPTION;
1015
1016 l_accounting_date DATE := NULL;
1017 l_return_status VARCHAR2(1) ;
1018 l_req_enc_flag VARCHAR2(1) ;
1019 l_pur_enc_flag VARCHAR2(1) ;
1020 l_sob_id NUMBER;
1021
1022 -- Cursor to get accounting date FROM PO headers for this document
1023 Cursor c_get_po_date IS
1024 SELECT cbc_accounting_date
1025 FROM po_headers
1026 WHERE po_header_id = p_document_id ;
1027
1028 -- Cursor to get accounting date FROM PO_Req headers for this document
1029 Cursor c_get_req_date IS
1030 SELECT cbc_accounting_date
1031 FROM po_requisition_headers
1032 WHERE requisition_header_id = p_document_id ;
1033
1034 -- Cursor to get accounting date FROM PO_Rel headers for this document
1035 Cursor c_get_rel_date IS
1036 SELECT cbc_accounting_date
1037 FROM po_releases
1038 WHERE po_release_id = p_document_id ;
1039
1040 -- Cursor to get the start date of the next open period
1041 Cursor c_next_period_date(p_sob_id NUMBER) IS
1042 SELECT start_date
1043 FROM gl_period_statuses a,
1044 fnd_application b
1045 WHERE a.application_id = b.application_id
1046 AND b.application_short_name = 'PO'
1047 AND a.set_of_books_id = p_sob_id
1048 AND a.closing_status = 'O'
1049 AND a.start_date > sysdate
1050 AND a.adjustment_period_flag = 'N'
1051 order by start_date asc ;
1052
1053 -- Cursor to get the latest accounting date FROM requisitions relating to the PO
1054 -- Bug 2885953 - amended cursor below for performance enhancements
1055 -- Cursor c_max_req_date IS
1056 -- SELECT max(r.cbc_accounting_date)
1057 -- FROM po_requisition_headers r,
1058 -- po_distributions_v p
1059 -- WHERE r.requisition_header_id = p.requisition_header_id
1060 -- AND p.po_header_id = p_document_id;
1061 Cursor c_max_req_date IS
1062 SELECT max(porh.cbc_accounting_date)
1063 FROM po_requisition_headers porh,
1064 po_distributions pod,
1065 po_requisition_lines porl,
1066 po_req_distributions pord
1067 WHERE pod.po_header_id = p_document_id
1068 AND pod.req_distribution_id = pord.distribution_id(+)
1069 AND pord.requisition_line_id = porl.requisition_line_id(+)
1070 AND porl.requisition_header_id = porh.requisition_header_id;
1071
1072
1073 -- Cursor to get the latest accounting date FROM requisitons relating to the Release
1074 -- Bug 2885953 - amended cursor below for performance enhancements
1075 -- Cursor c_max_rel_req_date IS
1076 -- SELECT max(r.cbc_accounting_date)
1077 -- FROM po_requisition_headers r,
1078 -- po_distributions_v p
1079 -- WHERE r.requisition_header_id = p.requisition_header_id
1080 -- AND p.po_release_id = p_document_id;
1081 Cursor c_max_rel_req_date IS
1082 SELECT max(porh.cbc_accounting_date)
1083 FROM po_requisition_headers porh,
1084 po_distributions pod,
1085 po_requisition_lines porl,
1086 po_req_distributions pord
1087 WHERE pod.po_release_id = p_document_id
1088 AND pod.req_distribution_id = pord.distribution_id(+)
1089 AND pord.requisition_line_id = porl.requisition_line_id(+)
1090 AND porl.requisition_header_id = porh.requisition_header_id;
1091
1092
1093 -- Cursor to get the latest accounting date FROM PO's relating to the release
1094 Cursor c_rel_po_date IS
1095 SELECT poh.cbc_accounting_date
1096 FROM po_releases por,
1097 po_headers poh
1098 WHERE por.po_release_id = p_document_id
1099 AND por.po_header_id = poh.po_header_id ;
1100
1101 -- Cursor to get financial information
1102 Cursor c_fin_info IS
1103 SELECT req_encumbrance_flag,
1104 purch_encumbrance_flag,
1105 set_of_books_id
1106 FROM financials_system_parameters ;
1107
1108 -- Added for PRC.FP.J, get the accounting date for the
1109 -- Blanket Agreements for Standard Purchase Orders.
1110 Cursor c_max_bpa_po_date IS
1111 SELECT max(bpa_h.cbc_accounting_date)
1112 FROM po_headers bpa_h,
1113 Po_lines pol,
1114 Po_distributions bpa_d
1115 WHERE pol.po_header_id = p_document_id
1116 AND pol.from_header_id IS NOT NULL
1117 AND pol.from_header_id = bpa_d.po_header_id
1118 AND bpa_d.po_header_id = bpa_h.po_header_id
1119 AND bpa_d.distribution_type = 'AGREEMENT'
1120 AND bpa_h.type_lookup_code = 'BLANKET'
1121 AND bpa_h.encumbrance_required_flag = 'Y';
1122
1123 -- Get the accounting date for the backing blanket agreements
1124 -- for the requisitions.
1125 Cursor c_max_bpa_req_date IS
1126 SELECT MAX(bpa_h.cbc_accounting_date)
1127 FROM po_headers bpa_h,
1128 Po_requisition_lines prl
1129 WHERE prl.requisition_header_id = p_document_id
1130 AND prl.blanket_po_header_id = bpa_h.po_header_id
1131 AND prl.blanket_po_header_id IS NOT NULL
1132 AND bpa_h.type_lookup_code = 'BLANKET'
1133 AND bpa_h.encumbrance_required_flag = 'Y'
1134 AND NVL(prl.closed_code,'X') <> 'FINALLY CLOSED'
1135 AND NVL(prl.cancel_flag,'N') = 'N';
1136
1137 l_bpa_enc_required_flag VARCHAR2(1);
1138 l_bpa_accounting_date DATE;
1139 l_req_accounting_date DATE;
1140
1141 BEGIN
1142
1143 -- Standard call to check for call compatibility.
1144 IF NOT FND_API.Compatible_API_Call(
1145 l_api_version,
1146 p_api_version,
1147 l_api_name,
1148 G_PKG_NAME
1149 )
1150 THEN
1151 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1152 END IF ;
1153
1154 -- Check p_init_msg_list
1155 IF FND_API.To_Boolean(p_init_msg_list)
1156 THEN
1157 FND_MSG_PUB.Initialize ;
1158 END IF ;
1159
1160 -- Initialize API return status to success
1161 x_return_status := FND_API.G_RET_STS_SUCCESS ;
1162 l_return_status := FND_API.G_RET_STS_SUCCESS ;
1163
1164 OPEN c_fin_info ;
1165 FETCH c_fin_info INTO l_req_enc_flag,
1166 l_pur_enc_flag,
1167 l_sob_id ;
1168 CLOSE c_fin_info ;
1169
1170 IF NOT((p_document_type = 'PO'
1171 AND p_document_sub_type IN ('STANDARD','PLANNED')
1172 AND NVL(l_pur_enc_flag,'N') = 'Y')
1173 OR
1174 (p_document_type LIKE 'REQ%'
1175 AND NVL(l_req_enc_flag,'N') = 'Y')
1176 OR
1177 (p_document_type LIKE 'REL%'
1178 AND p_document_sub_type IN ('SCHEDULED','BLANKET')
1179 AND NVL(l_pur_enc_flag,'N') = 'Y')
1180 OR
1181 (p_document_type IN ( 'PA', 'PO')
1182 AND p_document_sub_type = 'BLANKET'
1183 AND NVL(l_req_enc_flag,'N') = 'Y'))
1184 THEN
1185 -- Accounting Date should not be defaulted
1186 RETURN ;
1187 END IF ;
1188
1189 -- Added for 3173178, check if this particular BPA should be encumbered.
1190 IF p_document_type IN ('PA','PO')
1191 AND p_document_sub_type = 'BLANKET'
1192 THEN
1193 OPEN c_chk_bpa_enc(p_document_id);
1194 FETCH c_chk_bpa_enc INTO l_bpa_enc_required_flag;
1195 CLOSE c_chk_bpa_enc;
1196
1197 IF Nvl(l_bpa_enc_required_flag,'N') = 'N'
1198 THEN
1199 RETURN;
1200 END IF;
1201 END IF;
1202
1203 -- Get the existing Accounting Date
1204 IF p_document_type IN ('PO', 'PA') -- Bug 3173178
1205 THEN
1206
1207 OPEN c_get_po_date ;
1208 FETCH c_get_po_date INTO l_accounting_date ;
1209 IF c_get_po_date%NOTFOUND
1210 THEN
1211 CLOSE c_get_po_date ;
1212 RAISE e_igc_doc_not_found ;
1213 END IF ;
1214 ELSIF p_document_type LIKE 'REQ%'
1215 THEN
1216 OPEN c_get_req_date ;
1217 FETCH c_get_req_date INTO l_accounting_date ;
1218 IF c_get_req_date%NOTFOUND
1219 THEN
1220 CLOSE c_get_req_date ;
1221 RAISE e_igc_doc_not_found ;
1222 END IF ;
1223 ELSIF p_document_type LIKE 'REL%'
1224 THEN
1225 OPEN c_get_rel_date ;
1226 FETCH c_get_rel_date INTO l_accounting_date ;
1227 IF c_get_rel_date%NOTFOUND
1228 THEN
1229 CLOSE c_get_rel_date ;
1230 RAISE e_igc_doc_not_found ;
1231 END IF ;
1232 END IF ;
1233
1234 -- If we need to provide a default accounting date, then try and get a valid accounting date
1235 IF p_default = 'Y'
1236 THEN
1237 igc_cbc_po_grp.valid_cbc_acct_date(
1238 p_api_version => 1.0,
1239 p_init_msg_list => FND_API.G_FALSE,
1240 p_commit => FND_API.G_FALSE,
1241 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1242 x_return_status => l_return_status,
1243 x_msg_count => x_msg_count,
1244 x_msg_data => x_msg_data,
1245 p_document_id => p_document_id,
1246 p_document_type => p_document_type,
1247 p_document_sub_type => p_document_sub_type,
1248 p_cbc_acct_date => l_accounting_date
1249 ) ;
1250
1251 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
1252 THEN
1253 -- If date is not valid, get the maximum Acct date FROM related
1254 -- backing agreements or requisitions
1255 IF p_document_type LIKE 'REQ%'
1256 THEN
1257 -- Added for PRC.FP.J, 3173178
1258 -- As Requisitions can now have backing BPAs
1259 l_accounting_date := NULL;
1260 OPEN c_max_bpa_req_date ;
1261 FETCH c_max_bpa_req_date INTO l_accounting_date ;
1262 CLOSE c_max_bpa_req_date ;
1263
1264 ELSIF p_document_type = 'PO'
1265 THEN
1266 -- Added for PRC.FP.J, 3173178
1267 -- Get the accounting date from the BPA and requisition
1268 -- and then use the one which is the greatest of the two
1269 l_accounting_date := NULL;
1270 l_req_accounting_date := NULL;
1271 l_bpa_accounting_date := NULL;
1272 IF p_document_sub_type = 'STANDARD'
1273 THEN
1274 OPEN c_max_bpa_po_date ;
1275 FETCH c_max_bpa_po_date INTO l_bpa_accounting_date ;
1276 CLOSE c_max_bpa_po_date ;
1277 END IF;
1278
1279 -- Check if the accounting date is available from Requisitions
1280 -- This is true for planned or standard POs
1281 -- End of code added for PRC.FP.J, 3173178
1282 OPEN c_max_req_date ;
1283 FETCH c_max_req_date INTO l_req_accounting_date ;
1284 CLOSE c_max_req_date ;
1285
1286 l_accounting_date := Nvl(Nvl(greatest (l_req_accounting_date,
1287 l_bpa_accounting_date),
1288 l_req_accounting_date),
1289 l_bpa_accounting_date);
1290
1291 ELSIF p_document_type LIKE 'REL%'
1292 -- AND p_document_sub_type = 'SCHEDULED'
1293 THEN
1294 l_accounting_date := NULL;
1295 l_req_accounting_date := NULL;
1296
1297 -- Check if accounting date available from Planned PO related
1298 -- to this Release
1299 -- Modified for PRC.FP.J, as now even a Blanket PO can have
1300 -- accounting date
1301 OPEN c_rel_po_date ;
1302 FETCH c_rel_po_date INTO l_accounting_date ;
1303 CLOSE c_rel_po_date ;
1304
1305 IF p_document_sub_type = 'BLANKET'
1306 THEN
1307 -- Check if accounting date available FROM Req's
1308 -- related to this Release
1309 OPEN c_max_rel_req_date ;
1310 FETCH c_max_rel_req_date INTO l_req_accounting_date ;
1311 CLOSE c_max_rel_req_date ;
1312
1313 -- Choose the greatest between the accounting date on the BPA
1314 -- and the one on the requisition
1315 l_accounting_date := Nvl(Nvl(greatest (l_req_accounting_date,
1316 l_accounting_date),
1317 l_req_accounting_date),
1318 l_accounting_date);
1319
1320 END IF;
1321 END IF ;
1322
1323 -- Validate this accounting Date
1324 igc_cbc_po_grp.valid_cbc_acct_date(
1325 p_api_version => 1.0,
1326 p_init_msg_list => FND_API.G_FALSE,
1327 p_commit => FND_API.G_FALSE,
1328 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1329 x_return_status => l_return_status,
1330 x_msg_count => x_msg_count,
1331 x_msg_data => x_msg_data,
1332 p_document_id => p_document_id,
1333 p_document_type => p_document_type,
1334 p_document_sub_type => p_document_sub_type,
1335 p_cbc_acct_date => l_accounting_date
1336 ) ;
1337
1338 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
1339 THEN
1340 -- Accounting Date not found, try using system date
1341 l_accounting_date := TRUNC(sysdate) ;
1342 igc_cbc_po_grp.valid_cbc_acct_date(
1343 p_api_version => 1.0,
1344 p_init_msg_list => FND_API.G_FALSE,
1345 p_commit => FND_API.G_FALSE,
1346 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1347 x_return_status => l_return_status,
1348 x_msg_count => x_msg_count,
1349 x_msg_data => x_msg_data,
1350 p_document_id => p_document_id,
1351 p_document_type => p_document_type,
1352 p_document_sub_type => p_document_sub_type,
1353 p_cbc_acct_date => l_accounting_date
1354 ) ;
1355
1356 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
1357 THEN
1358 -- Sysdate is not valid, so get the first date of next open period
1359 OPEN c_next_period_date(l_sob_id) ;
1360 FETCH c_next_period_date INTO l_accounting_date ;
1361 IF c_next_period_date%FOUND
1362 THEN
1363 igc_cbc_po_grp.valid_cbc_acct_date(
1364 p_api_version => 1.0,
1365 p_init_msg_list => FND_API.G_FALSE,
1366 p_commit => FND_API.G_FALSE,
1367 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1368 x_return_status => l_return_status,
1369 x_msg_count => x_msg_count,
1370 x_msg_data => x_msg_data,
1371 p_document_id => p_document_id,
1372 p_document_type => p_document_type,
1373 p_document_sub_type => p_document_sub_type,
1374 p_cbc_acct_date => l_accounting_date
1375 ) ;
1376 END IF ; -- c_next_period_date%FOUND
1377 CLOSE c_next_period_date ;
1378 END IF ; -- sysdate return_status <> Success
1379 END IF ; -- requisition date return status <> Success
1380 END IF ; -- cbc accounting date return status <> Success
1381 END IF ; -- p_default = 'Y'
1382
1383 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1384 x_cbc_acct_date := NULL ;
1385 ELSE
1386 x_cbc_acct_date := l_accounting_date ;
1387 END IF ;
1388
1389 FND_MSG_PUB.COUNT_AND_GET(
1390 p_count => x_msg_count,
1391 p_data => x_msg_data
1392 ) ;
1393
1394
1395
1396 EXCEPTION
1397 WHEN e_igc_doc_not_found THEN
1398 x_return_status := FND_API.G_RET_STS_ERROR ;
1399 FND_MESSAGE.SET_NAME('IGC','IGC_DOCUMENT_NOT_FOUND') ;
1400 FND_MESSAGE.SET_TOKEN('DOC_ID',p_document_id);
1401 FND_MSG_PUB.ADD ;
1402 FND_MSG_PUB.COUNT_AND_GET(
1403 p_count => x_msg_count,
1404 p_data => x_msg_data
1405 ) ;
1406
1407 WHEN OTHERS THEN
1408 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1409 IF FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1410 THEN
1411 FND_MSG_PUB.ADD_EXC_MSG(
1412 G_PKG_NAME,
1413 l_api_name
1414 ) ;
1415 END IF ;
1416 FND_MSG_PUB.COUNT_AND_GET(
1417 p_count => x_msg_count,
1418 p_data => x_msg_data
1419 ) ;
1420
1421 END get_cbc_acct_date ;
1422
1423
1424 -- *************************************************************************
1425 -- UPDATE_cbc_acct_date
1426 -- *************************************************************************
1427 -- Tbis procedure updates the CBC Acounting Date on the PO tables
1428 -- It is called from within the PO forms.
1429
1430 PROCEDURE UPDATE_cbc_acct_date
1431 (
1432 p_api_version IN NUMBER,
1433 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1434 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1435 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1436 x_return_status OUT NOCOPY VARCHAR2 ,
1437 x_msg_count OUT NOCOPY NUMBER ,
1438 x_msg_data OUT NOCOPY VARCHAR2 ,
1439 p_document_id IN NUMBER,
1440 p_document_type IN VARCHAR2,
1441 p_document_sub_type IN VARCHAR2,
1442 p_cbc_acct_date IN DATE
1443 ) AS
1444
1445 l_api_version CONSTANT NUMBER := 1.0 ;
1446 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_cbc_acct_date' ;
1447 l_req_enc_flag VARCHAR2(1) ;
1448 l_pur_enc_flag VARCHAR2(1) ;
1449 l_sob_id NUMBER ;
1450
1451 e_igc_doc_not_found EXCEPTION;
1452
1453 -- Cursor to get financial information
1454 Cursor c_fin_info IS
1455 SELECT req_encumbrance_flag,
1456 purch_encumbrance_flag,
1457 set_of_books_id
1458 FROM financials_system_parameters ;
1459
1460 l_bpa_enc_required_flag VARCHAR2(1);
1461
1462 BEGIN
1463
1464 -- Standard call to check for call compatibility.
1465 IF NOT FND_API.Compatible_API_Call(
1466 l_api_version,
1467 p_api_version,
1468 l_api_name,
1469 G_PKG_NAME
1470 )
1471 THEN
1472 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1473 END IF ;
1474
1475 -- Check p_init_msg_list
1476 IF FND_API.To_Boolean(p_init_msg_list)
1477 THEN
1478 FND_MSG_PUB.Initialize ;
1479 END IF ;
1480
1481 -- Initialize API return status to success
1482 x_return_status := FND_API.G_RET_STS_SUCCESS ;
1483
1484 OPEN c_fin_info ;
1485 FETCH c_fin_info INTO l_req_enc_flag,
1486 l_pur_enc_flag,
1487 l_sob_id ;
1488 CLOSE c_fin_info ;
1489
1490 -- Check if we should be storing the date
1491 IF NOT((p_document_type = 'PO'
1492 AND p_document_sub_type IN ('STANDARD','PLANNED')
1493 AND NVL(l_pur_enc_flag,'N') = 'Y')
1494 OR
1495 (p_document_type LIKE 'REQ%'
1496 AND NVL(l_req_enc_flag,'N') = 'Y')
1497 OR
1498 (p_document_type LIKE 'REL%'
1499 AND p_document_sub_type IN ('SCHEDULED','BLANKET')
1500 AND NVL(l_pur_enc_flag,'N') = 'Y')
1501 OR
1502 (p_document_type IN ('PA', 'PO')
1503 AND p_document_sub_type = 'BLANKET'
1504 AND NVL(l_req_enc_flag,'N') = 'Y'))
1505 THEN
1506 -- Accounting date should not be updated.
1507 RETURN ;
1508 END IF ;
1509
1510 -- Added for 3173178, check if this particular BPA should be encumbered.
1511 IF p_document_type IN ('PA','PO')
1512 AND p_document_sub_type = 'BLANKET'
1513 THEN
1514 OPEN c_chk_bpa_enc(p_document_id);
1515 FETCH c_chk_bpa_enc INTO l_bpa_enc_required_flag;
1516 CLOSE c_chk_bpa_enc;
1517
1518 IF Nvl(l_bpa_enc_required_flag,'N') = 'N'
1519 THEN
1520 RETURN;
1521 END IF;
1522 END IF;
1523
1524 IF p_document_type IN ('PA', 'PO')
1525 THEN
1526 -- Bug 2885953 added if statement and removed nvl from update for performance enhancement
1527 IF p_cbc_acct_date IS NOT NULL THEN
1528 UPDATE po_headers
1529 -- SET cbc_accounting_date = NVL(p_cbc_acct_date, cbc_accounting_date)
1530 SET cbc_accounting_date = p_cbc_acct_date
1531 WHERE po_header_id = p_document_id ;
1532
1533 IF SQL%ROWCOUNT = 0
1534 THEN
1535 RAISE e_igc_doc_not_found ;
1536 END IF ;
1537 END IF ;
1538
1539 ELSIF (p_document_type LIKE 'REL%')
1540 THEN
1541 -- Bug 2885953 added if statement and removed nvl from update for performance enhancement
1542 IF p_cbc_acct_date IS NOT NULL THEN
1543 UPDATE po_releases
1544 -- SET cbc_accounting_date = NVL(p_cbc_acct_date, cbc_accounting_date)
1545 SET cbc_accounting_date = p_cbc_acct_date
1546 WHERE po_release_id = p_document_id ;
1547
1548 IF SQL%ROWCOUNT = 0
1549 THEN
1550 RAISE e_igc_doc_not_found ;
1551 END IF ;
1552 END IF ;
1553
1554 ELSIF (p_document_type LIKE 'REQ%')
1555 THEN
1556 -- Bug 2885953 added if statement and removed nvl from update for performance enhancement
1557 IF p_cbc_acct_date IS NOT NULL THEN
1558 UPDATE po_requisition_headers
1559 -- SET cbc_accounting_date = NVL(p_cbc_acct_date, cbc_accounting_date)
1560 SET cbc_accounting_date = p_cbc_acct_date
1561 WHERE requisition_header_id = p_document_id ;
1562
1563 IF SQL%ROWCOUNT = 0
1564 THEN
1565 RAISE e_igc_doc_not_found ;
1566 END IF ;
1567 END IF ;
1568 END IF ;
1569
1570 IF FND_API.To_Boolean(p_commit)
1571 THEN
1572 COMMIT WORK ;
1573 END IF ;
1574
1575 FND_MSG_PUB.Count_and_Get(
1576 p_count => x_msg_count,
1577 p_data => x_msg_data
1578 ) ;
1579
1580 EXCEPTION
1581
1582 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1583 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1584 FND_MSG_PUB.COUNT_AND_GET(
1585 p_count => x_msg_count,
1586 p_data => x_msg_data
1587 ) ;
1588
1589 WHEN e_igc_doc_not_found THEN
1590 x_return_status := FND_API.G_RET_STS_ERROR ;
1591 FND_MESSAGE.SET_NAME('IGC','IGC_DOCUMENT_NOT_FOUND') ;
1592 FND_MSG_PUB.ADD ;
1593 FND_MSG_PUB.COUNT_AND_GET(
1594 p_count => x_msg_count,
1595 p_data => x_msg_data
1596 ) ;
1597
1598 WHEN OTHERS THEN
1599 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1600 IF FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1601 THEN
1602 FND_MSG_PUB.ADD_EXC_MSG(
1603 G_PKG_NAME,
1604 l_api_name
1605 ) ;
1606 END IF ;
1607 FND_MSG_PUB.COUNT_AND_GET(
1608 p_count => x_msg_count,
1609 p_data => x_msg_data
1610 ) ;
1611
1612 END UPDATE_cbc_acct_date ;
1613
1614
1615 -- *************************************************************************
1616 -- gl_date_roll_forward
1617 -- *************************************************************************
1618 -- This procedure will be called when documents are being cancelled.
1619 -- It is called from the PO routine in pocca.lpc
1620 -- For Documents which have backing requisitions, and which have not
1621 -- been moved forward by the Year End process, this process moves
1622 -- the GL Date for the reinstated requisition line.
1623
1624 PROCEDURE gl_date_roll_forward
1625 (
1626 p_api_version IN NUMBER,
1627 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1628 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1629 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1630 x_return_status OUT NOCOPY VARCHAR2 ,
1631 x_msg_count OUT NOCOPY NUMBER ,
1632 x_msg_data OUT NOCOPY VARCHAR2 ,
1633 p_document_id IN VARCHAR2,
1634 p_document_type IN VARCHAR2,
1635 p_document_sub_type IN VARCHAR2,
1636 p_line_id IN VARCHAR2 := NULL,
1637 p_line_location_id IN VARCHAR2 := NULL,
1638 p_action_date IN DATE,
1639 p_cancel_req IN VARCHAR2
1640 ) IS
1641
1642 l_api_version CONSTANT NUMBER := 1.0;
1643 l_api_name CONSTANT VARCHAR2(30) := 'GL_Date_Roll_Forward';
1644
1645 Cursor c_PO_dists_po IS
1646 Select p.req_distribution_id,
1647 p.gl_encumbered_date PO_GL_Date,
1648 p.set_of_books_id sob_id,
1649 h.cbc_accounting_date PO_Acct_Date
1650 From PO_Distributions_V p,
1651 PO_Headers h
1652 Where p.po_header_id = p_document_id
1653 And p.po_line_id = nvl(p_line_id, p.po_line_id)
1654 And p.line_location_id = nvl(p_line_location_id, p.line_location_id)
1655 And p.po_header_id = h.po_header_id
1656 And p.requisition_header_id is not null;
1657
1658 l_po_dists_po c_po_dists_po%rowtype;
1659
1660 -- Bug 2885953 - Performance fixes
1661 -- Cursor c_po_dists_bla_rel IS
1662 -- Select p.req_distribution_id,
1663 -- p.gl_encumbered_date rel_gl_date,
1664 -- p.set_of_books_id sob_id,
1665 -- r.cbc_accounting_date rel_acct_date
1666 -- From po_distributions_v p,
1667 -- po_releases r
1668 -- Where p.po_release_id = p_document_id
1669 -- And p.line_location_id = nvl(p_line_location_id, p.line_location_id)
1670 -- And p.po_release_id = r.po_release_id
1671 -- And p.requisition_header_id is not null;
1672 Cursor c_po_dists_bla_rel IS
1673 Select pod.req_distribution_id,
1674 pod.gl_encumbered_date rel_gl_date,
1675 pod.set_of_books_id sob_id,
1676 r.cbc_accounting_date rel_acct_date
1677 From po_distributions pod,
1678 po_releases r,
1679 po_requisition_headers porh,
1680 po_requisition_lines porl,
1681 po_req_distributions pord
1682 Where pod.po_release_id = p_document_id
1683 And pod.line_location_id = nvl(p_line_location_id, pod.line_location_id)
1684 And pod.po_release_id = r.po_release_id
1685 And porh.requisition_header_id is not null
1686 And pod.req_distribution_id = pord.distribution_id(+)
1687 And pord.requisition_line_id = porl.requisition_line_id(+)
1688 And porl.requisition_header_id = porh.requisition_header_id(+);
1689
1690 l_po_dists_bla_rel c_po_dists_bla_rel%rowtype;
1691
1692 Cursor c_po_dists_sch_rel IS
1693 Select p.gl_encumbered_date rel_gl_date,
1694 p.source_distribution_id,
1695 p.set_of_books_id sob_id,
1696 r.cbc_accounting_date rel_acct_date
1697 From po_distributions_v P,
1698 po_releases R
1699 Where p.po_release_id = p_document_id
1700 And p.line_location_id = nvl(p_line_location_id, p.line_location_id)
1701 And p.po_release_id = r.po_release_id;
1702
1703 l_po_dists_sch_rel c_po_dists_sch_rel%rowtype;
1704
1705 Cursor c_req_dists (p_req_dist_id NUMBER) IS
1706 Select gl_encumbered_date
1707 From po_req_distributions
1708 Where distribution_id = p_req_dist_id;
1709
1710 l_req_gl_date Date;
1711
1712 Cursor c_po_dists (p_source_id NUMBER) IS
1713 Select gl_encumbered_date
1714 From po_distributions
1715 Where po_distribution_id = p_source_id;
1716
1717 l_po_gl_date Date;
1718
1719 Cursor c_linked_req_dists (p_req_dist_id NUMBER) IS
1720 Select distribution_id, gl_encumbered_date
1721 From po_req_distributions
1722 Where source_req_distribution_id = p_req_dist_id;
1723
1724 l_linked_req c_linked_req_dists%rowtype;
1725
1726 l_fiscal_year Number;
1727 l_action_fiscal_year Number;
1728 l_acct_fiscal_year Number;
1729 l_po_gl_fiscal_year Number;
1730 l_rel_gl_fiscal_year Number;
1731 l_req_gl_fiscal_year Number;
1732
1733 l_req_encumbrance_flag financials_system_parameters.req_encumbrance_flag%TYPE;
1734 l_purch_encumbrance_flag financials_system_parameters.purch_encumbrance_flag%TYPE;
1735 l_passed_validation Boolean;
1736
1737 l_sob_id financials_system_parameters.set_of_books_id%TYPE;
1738
1739 Begin
1740
1741 --Standard Call to check for call compatibility
1742
1743 IF NOT FND_API.Compatible_API_Call ( l_api_version,
1744 p_api_version,
1745 l_api_name,
1746 G_PKG_NAME )
1747 THEN
1748 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1749 END IF;
1750
1751 --Check p_init_msg_list
1752
1753 IF FND_API.to_Boolean ( p_init_msg_list ) THEN
1754 FND_MSG_PUB.initialize;
1755 END IF;
1756
1757 --Initialize API return status to success
1758
1759 x_return_status := FND_API.G_RET_STS_SUCCESS;
1760
1761 --Check if Requisitions are being cancelled, if not then carry on processing
1762
1763 /*
1764 -- Bug 2784359, Commented out the entire procedure GL_Date_Rollforward as it
1765 -- causes other complications. Instead user has been recommended to cancel
1766 -- the backing requisition
1767 -- This is done in the IGILUTIL2.pld and the IGI_CBC.pld
1768
1769 IF p_cancel_req = 'Y' THEN
1770 Return;
1771 END IF;
1772
1773 SELECT set_of_books_id,req_encumbrance_flag,purch_encumbrance_flag
1774 INTO l_sob_id,l_req_encumbrance_flag,l_purch_encumbrance_flag
1775 FROM financials_system_parameters;
1776
1777
1778 --Determine current fiscal year based on sysdate
1779
1780 l_fiscal_year := igc_cbc_po_grp.get_fiscal_year(sysdate, l_sob_id);
1781
1782 -- Determine fiscal year of action date.
1783 -- If same as current year then continue processing
1784
1785 l_action_fiscal_year := igc_cbc_po_grp.get_fiscal_year(p_action_date, l_sob_id);
1786
1787 IF l_fiscal_year <> l_action_fiscal_year THEN
1788 Return;
1789 END IF;
1790
1791 --Process PO Documents
1792
1793 IF p_document_type = 'PO'
1794 AND NVL(l_purch_encumbrance_flag,'N')='Y' THEN
1795
1796 --Standard or Planned PO's only
1797
1798 IF p_document_sub_type IN ('PLANNED', 'STANDARD')
1799 THEN
1800
1801 FOR l_po_dists_po IN c_po_dists_po
1802 LOOP
1803
1804 --Initialize validation flag
1805
1806 l_passed_validation := TRUE;
1807
1808 -- Determine fiscal year of GL Date of PO Distribution.
1809 -- If different, from current year then do not proceed
1810
1811 l_po_gl_fiscal_year := igc_cbc_po_grp.get_fiscal_year(l_po_dists_po.po_gl_date, l_po_dists_po.sob_id);
1812
1813 -- If the GL Date is not in the current fiscal year then do not process.
1814 IF l_fiscal_year <> l_po_gl_fiscal_year THEN
1815 l_passed_validation := FALSE;
1816 END IF;
1817
1818 IF l_passed_validation THEN
1819
1820 --Check to see if current distribution on the requisition is obsolete
1821 -- and if a new requisition line has been created.
1822 Open c_linked_req_dists(l_po_dists_po.req_distribution_id);
1823 Fetch c_linked_req_dists into l_linked_req;
1824
1825 IF c_linked_req_dists%found THEN
1826 l_req_gl_fiscal_year := igc_cbc_po_grp.get_fiscal_year(l_linked_req.gl_encumbered_date, l_po_dists_po.sob_id);
1827
1828 -- If the requisition GL date is already in the current year then
1829 -- do not update the requisition
1830 IF l_req_gl_fiscal_year >= l_fiscal_year THEN
1831 l_passed_validation := FALSE;
1832 END IF;
1833
1834 --If validations passed, then roll forward the date
1835
1836 IF l_passed_validation THEN
1837 UPDATE po_req_distributions
1838 SET gl_encumbered_date = p_action_date
1839 WHERE distribution_id = l_linked_req.distribution_id;
1840 END IF;
1841
1842 --Continue processing if no linked requsition distribution found.
1843
1844 ELSE
1845
1846 Open c_req_dists(l_po_dists_po.req_distribution_id);
1847 Fetch c_req_dists into l_req_gl_date;
1848 Close c_req_dists;
1849 l_req_gl_fiscal_year := igc_cbc_po_grp.get_fiscal_year(l_req_gl_date, l_po_dists_po.sob_id);
1850 IF l_req_gl_fiscal_year >= l_fiscal_year THEN
1851 l_passed_validation := FALSE;
1852 END IF;
1853
1854 --If validation passed, then roll forward date.
1855
1856 IF l_passed_validation THEN
1857 UPDATE po_req_distributions
1858 SET gl_encumbered_date = p_action_date
1859 WHERE distribution_id = l_po_dists_po.req_distribution_id;
1860 END IF;
1861
1862 END IF; --req_dists%found
1863
1864 close c_linked_req_dists;
1865
1866 END IF; --validation
1867
1868 END LOOP; --FOR l_po_dists
1869
1870 END IF; --p_document_subtype IN (PLANNED, STANDARD)
1871
1872 ELSIF p_document_type LIKE 'REL%'
1873 AND NVL(l_purch_encumbrance_flag,'N')='Y' THEN
1874
1875 IF p_document_sub_type = 'SCHEDULED' THEN
1876
1877 --Loop release distributions
1878 FOR l_po_dists_sch_rel IN c_po_dists_sch_rel
1879 LOOP
1880
1881 --Initialize validation Flag
1882
1883 l_passed_validation := TRUE;
1884
1885 --Determine Fiscal Year of GL Date of Release Distribution
1886
1887 l_rel_gl_fiscal_year := igc_cbc_po_grp.get_fiscal_year(l_po_dists_sch_rel.rel_gl_date, l_po_dists_sch_rel.sob_id);
1888
1889 IF l_fiscal_year <> l_rel_gl_fiscal_year THEN
1890 l_passed_validation := FALSE;
1891 END IF;
1892
1893 IF l_passed_validation THEN
1894
1895 --Determine fiscal year of PO Distribution GL Date. If greater or equal to fiscal year
1896 --then fail.
1897
1898 Open c_po_dists(l_po_dists_sch_rel.source_distribution_id);
1899 Fetch c_po_dists into l_po_gl_date;
1900 Close c_po_dists;
1901
1902 l_po_gl_fiscal_year := igc_cbc_po_grp.get_fiscal_year(l_po_gl_date, l_po_dists_sch_rel.sob_id);
1903
1904 IF l_po_gl_fiscal_year >= l_fiscal_year THEN
1905 l_passed_validation := FALSE;
1906 END IF;
1907
1908 END IF;
1909
1910 --If all validations pass then PO dist GL Dates require rolling forward
1911
1912 IF l_passed_validation THEN
1913 UPDATE po_distributions
1914 SET gl_encumbered_date = p_action_date
1915 WHERE po_distribution_id = l_po_dists_sch_rel.source_distribution_id;
1916 END IF;
1917
1918 END LOOP;
1919
1920 ELSIF p_document_sub_type = 'BLANKET' THEN
1921
1922 --Loop through Release Distributions
1923 FOR l_po_dists_bla_rel IN c_po_dists_bla_rel
1924 LOOP
1925
1926 --Initialize validation flag
1927
1928 l_passed_validation := TRUE;
1929
1930 --Determine Fiscal Year of GL Date of Release Distributions. If different then fail
1931 --validation.
1932
1933 l_rel_gl_fiscal_year := igc_cbc_po_grp.get_fiscal_year(l_po_dists_bla_rel.rel_gl_date, l_po_dists_bla_rel.sob_id);
1934
1935 IF l_fiscal_year <> l_rel_gl_fiscal_year THEN
1936 l_passed_validation := FALSE;
1937 END IF;
1938
1939 IF l_passed_validation THEN
1940
1941 --Check to see if linked requsition distribution exists
1942
1943 Open c_linked_req_dists(l_po_dists_bla_rel.req_distribution_id);
1944 Fetch c_linked_req_dists into l_linked_req;
1945
1946 IF c_linked_req_dists%found THEN
1947
1948 l_req_gl_fiscal_year := igc_cbc_po_grp.get_fiscal_year(l_linked_req.gl_encumbered_date, l_po_dists_bla_rel.sob_id);
1949
1950 IF l_req_gl_fiscal_year >= l_fiscal_year THEN
1951 l_passed_validation := FALSE;
1952 END IF;
1953
1954 --If all validations pass, then roll forward linked requisition distribution gl_date
1955
1956 IF l_passed_validation THEN
1957 UPDATE po_req_distributions
1958 SET gl_encumbered_date = p_action_date
1959 WHERE distribution_id = l_linked_req.distribution_id;
1960 END IF;
1961
1962 --If no linked requisition distributions exist then continue
1963
1964 ELSE --c_linked_req_dists%found
1965
1966 --determine fiscal year of requsition distribution GL date. If greater or equal to
1967 --fiscal year then fail validation
1968
1969 Open c_req_dists(l_po_dists_bla_rel.req_distribution_id);
1970 Fetch c_req_dists into l_req_gl_date;
1971 Close c_req_dists;
1972
1973 l_req_gl_fiscal_year := igc_cbc_po_grp.get_fiscal_year(l_req_gl_date, l_po_dists_bla_rel.sob_id);
1974
1975 IF l_req_gl_fiscal_year >= l_fiscal_year THEN
1976 l_passed_validation := FALSE;
1977 END IF;
1978
1979 -- If all validations pass, then roll forward requisition distribution gl date
1980 IF l_passed_validation THEN
1981 UPDATE po_req_distributions
1982 SET gl_encumbered_date = p_action_date
1983 WHERE distribution_id = l_po_dists_bla_rel.req_distribution_id;
1984 END IF;
1985
1986 END IF; --linked req distributions found
1987
1988 END IF; --passed validation
1989
1990 END LOOP; --For l_po_dists_bla_rel
1991
1992 END IF; --p_document_subtype = 'BLANKET'
1993
1994 END IF; --p_document_type = 'REL'
1995
1996 IF FND_API.To_Boolean(p_commit)
1997 THEN
1998 COMMIT WORK ;
1999 END IF ;
2000
2001 */ -- Entire procedure commeted out.
2002
2003 FND_MSG_PUB.Count_And_Get
2004 ( p_count => x_msg_count,
2005 p_data => x_msg_data
2006 );
2007
2008 EXCEPTION
2009 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2010 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2011 FND_MSG_PUB.Count_And_Get
2012 ( p_count => x_msg_count,
2013 p_data => x_msg_data
2014 );
2015
2016 WHEN OTHERS THEN
2017 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2018
2019 IF FND_MSG_PUB.Check_Msg_Level
2020 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2021 THEN
2022 FND_MSG_PUB.Add_Exc_Msg
2023 ( G_PKG_NAME,
2024 l_api_name
2025 );
2026 END IF;
2027 FND_MSG_PUB.Count_And_Get
2028 ( p_count => x_msg_count,
2029 p_data => x_msg_data
2030 );
2031
2032 END gl_date_roll_forward ;
2033
2034 END igc_cbc_po_grp;
2035
[Home] [Help]