[Home] [Help]
PACKAGE BODY: APPS.IGC_CBC_PO_YEAR_END_PKG
Source
1 PACKAGE BODY IGC_CBC_PO_YEAR_END_PKG AS
2 /*$Header: IGCPOYEB.pls 120.25.12010000.2 2008/08/29 13:20:18 schakkin ship $*/
3
4
5 TYPE exception_rec_type IS RECORD
6 (document_type igc_cbc_po_process_excpts_all.document_type%TYPE,
7 document_id igc_cbc_po_process_excpts_all.document_id%TYPE,
8 line_id igc_cbc_po_process_excpts_all.line_id%TYPE,
9 line_location_id igc_cbc_po_process_excpts_all.line_location_id%TYPE,
10 distribution_id igc_cbc_po_process_excpts_all.distribution_id%TYPE,
11 exception_reason igc_cbc_po_process_excpts_all.exception_reason%TYPE,
12 exception_code igc_cbc_po_process_excpts_all.exception_code%TYPE
13 );
14
15 TYPE exception_tbl_type IS TABLE OF exception_rec_type
16 INDEX BY BINARY_INTEGER;
17
18 g_exception_tbl exception_tbl_type ;
19 g_exception_tbl_index NUMBER := 0;
20 g_pkg_name CONSTANT VARCHAR2(30):= 'IGC_CBC_PO_YEAR_END_PKG';
21
22 g_debug_mode VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
23 -- bug 2804025 ssmales 19-Feb-2003 created 3 globals below
24 g_user_id NUMBER := FND_GLOBAL.user_id ;
25 g_login NUMBER := FND_GLOBAL.login_id;
26 g_resp_id NUMBER := FND_GLOBAL.resp_id ;
27
28
29 --following variables added for bug 3199488: fnd logging changes: sdixit
30 g_debug_level number := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
31 g_state_level number := FND_LOG.LEVEL_STATEMENT;
32 g_proc_level number := FND_LOG.LEVEL_PROCEDURE;
33 g_event_level number := FND_LOG.LEVEL_EVENT;
34 g_excep_level number := FND_LOG.LEVEL_EXCEPTION;
35 g_error_level number := FND_LOG.LEVEL_ERROR;
36 g_unexp_level number := FND_LOG.LEVEL_UNEXPECTED;
37 g_path varchar2(500) := 'igc.plsql.igcpoyeb.igc_cbc_po_year_end_pkg.';
38
39 -- NOTE that in all of the following processing, the return code value signifies the following:
40 -- 0 - Processing should terminate successfully
41 -- 1 - Processing should terminate with warnings
42 -- 2 - Processing should terminate with errors
43 -- -99 - Processing should continue
44
45
46
47 -- Procedure Put_Debug_Msg
48 -- =======================
49 --
50 -- This Procedure writes debug messages to the log file
51 --
52 -- IN Parameters
53 -- -------------
54 -- p_debug_msg Message to be output to log file
55 --
56 -- OUT Parameters
57 -- --------------
58 --
59 --
60 /*modifed for 3199488 - fnd logging changes*/
61 PROCEDURE Put_Debug_Msg (
62 p_path IN VARCHAR2,
63 p_debug_msg IN VARCHAR2,
64 p_sev_level IN VARCHAR2 := g_state_level
65 ) IS
66 BEGIN
67
68 IF p_sev_level >= g_debug_level THEN
69 fnd_log.string(p_sev_level, p_path, p_debug_msg);
70 END IF;
71 END;
72 /****************
73 PROCEDURE Put_Debug_Msg (l_full_path,
74 p_debug_msg IN VARCHAR2
75 ) IS
76
77 -- Constants :
78
79 l_Return_Status VARCHAR2(1);
80 l_api_name CONSTANT VARCHAR2(30) := 'Put_Debug_Msg';
81 l_prod VARCHAR2(3) := 'IGC';
82 l_sub_comp VARCHAR2(5) := 'POYEB';
83 l_profile_name VARCHAR2(255) := 'IGC_DEBUG_LOG_DIRECTORY';
84
85 BEGIN
86
87 IGC_MSGS_PKG.Put_Debug_Msg (l_full_path,p_debug_message => p_debug_msg,
88 p_profile_log_name => l_profile_name,
89 p_prod => l_prod,
90 p_sub_comp => l_sub_comp,
91 p_filename_val => NULL,
92 x_Return_Status => l_Return_Status
93 );
94 IF (l_Return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
95 fnd_file.put_line(fnd_file.log,'g_exc_error');
96 raise FND_API.G_EXC_ERROR;
97 END IF;
98
99 RETURN;
100
101 -- --------------------------------------------------------------------
102 -- Exception handler section for the Put_Debug_Msg procedure.
103 -- --------------------------------------------------------------------
104 EXCEPTION
105
106 WHEN FND_API.G_EXC_ERROR THEN
107 RETURN;
108
109 WHEN OTHERS THEN
110 IF (FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)) THEN
111 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, l_api_name);
112 END IF;
113 RETURN;
114
115 END Put_Debug_Msg;
116 *****************/
117
118
119 -- Function Lock_Documents
120 -- =======================
121 --
122 -- This procedure locks the header document, as well as any related lines, shipments and
123 -- distributions
124 --
125 -- IN Parameters
126 -- -------------
127 -- p_document_type Type of document: PO, REQ or REL
128 -- p_document_id Id of document
129 --
130 -- OUT Parameters
131 -- --------------
132 -- Returns Fnd_Api.G_True for success, otherwise G_False
133 --
134 FUNCTION Lock_Documents(p_document_type IN VARCHAR2,
135 p_document_id IN NUMBER
136 ) RETURN VARCHAR2 AS
137
138 CURSOR c_lock_req IS
139 SELECT 'x'
140 FROM po_requisition_headers prh,
141 po_requisition_lines prl,
142 po_req_distributions prd
143 WHERE prh.requisition_header_id = p_document_id
144 AND prh.requisition_header_id = prl.requisition_header_id
145 AND prl.requisition_line_id = prd.requisition_line_id
146 FOR UPDATE NOWAIT;
147
148 -- Amended Cursor below whilst testing Relock failure
149 CURSOR c_lock_po IS
150 SELECT 'x'
151 FROM po_headers poh,
152 po_lines pol ,
153 po_line_locations poll,
154 po_distributions pod
155 WHERE poh.po_header_id = p_document_id
156 AND poh.po_header_id = pol.po_header_id
157 AND pol.po_header_id = poll.po_header_id
158 AND poll.po_header_id = pod.po_header_id
159 FOR UPDATE NOWAIT;
160
161 CURSOR c_lock_release IS
162 SELECT 'x'
163 FROM po_releases por,
164 po_line_locations poll,
165 po_distributions pod
166 WHERE por.po_release_id = p_document_id
167 AND por.po_release_id = poll.po_release_id
168 AND poll.po_release_id = pod.po_release_id
169 FOR UPDATE NOWAIT;
170
171 CURSOR c_lock_bpa IS
172 SELECT 'x'
173 FROM po_headers poh,
174 po_distributions pod
175 WHERE poh.po_header_id = p_document_id
176 AND poh.po_header_id = pod.po_header_id
177 FOR UPDATE NOWAIT;
178
179 l_lock_req c_lock_req%ROWTYPE;
180 l_lock_po c_lock_po%ROWTYPE;
181 l_lock_release c_lock_release%ROWTYPE;
182 l_lock_bpa c_lock_bpa%ROWTYPE;
183 l_exception BOOLEAN;
184 l_full_path VARCHAR2(500) := g_path||'Lock_Documents';
185 BEGIN
186
187 IF (g_debug_mode = 'Y') THEN
188 Put_Debug_Msg (l_full_path,p_debug_msg => '**** Lock Document ****');
189 END IF;
190
191 -- lock document header and children, depending upon document type
192 IF p_document_type = 'PO'
193 THEN
194 FOR i IN 1 .. 10000 LOOP
195 l_exception := FALSE;
196 BEGIN
197 OPEN c_lock_po;
198 FETCH c_lock_po INTO l_lock_po;
199 CLOSE c_lock_po;
200 EXCEPTION
201 WHEN OTHERS THEN
202 IF (SQLCODE = -54) THEN -- record already locked
203 l_exception := TRUE ;
204 ELSE
205 APP_EXCEPTION.Raise_Exception;
206 END IF;
207 END;
208 IF l_exception = FALSE THEN
209 IF g_debug_mode = 'Y' THEN
210 Put_Debug_Msg (l_full_path,p_debug_msg => 'lock loop:'||i);
211 END IF;
212 END IF;
213 EXIT WHEN l_exception = FALSE;
214 END LOOP;
215 ELSIF p_document_type = 'REQ'
216 THEN
217 FOR i IN 1 .. 10000 LOOP
218 l_exception := FALSE;
219 BEGIN
220 OPEN c_lock_req;
221 FETCH c_lock_req INTO l_lock_req;
222 CLOSE c_lock_req;
223 EXCEPTION
224 WHEN OTHERS THEN
225 IF (SQLCODE = -54) THEN -- record already locked
226 l_exception := TRUE ;
227 ELSE
228 APP_EXCEPTION.Raise_Exception;
229 END IF;
230 END;
231 IF l_exception = FALSE THEN
232 IF g_debug_mode = 'Y' THEN
233 Put_Debug_Msg (l_full_path,p_debug_msg => 'lock loop:'||i);
234 END IF;
235 END IF;
236 EXIT WHEN l_exception = FALSE;
237 END LOOP;
238 ELSIF p_document_type = 'REL'
239 THEN
240 FOR i IN 1 .. 10000 LOOP
241 l_exception := FALSE;
242 BEGIN
243 OPEN c_lock_release;
244 FETCH c_lock_release INTO l_lock_release;
245 CLOSE c_lock_release;
246 EXCEPTION
247 WHEN OTHERS THEN
248 IF (SQLCODE = -54) THEN -- record already locked
249 l_exception := TRUE ;
250 ELSE
251 APP_EXCEPTION.Raise_Exception;
252 END IF;
253 END;
254 IF l_exception = FALSE THEN
255 IF g_debug_mode = 'Y' THEN
256 Put_Debug_Msg (l_full_path,p_debug_msg => 'lock loop:'||i);
257 END IF;
258 END IF;
259 EXIT WHEN l_exception = FALSE;
260 END LOOP;
261 -- Added for PRC.FP.J, 3173178
262 ELSIF p_document_type = 'PA'
263 THEN
264 FOR i IN 1 .. 10000 LOOP
265 l_exception := FALSE;
266 BEGIN
267 OPEN c_lock_bpa;
268 FETCH c_lock_bpa INTO l_lock_bpa;
269 CLOSE c_lock_bpa;
270 EXCEPTION
271 WHEN OTHERS THEN
272 IF (SQLCODE = -54) THEN -- record already locked
273 l_exception := TRUE ;
274 ELSE
275 APP_EXCEPTION.Raise_Exception;
276 END IF;
277 END;
278 IF l_exception = FALSE THEN
279 IF g_debug_mode = 'Y' THEN
280 Put_Debug_Msg (l_full_path,p_debug_msg => 'lock loop:'||i);
281 END IF;
282 END IF;
283 EXIT WHEN l_exception = FALSE;
284 END LOOP;
285 END IF; -- p_document_type = 'PO'
286
287 IF l_exception = FALSE THEN
288 RETURN FND_API.G_RET_STS_SUCCESS;
289 ELSE
290 RETURN FND_API.G_RET_STS_ERROR;
291 END IF;
292
293
294 EXCEPTION
295 WHEN OTHERS THEN
296 IF (FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)) THEN
297 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, 'Lock_Documents');
298 END IF;
299 IF ( g_unexp_level >= g_debug_level ) THEN
300 FND_MESSAGE.SET_NAME('IGC','IGC_LOGGING_UNEXP_ERROR');
301 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
302 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
303 FND_LOG.MESSAGE ( g_unexp_level,l_full_path, TRUE);
304 END IF;
305 APP_EXCEPTION.Raise_Exception;
306 END Lock_Documents;
307
308
309
310 -- Procedure Log_Error
311 -- ===================
312 --
313 -- This procedure copies any exceptions logged in the pl/sql table to the CBC exceptions table.
314 -- The purpose of this is to minimize database i/o calls.
315 --
316 -- IN Parameters
317 -- -------------
318 -- p_sob_id Set of Books Id
319 -- p_org_id Org Id
320 -- p_conc_request_id Concurrent Request Id
321 -- p_process_phase User entered processing phase: F - Final, P - Preliminary
322 --
323 -- OUT Parameters
324 -- --------------
325 --
326 --
327 PROCEDURE Log_Error(p_sobid IN NUMBER,
328 p_org_id IN NUMBER,
329 p_conc_request_id IN NUMBER,
330 p_process_phase IN VARCHAR2
331 ) IS
332
333
334
335 l_full_path VARCHAR2(500) := g_path||'Log_error';
336 BEGIN
337
338 IF (g_debug_mode = 'Y') THEN
339 Put_Debug_Msg (l_full_path,p_debug_msg => '**** Log Error ****');
340 END IF;
341
342 -- check to ensure that exceptions are present in pl/sql table
343 IF g_exception_tbl_index > 0
344 THEN
345 -- insert any records in global pl/sql exception table into igc exceptions table
346 -- bug 2804025 ssmales 19-Feb-2003 amended below to user g_user_id,g_login
347 FOR l_index IN g_exception_tbl.FIRST .. g_exception_tbl.LAST
348 LOOP
349 INSERT INTO igc_cbc_po_process_excpts_all
350 (
351 document_type,
352 document_id,
353 line_id,
354 line_location_id,
355 distribution_id,
356 org_id,
357 sob_id,
358 process_type,
359 process_phase,
360 conc_request_id,
361 exception_code,
362 exception_reason,
363 last_update_date,
364 last_updated_by,
365 last_update_login,
366 creation_date,
367 created_by
368 )
369 VALUES
370 (
371 g_exception_tbl(l_index).document_type,
372 g_exception_tbl(l_index).document_id,
373 g_exception_tbl(l_index).line_id,
374 g_exception_tbl(l_index).line_location_id,
375 g_exception_tbl(l_index).distribution_id,
376 p_org_id,
377 p_sobid,
378 'YE',
379 p_process_phase,
380 p_conc_request_id,
381 g_exception_tbl(l_index).exception_code,
382 g_exception_tbl(l_index).exception_reason,
383 SYSDATE,
384 g_user_id,
385 g_login,
386 SYSDATE,
387 g_user_id
388 );
389 END LOOP
390 COMMIT;
391 -- reset index and clear pl/sql table
392 g_exception_tbl_index := 0;
393 g_exception_tbl.DELETE;
394
395 END IF; -- g_exception_tbl_index > 0
396
397 IF (g_debug_mode = 'Y') THEN
398 Put_Debug_Msg (l_full_path,p_debug_msg => '**** Log Error - Completed ****');
399 END IF;
400
401 EXCEPTION
402 WHEN OTHERS THEN
403 IF (FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)) THEN
404 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, 'Log_Error');
405 END IF;
406 IF ( g_unexp_level >= g_debug_level ) THEN
407 FND_MESSAGE.SET_NAME('IGC','IGC_LOGGING_UNEXP_ERROR');
408 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
409 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
410 FND_LOG.MESSAGE ( g_unexp_level,l_full_path, TRUE);
411 END IF;
412 APP_EXCEPTION.Raise_Exception;
413 END Log_Error;
414
415
416
417 -- Procedure Execute_Exceptions_Report
418 -- ===================================
419 --
420 -- This procedure initiates a concurrent process to run the PO CBC Year End Exceptions report.
421 --
422 -- IN Parameters
423 -- -------------
424 -- p_sob_id Set of Books Id
425 -- p_org_id Org Id
426 -- p_conc_request_id Concurrent Request Id
427 -- p_process_phase User entered processing phase: F - Final, P - Preliminary
428 -- p_year User entered Year being closed
429 --
430 -- OUT Parameters
431 -- --------------
432 --
433 --
434 PROCEDURE Execute_Exceptions_Report(p_sobid IN NUMBER,
435 p_org_id IN NUMBER,
436 p_conc_request_id IN NUMBER,
437 p_process_phase IN VARCHAR2,
438 p_year IN NUMBER
439 ) IS
440
441 l_request_id NUMBER := 0;
442
443 l_full_path VARCHAR2(500) := g_path||'Execute_Exceptions_Report';
444 ------Variables related to XML Report
445 l_terr VARCHAR2(10):='US';
446 l_lang VARCHAR2(10):='en';
447 l_layout BOOLEAN;
448 BEGIN
449 IF (g_debug_mode = 'Y') THEN
450 Put_Debug_Msg (l_full_path,p_debug_msg => '**** Execute Exceptions Report ****');
451 END IF;
452
453 -- Initiate Exceptions Report
454
455 /*Bug No : 6341012. MOAC uptake. Set Operaating Unit for the request before submitting. */
456
457 fnd_request.set_org_id(p_org_id);
458
459 l_request_id := FND_REQUEST.SUBMIT_REQUEST(
460 APPLICATION => 'IGC',
461 PROGRAM => 'IGCPOYEE',
462 DESCRIPTION => 'PO Year End Exceptions Report',
463 START_TIME => NULL,
464 SUB_REQUEST => FALSE,
465 ARGUMENT1 => p_sobid,
466 ARGUMENT2 => p_org_id,
467 ARGUMENT3 => p_process_phase,
468 ARGUMENT4 => p_year,
469 ARGUMENT5 => p_conc_request_id,
470 ARGUMENT6 => NULL,
471 ARGUMENT7 => NULL,
472 ARGUMENT8 => NULL,
473 ARGUMENT9 => NULL,
474 ARGUMENT10 => NULL,
475 ARGUMENT11 => NULL,
476 ARGUMENT12 => NULL,
477 ARGUMENT13 => NULL, ARGUMENT14 => NULL,
478 ARGUMENT15 => NULL, ARGUMENT16 => NULL, ARGUMENT17 => NULL,
479 ARGUMENT18 => NULL, ARGUMENT19 => NULL, ARGUMENT20 => NULL,
480 ARGUMENT21 => NULL, ARGUMENT22 => NULL, ARGUMENT23 => NULL,
481 ARGUMENT24 => NULL, ARGUMENT25 => NULL, ARGUMENT26 => NULL,
482 ARGUMENT27 => NULL, ARGUMENT28 => NULL, ARGUMENT29 => NULL,
483 ARGUMENT30 => NULL, ARGUMENT31 => NULL, ARGUMENT32 => NULL,
484 ARGUMENT33 => NULL, ARGUMENT34 => NULL, ARGUMENT35 => NULL,
485 ARGUMENT36 => NULL, ARGUMENT37 => NULL, ARGUMENT38 => NULL,
486 ARGUMENT39 => NULL, ARGUMENT40 => NULL, ARGUMENT41 => NULL,
487 ARGUMENT42 => NULL, ARGUMENT43 => NULL, ARGUMENT44 => NULL,
488 ARGUMENT45 => NULL, ARGUMENT46 => NULL, ARGUMENT47 => NULL,
489 ARGUMENT48 => NULL, ARGUMENT49 => NULL, ARGUMENT50 => NULL,
490 ARGUMENT51 => NULL, ARGUMENT52 => NULL, ARGUMENT53 => NULL,
491 ARGUMENT54 => NULL, ARGUMENT55 => NULL, ARGUMENT56 => NULL,
492 ARGUMENT57 => NULL, ARGUMENT58 => NULL, ARGUMENT59 => NULL,
493 ARGUMENT60 => NULL, ARGUMENT61 => NULL, ARGUMENT62 => NULL,
494 ARGUMENT63 => NULL, ARGUMENT64 => NULL, ARGUMENT65 => NULL,
495 ARGUMENT66 => NULL, ARGUMENT67 => NULL, ARGUMENT68 => NULL,
496 ARGUMENT69 => NULL, ARGUMENT70 => NULL, ARGUMENT71 => NULL,
497 ARGUMENT72 => NULL, ARGUMENT73 => NULL, ARGUMENT74 => NULL,
498 ARGUMENT75 => NULL, ARGUMENT76 => NULL, ARGUMENT77 => NULL,
499 ARGUMENT78 => NULL, ARGUMENT79 => NULL, ARGUMENT80 => NULL,
500 ARGUMENT81 => NULL, ARGUMENT82 => NULL, ARGUMENT83 => NULL,
501 ARGUMENT84 => NULL, ARGUMENT85 => NULL, ARGUMENT86 => NULL,
502 ARGUMENT87 => NULL, ARGUMENT88 => NULL, ARGUMENT89 => NULL,
503 ARGUMENT90 => NULL, ARGUMENT91 => NULL, ARGUMENT92 => NULL,
504 ARGUMENT93 => NULL, ARGUMENT94 => NULL, ARGUMENT95 => NULL,
505 ARGUMENT96 => NULL, ARGUMENT97 => NULL, ARGUMENT98 => NULL,
506 ARGUMENT99 => NULL, ARGUMENT100 => NULL );
507
508 IF l_request_id = 0 THEN
509 fnd_message.set_name('IGC','IGC_CC_ER_SUBMIT_EXCPTION_RPT');
510 IF (g_debug_mode = 'Y') THEN
511 Put_Debug_Msg (l_full_path,fnd_message.get,g_event_level);
512 END IF;
513 app_exception.raise_exception;
514 END IF; -- l_request_id = 0
515
516 IF (g_debug_mode = 'Y') THEN
517 Put_Debug_Msg (l_full_path,p_debug_msg => '**** Completed Execute Exceptions Report ****');
518 END IF;
519
520 ---------------------------------
521 ------Run XML Report
522 ---------------------------------
523 IF IGC_CC_COMMON_UTILS_PVT.xml_report_enabled THEN
524 IGC_CC_COMMON_UTILS_PVT.get_xml_layout_info(
525 l_lang,
526 l_terr,
527 'IGCPOYEE_XML',
528 'IGC',
529 'IGCPOYEE_XML' );
530
531 l_layout := FND_REQUEST.ADD_LAYOUT(
532 'IGC',
533 'IGCPOYEE_XML',
534 l_lang,
535 l_terr,
536 'RTF');
537 IF l_layout then
538 l_request_id := FND_REQUEST.SUBMIT_REQUEST(
539 APPLICATION => 'IGC',
540 PROGRAM => 'IGCPOYEE_XML',
541 DESCRIPTION => 'PO Year End Exceptions Report',
542 START_TIME => NULL,
543 SUB_REQUEST => FALSE,
544 ARGUMENT1 => p_sobid,
545 ARGUMENT2 => p_org_id,
546 ARGUMENT3 => p_process_phase,
547 ARGUMENT4 => p_year,
548 ARGUMENT5 => p_conc_request_id,
549 ARGUMENT6 => NULL,
550 ARGUMENT7 => NULL,
551 ARGUMENT8 => NULL,
552 ARGUMENT9 => NULL,
553 ARGUMENT10 => NULL,
554 ARGUMENT11 => NULL,
555 ARGUMENT12 => NULL,
556 ARGUMENT13 => NULL, ARGUMENT14 => NULL,
557 ARGUMENT15 => NULL, ARGUMENT16 => NULL, ARGUMENT17 => NULL,
558 ARGUMENT18 => NULL, ARGUMENT19 => NULL, ARGUMENT20 => NULL,
559 ARGUMENT21 => NULL, ARGUMENT22 => NULL, ARGUMENT23 => NULL,
560 ARGUMENT24 => NULL, ARGUMENT25 => NULL, ARGUMENT26 => NULL,
561 ARGUMENT27 => NULL, ARGUMENT28 => NULL, ARGUMENT29 => NULL,
562 ARGUMENT30 => NULL, ARGUMENT31 => NULL, ARGUMENT32 => NULL,
563 ARGUMENT33 => NULL, ARGUMENT34 => NULL, ARGUMENT35 => NULL,
564 ARGUMENT36 => NULL, ARGUMENT37 => NULL, ARGUMENT38 => NULL,
565 ARGUMENT39 => NULL, ARGUMENT40 => NULL, ARGUMENT41 => NULL,
566 ARGUMENT42 => NULL, ARGUMENT43 => NULL, ARGUMENT44 => NULL,
567 ARGUMENT45 => NULL, ARGUMENT46 => NULL, ARGUMENT47 => NULL,
568 ARGUMENT48 => NULL, ARGUMENT49 => NULL, ARGUMENT50 => NULL,
569 ARGUMENT51 => NULL, ARGUMENT52 => NULL, ARGUMENT53 => NULL,
570 ARGUMENT54 => NULL, ARGUMENT55 => NULL, ARGUMENT56 => NULL,
571 ARGUMENT57 => NULL, ARGUMENT58 => NULL, ARGUMENT59 => NULL,
572 ARGUMENT60 => NULL, ARGUMENT61 => NULL, ARGUMENT62 => NULL,
573 ARGUMENT63 => NULL, ARGUMENT64 => NULL, ARGUMENT65 => NULL,
574 ARGUMENT66 => NULL, ARGUMENT67 => NULL, ARGUMENT68 => NULL,
575 ARGUMENT69 => NULL, ARGUMENT70 => NULL, ARGUMENT71 => NULL,
576 ARGUMENT72 => NULL, ARGUMENT73 => NULL, ARGUMENT74 => NULL,
577 ARGUMENT75 => NULL, ARGUMENT76 => NULL, ARGUMENT77 => NULL,
578 ARGUMENT78 => NULL, ARGUMENT79 => NULL, ARGUMENT80 => NULL,
579 ARGUMENT81 => NULL, ARGUMENT82 => NULL, ARGUMENT83 => NULL,
580 ARGUMENT84 => NULL, ARGUMENT85 => NULL, ARGUMENT86 => NULL,
581 ARGUMENT87 => NULL, ARGUMENT88 => NULL, ARGUMENT89 => NULL,
582 ARGUMENT90 => NULL, ARGUMENT91 => NULL, ARGUMENT92 => NULL,
583 ARGUMENT93 => NULL, ARGUMENT94 => NULL, ARGUMENT95 => NULL,
584 ARGUMENT96 => NULL, ARGUMENT97 => NULL, ARGUMENT98 => NULL,
585 ARGUMENT99 => NULL, ARGUMENT100 => NULL );
586
587 IF l_request_id = 0 THEN
588 fnd_message.set_name('IGC','IGC_CC_ER_SUBMIT_EXCPTION_RPT');
589 IF (g_debug_mode = 'Y') THEN
590 Put_Debug_Msg (l_full_path,fnd_message.get,g_event_level);
591 END IF;
592 app_exception.raise_exception;
593 END IF; -- l_request_id = 0
594 IF (g_debug_mode = 'Y') THEN
595 Put_Debug_Msg (l_full_path,p_debug_msg => '**** Completed Execute Exceptions Report ****');
596 END IF;
597 END IF;
598 END IF;
599 --------------------
600 -- End of XML Report
601 --------------------
602 COMMIT;
603
604
605 EXCEPTION
606 WHEN OTHERS THEN
607 fnd_message.set_name('IGC','IGC_CC_ER_SUBMIT_EXCPTION_RPT');
608 IF g_debug_mode = 'Y' THEN
609 Put_Debug_Msg (l_full_path,fnd_message.get,g_error_level);
610 END IF;
611 app_exception.raise_exception;
612 IF ( g_unexp_level >= g_debug_level ) THEN
613 FND_MESSAGE.SET_NAME('IGC','IGC_LOGGING_UNEXP_ERROR');
614 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
615 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
616 FND_LOG.MESSAGE ( g_unexp_level,l_full_path, TRUE);
617 END IF;
618
619 END Execute_Exceptions_Report;
620
621
622
623 -- Procedure Insert_Exception_Record
624 -- =================================
625 --
626 -- This procedure inserts exception detail records into the pl/sql exceptions table.
627 --
628 -- IN Parameters
629 -- -------------
630 -- p_document_type Type of document: PO, REQ , 'PA' or REL
631 -- p_document_id Id of document
632 -- p_line_id Id of document line
633 -- p_line_location_id Id of document line location
634 -- p_distribution_id Id of document distribution
635 -- p_exception_reason Description of exception
636 -- p_exception_code Code of exception
637 --
638 -- OUT Parameters
639 -- --------------
640 --
641 --
642 PROCEDURE Insert_Exception_Record(p_document_type IN VARCHAR2 := NULL,
643 p_document_id IN NUMBER := NULL,
644 p_line_id IN NUMBER := NUll,
645 p_line_location_id IN NUMBER := NULL,
646 p_distribution_id IN NUMBER := NULL,
647 p_exception_reason IN VARCHAR2,
648 p_exception_code IN VARCHAR2 := NULL
649 ) AS
650
651 l_full_path VARCHAR2(500) := g_path||'Insert_Exception_Record';
652 BEGIN
653
654 IF (g_debug_mode = 'Y') THEN
655 Put_Debug_Msg (l_full_path,p_debug_msg => '**** Insert Exception Record ****');
656 END IF;
657
658 -- insert exception record into pl/sql table
659
660 g_exception_tbl_index := g_exception_tbl_index + 1;
661
662 g_exception_tbl(g_exception_tbl_index).document_type := p_document_type ;
663 g_exception_tbl(g_exception_tbl_index).document_id := p_document_id ;
664 g_exception_tbl(g_exception_tbl_index).line_id := p_line_id ;
665 g_exception_tbl(g_exception_tbl_index).line_location_id := p_line_location_id ;
666 g_exception_tbl(g_exception_tbl_index).distribution_id := p_distribution_id ;
667 g_exception_tbl(g_exception_tbl_index).exception_reason := p_exception_reason ;
668 g_exception_tbl(g_exception_tbl_index).exception_code := p_exception_code ;
669
670 EXCEPTION
671 WHEN OTHERS THEN
672 IF (FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)) THEN
673 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, 'Insert_Exception_Record');
674 END IF;
675 IF ( g_unexp_level >= g_debug_level ) THEN
676 FND_MESSAGE.SET_NAME('IGC','IGC_LOGGING_UNEXP_ERROR');
677 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
678 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
679 FND_LOG.MESSAGE ( g_unexp_level,l_full_path, TRUE);
680 END IF;
681 APP_EXCEPTION.Raise_Exception;
682
683 END Insert_Exception_Record;
684
685
686
687 -- Procedure Validate_BC_Params
688 -- ============================
689 --
690 -- The purpose of this procedure is to check whether Budgetary Control is enabled
691 -- in the Standard and Commitment Budgets for PO's and Requisitions.
692 -- In the event of both Standard PO and Requisition encumbrance being disabled, terminate processing
693 --
694 -- IN Parameters
695 -- -------------
696 -- p_sob_id Set of Books Id
697 -- p_org_id Org Id
698 -- p_process_phase User entered processing phase: F - Final, P - Preliminary
699 -- p_year User entered Year being closed
700 -- p_trunc_exception User entered choice to truncate the exception table: Y or N
701 --
702 -- OUT Parameters
703 -- --------------
704 -- x_po_enc_on Flag indicating whether SBC PO encumbrance is enabled
705 -- x_req_enc_on Flag indicating whether SBC Req encumbrance is enabled
706 -- x_return_code Indicates the return status of the procedure:
707 -- 0 - Need to terminate processing successfully
708 -- 1 - Need to terminate processing with warning
709 -- 2 - Need to terminate processing with error
710 -- -99 - Successful, continue processing
711 -- x_msg_buf stores any error message encountered
712 --
713 --
714 PROCEDURE Validate_BC_Params
715 (
716 p_sobid IN NUMBER,
717 p_org_id IN NUMBER,
718 p_process_phase IN VARCHAR2,
719 p_year IN NUMBER,
720 p_trunc_exception IN VARCHAR2,
721 x_po_enc_on OUT NOCOPY BOOLEAN,
722 x_req_enc_on OUT NOCOPY BOOLEAN,
723 x_return_code OUT NOCOPY NUMBER,
724 x_msg_buf OUT NOCOPY VARCHAR2
725 ) AS
726
727
728 CURSOR c_sbc_enabled IS
729 SELECT glsob.enable_budgetary_control_flag
730 ,glsob.chart_of_accounts_id
731 FROM gl_sets_of_books glsob
732 WHERE glsob.set_of_books_id = p_sobid ;
733
734 CURSOR c_sbc_encumbrances IS
735 SELECT req_encumbrance_type_id,
736 purch_encumbrance_type_id,
737 req_encumbrance_flag,
738 purch_encumbrance_flag
739 FROM financials_system_parameters
740 WHERE set_of_books_id = p_sobid ;
741
742 /*Bug No : 6341012. SLA uptake. The table IGC_CC_ENCMBRNC_CTRLS_V no more exists.
743
744 CURSOR c_cbc_encumbrances IS
745 SELECT cc_prov_encmbrnc_type_id,
746 cc_conf_encmbrnc_type_id,
747 cc_prov_encmbrnc_enable_flag,
748 cc_conf_encmbrnc_enable_flag
749 FROM igc_cc_encmbrnc_ctrls_v
750 WHERE org_id = p_org_id ;
751 */
752
753 l_cbc_enabled VARCHAR2(1) := 'N' ;
754 l_sbc_enabled VARCHAR2(1) := 'N' ;
755 l_coa_id NUMBER := 0 ;
756 l_req_enc_type_id NUMBER := 0 ;
757 l_pur_enc_type_id NUMBER := 0 ;
758 l_req_enc_flag VARCHAR2(1) := 'N' ;
759 l_pur_enc_flag VARCHAR2(1) := 'N' ;
760 /*Bug No : 6341012. SLA uptake. Encumbrance_Type_Ids are not required. */
761 -- l_prv_enc_type_id NUMBER := 0 ;
762 -- l_con_enc_type_id NUMBER := 0 ;
763 -- l_prv_enc_flag VARCHAR2(1) := 'N' ;
764 -- l_con_enc_flag VARCHAR2(1) := 'N' ;
765 l_sql_string VARCHAR2(100) := null ;
766 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
767 l_msg_count NUMBER := 0 ;
768 l_msg_data VARCHAR2(2000) := null ;
769 l_err_code VARCHAR2(100) := null ;
770
771 E_IGC_CBC_PO_DISABLE_YEP EXCEPTION ;
772 E_IGC_CBC_PO_ON_SBC_OFF EXCEPTION ;
773 E_IGC_CC_INVALID_SET_UP EXCEPTION ;
774
775
776 l_full_path VARCHAR2(500) := g_path||'Validate_BC_Params';
777 BEGIN
778
779 IF (g_debug_mode = 'Y') THEN
780 Put_Debug_Msg (l_full_path,p_debug_msg => '**** Validate Budgetary Control Parameters ****');
781 END IF;
782
783 -- Check if CBC is enabled for Purchasing
784
785 IGC_CBC_PO_GRP.IS_CBC_ENABLED(
786 p_api_version => 1
787 ,p_init_msg_list => 'T'
788 ,p_commit => 'F'
789 ,p_validation_level => 100
790 ,x_return_status => l_return_status
791 ,x_msg_count => l_msg_count
792 ,x_msg_data => l_msg_data
793 ,x_cbc_enabled => l_cbc_enabled
794 ) ;
795
796 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
797 THEN
798 -- if update unsuccessful report errors
799 l_msg_data := '';
800 For j in 1..NVL(l_msg_count,0) LOOP
801 l_msg_data := FND_MSG_PUB.Get(p_msg_index => j,
802 p_encoded => 'T');
803 Insert_Exception_Record(p_exception_reason => l_msg_data,
804 p_exception_code => l_return_status
805 );
806 END LOOP;
807 x_return_code := 2;
808 x_msg_buf := l_msg_data;
809 RETURN ;
810 END IF ;
811
812 IF l_cbc_enabled = 'N'
813 THEN
814 -- since CBC is not enabled for PO, Year End Process should not be run. Terminate with Error
815 Raise E_IGC_CBC_PO_DISABLE_YEP ;
816 ELSE -- l_cbc_enabled = 'N'
817 -- Check that Standard Budgetary Control is enabled
818 IF (g_debug_mode = 'Y') THEN
819 Put_Debug_Msg (l_full_path,p_debug_msg => 'Checking SBC enabled');
820 END IF;
821
822 OPEN c_sbc_enabled ;
823 FETCH c_sbc_enabled
824 INTO l_sbc_enabled
825 ,l_coa_id ;
826 CLOSE c_sbc_enabled ;
827
828 IF l_sbc_enabled = 'N'
829 THEN
830 -- Error, as CBC cannot be enabled without SBC being enabled. Terminate with Error
831 Raise E_IGC_CBC_PO_ON_SBC_OFF ;
832 END IF;
833
834 END IF; -- l_cbc_enabled = 'N'
835
836 -- Clear the exceptions table if user has selected Yes for Truncate parameter
837 IF p_trunc_exception = 'Y'
838 THEN
839 IF (g_debug_mode = 'Y') THEN
840 Put_Debug_Msg (l_full_path,p_debug_msg => 'Truncating igc_cbc_po_process_excpts_all table');
841 END IF;
842
843 DELETE FROM igc_cbc_po_process_excpts_all;
844 -- l_sql_string := 'TRUNCATE table igc_cbc_po_process_excpts_all' ;
845 -- EXECUTE IMMEDIATE l_sql_string;
846 END IF ; -- p_trunc_exception = 'Y'
847
848 -- Both SBC and CBC are enabled, so get encumbrance types
849 IF (g_debug_mode = 'Y') THEN
850 Put_Debug_Msg (l_full_path,p_debug_msg => 'Getting sbc encumbrance details');
851 END IF;
852 OPEN c_sbc_encumbrances ;
853 FETCH c_sbc_encumbrances INTO
854 l_req_enc_type_id,
855 l_pur_enc_type_id,
856 l_req_enc_flag,
857 l_pur_enc_flag ;
858 CLOSE c_sbc_encumbrances ;
859
860 IF (g_debug_mode = 'Y') THEN
861 Put_Debug_Msg (l_full_path,p_debug_msg => 'Getting cbc encumbrance details');
862 END IF;
863
864 /*Bug No : 6341012. SLA uptake. This cursor is not required as the table IGC_CC_ENCMBRNC_CTRLS_V is obsoleted.
865
866 OPEN c_cbc_encumbrances ;
867 FETCH c_cbc_encumbrances INTO
868 l_prv_enc_type_id,
869 l_con_enc_type_id,
870 l_prv_enc_flag,
871 l_con_enc_flag ;
872 CLOSE c_cbc_encumbrances ;
873
874 -- Check for Invalid setup. If invalid terminate with Error.
875 IF (l_req_enc_type_id IS NOT NULL AND l_prv_enc_type_id IS NULL ) OR
876 (l_pur_enc_type_id IS NOT NULL AND l_con_enc_type_id IS NULL )
877 THEN
878 RAISE E_IGC_CC_INVALID_SET_UP ;
879 END IF ;
880 */
881
882 -- Set encumbrance OUT parameters
883 IF l_req_enc_flag = 'Y'
884 THEN
885 x_req_enc_on := TRUE ;
886 ELSE
887 x_req_enc_on := FALSE ;
888 END IF;
889
890 IF l_pur_enc_flag = 'Y'
891 THEN
892 x_po_enc_on := TRUE ;
893 ELSE
894 x_po_enc_on := FALSE ;
895 END IF ;
896
897 -- Record encumbrance exceptions
898 IF NOT x_req_enc_on
899 THEN
900 l_err_code := 'IGC_PO_YEP_REQ_ENC_OFF';
901 FND_MESSAGE.set_name('IGC',l_err_code);
902 l_msg_data := FND_MESSAGE.get;
903 Insert_Exception_Record(p_exception_reason => l_msg_data,
904 p_exception_code => l_err_code);
905 END IF; -- NOT x_req_enc_on
906
907 IF NOT x_po_enc_on
908 THEN
909 l_err_code := 'IGC_PO_YEP_PO_ENC_OFF';
910 FND_MESSAGE.set_name('IGC',l_err_code);
911 l_msg_data := FND_MESSAGE.get;
912 Insert_Exception_Record(p_exception_reason => l_msg_data,
913 p_exception_code => l_err_code);
914 END IF ; -- NOT x_po_enc_on
915
916 -- If both Req and PO encumbrances are off then terminate processing
917
918 IF NOT x_req_enc_on AND NOT x_po_enc_on
919 THEN
920 x_return_code := 0;
921 RETURN;
922
923 END IF; -- NOT x_req_enc_on AND NOT x_po_enc_on
924 x_return_code := -99;
925
926 EXCEPTION
927 WHEN E_IGC_CBC_PO_DISABLE_YEP THEN
928 l_err_code := 'IGC_CBC_PO_DISABLE_YEP';
929 FND_MESSAGE.set_name('IGC',l_err_code);
930 IF(g_excep_level >= g_debug_level) THEN
931 FND_LOG.MESSAGE(g_excep_level, l_full_path, FALSE);
932 END IF;
933 x_msg_buf := FND_MESSAGE.Get;
934 x_return_code := 2 ;
935 WHEN E_IGC_CBC_PO_ON_SBC_OFF THEN
936 l_err_code := 'IGC_CBC_PO_ON_SBC_OFF';
937 FND_MESSAGE.set_name('IGC',l_err_code);
938 IF(g_excep_level >= g_debug_level) THEN
939 FND_LOG.MESSAGE(g_excep_level, l_full_path, FALSE);
940 END IF;
941 x_msg_buf := FND_MESSAGE.Get;
942 x_return_code := 2 ;
943 WHEN E_IGC_CC_INVALID_SET_UP THEN
944 l_err_code := 'IGC_CC_INVALID_SET_UP';
945 FND_MESSAGE.set_name('IGC',l_err_code);
946 IF(g_excep_level >= g_debug_level) THEN
947 FND_LOG.MESSAGE(g_excep_level, l_full_path, FALSE);
948 END IF;
949 x_msg_buf := FND_MESSAGE.Get;
950 x_return_code := 2 ;
951 WHEN OTHERS THEN
952 IF (FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)) THEN
953 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, 'Validate_BC_Params');
954 END IF;
955 fnd_message.set_name('IGC','IGC_LOGGING_USER_ERROR');
956 x_msg_buf := fnd_message.get;
957 x_return_code := 2 ;
958 IF ( g_unexp_level >= g_debug_level ) THEN
959 FND_MESSAGE.SET_NAME('IGC','IGC_LOGGING_UNEXP_ERROR');
960 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
961 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
962 FND_LOG.MESSAGE ( g_unexp_level,l_full_path, TRUE);
963 END IF;
964
965
966 END Validate_BC_Params;
967
968
969
970
971 -- Function Validate_Period_Status
972 -- ===============================
973 --
974 -- The purpose of this function is to check whether the GL and PO periods are in the expected
975 -- state to process Year End, and determine specific dates required by subsequent processing.
976 -- Validations are only carried out if in Final Mode.
977 --
978 -- IN Parameters
979 -- -------------
980 -- p_sob_id Set of Books Id
981 -- p_org_id Org Id
982 -- p_process_phase User entered processing phase: F - Final, P - Preliminary
983 -- p_year User entered Year being closed
984 --
985 -- OUT Parameters
986 -- --------------
987 -- x_prev_year_start_date Start Date of year being closed
988 -- x_prev_year_end_date End Date of year being closed
989 -- x_prev_year_end_period End period name of year being closed
990 -- x_prev_year_end_num End period number of year being closed
991 -- x_prev_year_end_quarter End quarter number of year being closed
992 -- x_curr_year_start_date Start Date of the current year
993 -- x_curr_year_start_period First period name of the current year
994 -- x_curr_year_start_num First period number of the current year
995 -- x_curr_year_start_quarter First quarter number of the current year
996 --
997 -- Function returns True for success, False for validation failure.
998 --
999 -- bug 2804025 ssmales 19-Feb-2003 added prev_year_end_period/num/quarter and curr_year_start_num/quarter
1000 FUNCTION Validate_Period_Status(p_sobid IN NUMBER,
1001 p_org_id IN NUMBER,
1002 p_process_phase IN VARCHAR2,
1003 p_year IN NUMBER,
1004 x_prev_year_start_date OUT NOCOPY DATE,
1005 x_prev_year_end_date OUT NOCOPY DATE,
1006 x_prev_year_end_period OUT NOCOPY VARCHAR2,
1007 x_prev_year_end_num OUT NOCOPY NUMBER,
1008 x_prev_year_end_quarter OUT NOCOPY NUMBER,
1009 x_curr_year_start_date OUT NOCOPY DATE,
1010 x_curr_year_start_period OUT NOCOPY VARCHAR2,
1011 x_curr_year_start_num OUT NOCOPY NUMBER,
1012 x_curr_year_start_quarter OUT NOCOPY NUMBER
1013 ) RETURN BOOLEAN IS
1014
1015
1016
1017 -- bug 2804025 ssmales 19-Feb-2003 added quarter num to period_dtls_rec_type
1018 TYPE period_dtls_rec_type IS RECORD
1019 (period_num igc_tbl_number,
1020 period_name igc_tbl_varchar30,
1021 start_date igc_tbl_date,
1022 end_date igc_tbl_date,
1023 gl_period_status igc_tbl_varchar5,
1024 po_period_status igc_tbl_varchar5,
1025 quarter_num igc_tbl_number );
1026
1027
1028 l_prd_dtls_rec period_dtls_rec_type ;
1029 l_curr_period_rec period_dtls_rec_type ;
1030
1031 l_msg_data VARCHAR2(2000) := null ;
1032 l_err_code VARCHAR2(100) := null ;
1033
1034
1035 -- bug 2804025 ssmales 19-Feb-2003 added quarter_num to select below
1036 CURSOR c_get_prd_dtls(c_p_year NUMBER) IS
1037 SELECT gp.period_num,
1038 gp.period_name,
1039 gps.start_date,
1040 gps.end_date,
1041 gps.closing_status gl_period_status,
1042 pos.closing_status po_period_status,
1043 gp.quarter_num
1044 FROM gl_period_statuses gps,
1045 gl_periods gp,
1046 gl_period_statuses pos,
1047 gl_sets_of_books gb,
1048 fnd_application gl,
1049 fnd_application po
1050 WHERE gb.set_of_books_id = p_sobid
1051 AND gp.period_set_name = gb.period_set_name
1052 AND gp.period_type = gb.accounted_period_type
1053 AND gps.set_of_books_id = gb.set_of_books_id
1054 AND gps.period_name = gp.period_name
1055 AND gps.application_id = gl.application_id
1056 AND gps.period_num = gp.period_num
1057 AND gl.application_short_name = 'SQLGL'
1058 AND gp.period_year = c_p_year
1059 AND gp.adjustment_period_flag = 'N'
1060 AND pos.set_of_books_id = gb.set_of_books_id
1061 AND pos.period_name = gp.period_name
1062 AND pos.application_id = po.application_id
1063 AND po.application_short_name = 'PO'
1064 AND pos.period_num = gps.period_num
1065 ORDER BY gp.period_num ASC ;
1066
1067
1068 l_full_path VARCHAR2(500) := g_path||'Validate_Period_Status';
1069 BEGIN
1070
1071 IF (g_debug_mode = 'Y') THEN
1072 Put_Debug_Msg (l_full_path,p_debug_msg => '**** Validate Period Status ****');
1073 END IF;
1074
1075
1076 -- Validate Previous Year's details
1077 OPEN c_get_prd_dtls(p_year) ;
1078
1079 FETCH c_get_prd_dtls BULK COLLECT INTO l_prd_dtls_rec.period_num,
1080 l_prd_dtls_rec.period_name,
1081 l_prd_dtls_rec.start_date,
1082 l_prd_dtls_rec.end_date,
1083 l_prd_dtls_rec.gl_period_status,
1084 l_prd_dtls_rec.po_period_status,
1085 l_prd_dtls_rec.quarter_num ;
1086
1087
1088 CLOSE c_get_prd_dtls ;
1089
1090 IF (g_debug_mode = 'Y') THEN
1091 Put_Debug_Msg (l_full_path,p_debug_msg => 'Completed getting period details');
1092 END IF;
1093
1094 -- Validate First Period
1095 FOR l_period_index IN l_prd_dtls_rec.period_num.FIRST .. l_prd_dtls_rec.period_num.LAST
1096 LOOP
1097
1098 IF l_period_index = 1
1099 THEN
1100 IF (g_debug_mode = 'Y') THEN
1101 Put_Debug_Msg (l_full_path,p_debug_msg => 'Processing First Period');
1102 END IF;
1103 x_prev_year_start_date := l_prd_dtls_rec.start_date(l_period_index) ;
1104
1105 -- PO Period has to be closed for all periods in previous year except last period
1106 IF l_prd_dtls_rec.po_period_status(l_period_index) NOT IN ('C','P','N')
1107 AND p_process_phase = 'F'
1108 THEN
1109 l_err_code := 'IGC_PO_YEP_PO_PRD_STATUS';
1110 FND_MESSAGE.set_name('IGC',l_err_code);
1111 FND_MESSAGE.set_token('PREV_YEAR',p_year);
1112 l_msg_data := FND_MESSAGE.get;
1113 Insert_Exception_Record(p_exception_reason => l_msg_data,
1114 p_exception_code => l_err_code);
1115 END IF ; -- po_period_status
1116
1117 -- Validate Last Period
1118 ELSIF l_period_index = l_prd_dtls_rec.period_num.LAST -- l_period_index = 1
1119 THEN
1120 IF (g_debug_mode = 'Y') THEN
1121 Put_Debug_Msg (l_full_path,p_debug_msg => 'Processing Last Period');
1122 END IF;
1123 x_prev_year_end_date := l_prd_dtls_rec.end_date(l_period_index) ;
1124 x_prev_year_end_period := l_prd_dtls_rec.period_name(l_period_index);
1125 x_prev_year_end_num := l_prd_dtls_rec.period_num(l_period_index);
1126 x_prev_year_end_quarter := l_prd_dtls_rec.quarter_num(l_period_index);
1127
1128 -- Last PO Period of previous year should be open
1129 IF l_prd_dtls_rec.po_period_status(l_period_index) <> 'O'
1130 AND p_process_phase = 'F'
1131 THEN
1132 l_err_code := 'IGC_PO_YEP_PO_LAST_PERIOD';
1133 FND_MESSAGE.set_name('IGC',l_err_code);
1134 FND_MESSAGE.set_token('PREV_YEAR',p_year);
1135 l_msg_data := FND_MESSAGE.get;
1136 Insert_Exception_Record(p_exception_reason => l_msg_data,
1137 p_exception_code => l_err_code);
1138 END IF ; -- po_period_status
1139
1140 -- Last GL Period of previous year should be open
1141 IF l_prd_dtls_rec.gl_period_status(l_period_index) <> 'O'
1142 AND p_process_phase = 'F'
1143 THEN
1144 l_err_code := 'IGC_PO_YEP_GL_LAST_PERIOD';
1145 FND_MESSAGE.set_name('IGC',l_err_code );
1146 FND_MESSAGE.set_token('PREV_YEAR',p_year);
1147 l_msg_data := FND_MESSAGE.get;
1148 Insert_Exception_Record(p_exception_reason => l_msg_data,
1149 p_exception_code => l_err_code);
1150 END IF ; -- gl_period_status
1151
1152 ELSE -- l_period_index = 1
1153
1154 IF (g_debug_mode = 'Y') THEN
1155 Put_Debug_Msg (l_full_path,p_debug_msg => 'Processing other periods');
1156 END IF;
1157 -- Perform validation for other periods
1158
1159 -- PO Period has to be closed for all periods in previous year except last period
1160 IF l_prd_dtls_rec.po_period_status(l_period_index) NOT IN ('C','P','N')
1161 AND p_process_phase = 'F'
1162 THEN
1163 l_err_code := 'IGC_PO_YEP_PO_PRD_STATUS';
1164 FND_MESSAGE.set_name('IGC',l_err_code);
1165 FND_MESSAGE.set_token('PREV_YEAR',p_year);
1166 l_msg_data := FND_MESSAGE.get;
1167 Insert_Exception_Record(p_exception_reason => l_msg_data,
1168 p_exception_code => l_err_code);
1169 END IF ; -- po_poeriod_status
1170
1171 END IF; -- l_period_index = 1
1172
1173 END LOOP ;
1174
1175 -- Validate First Period Status of Current Year
1176 OPEN c_get_prd_dtls(p_year + 1) ;
1177 FETCH c_get_prd_dtls BULK COLLECT INTO l_curr_period_rec.period_num,
1178 l_curr_period_rec.period_name,
1179 l_curr_period_rec.start_date,
1180 l_curr_period_rec.end_date,
1181 l_curr_period_rec.gl_period_status,
1182 l_curr_period_rec.po_period_status,
1183 l_curr_period_rec.quarter_num ;
1184 CLOSE c_get_prd_dtls ;
1185
1186 IF (g_debug_mode = 'Y') THEN
1187 Put_Debug_Msg (l_full_path,p_debug_msg => 'Completed getting next years period details');
1188 END IF;
1189 x_curr_year_start_date := l_curr_period_rec.start_date(1) ;
1190 x_curr_year_start_period := l_curr_period_rec.period_name(1);
1191 x_curr_year_start_num := l_curr_period_rec.period_num(1);
1192 x_curr_year_start_quarter := l_curr_period_rec.quarter_num(1);
1193
1194 -- First PO Period of current year should be Open or Future Entry
1195 IF l_curr_period_rec.po_period_status(1) NOT IN ('O', 'F')
1196 AND p_process_phase = 'F'
1197 THEN
1198 l_err_code := 'IGC_PO_YEP_PO_FIRST_PERIOD';
1199 FND_MESSAGE.set_name('IGC',l_err_code);
1200 FND_MESSAGE.set_token('CURR_YEAR',(p_year + 1));
1201 l_msg_data := FND_MESSAGE.get;
1202 Insert_Exception_Record(p_exception_reason => l_msg_data,
1203 p_exception_code => l_err_code);
1204
1205 END IF ; -- po_period_status
1206
1207 -- First GL Period of current year should be Open or Future Entry
1208 IF l_curr_period_rec.gl_period_status(1) NOT IN ('O', 'F')
1209 AND p_process_phase = 'F'
1210 THEN
1211 l_err_code := 'IGC_PO_YEP_GL_FIRST_PERIOD';
1212 FND_MESSAGE.set_name('IGC',l_err_code);
1213 FND_MESSAGE.set_token('CURR_YEAR',(p_year + 1));
1214 l_msg_data := FND_MESSAGE.get;
1215 Insert_Exception_Record(p_exception_reason => l_msg_data,
1216 p_exception_code => l_err_code);
1217
1218 END IF ; -- gl_period_status
1219
1220 -- If any validations failed and we are in Final Mode then terminate processing
1221 IF l_err_code IS NOT NULL AND p_process_phase = 'F'
1222 THEN
1223
1224 RETURN FALSE;
1225
1226 END IF; -- l_err_code IS NULL AND p_process_phase = 'F'
1227
1228 RETURN TRUE;
1229
1230 EXCEPTION
1231 WHEN OTHERS THEN
1232 IF (FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)) THEN
1233 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, 'Validate_Period_Status');
1234 END IF;
1235 IF ( g_unexp_level >= g_debug_level ) THEN
1236 FND_MESSAGE.SET_NAME('IGC','IGC_LOGGING_UNEXP_ERROR');
1237 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
1238 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
1239 FND_LOG.MESSAGE ( g_unexp_level,l_full_path, TRUE);
1240 END IF;
1241 APP_EXCEPTION.Raise_Exception;
1242
1243 END Validate_Period_Status ;
1244
1245
1246
1247
1248 -- Procedure Fetch_PO_And_Releases
1249 -- ===============================
1250 --
1251 -- This procedure retrieves all PO and Release documents that satisfy the selection criteria
1252 -- and places them into the global temporary table for processing.
1253 --
1254 -- IN Parameters
1255 -- -------------
1256 -- p_org_id Org Id
1257 -- p_prev_year_start_date Start Date of year being closed
1258 -- p_prev_year_end_date End Date of year being closed
1259 --
1260 -- OUT Parameters
1261 -- --------------
1262 --
1263 --
1264 PROCEDURE Fetch_PO_And_Releases(p_org_id IN NUMBER,
1265 p_prev_year_start_date IN DATE,
1266 p_prev_year_end_date IN DATE
1267 ) IS
1268 l_rec_count NUMBER;
1269
1270 l_full_path VARCHAR2(500) := g_path||'fetch_PO_and_releases';
1271 BEGIN
1272 IF (g_debug_mode = 'Y') THEN
1273 Put_Debug_Msg (l_full_path,p_debug_msg => '**** Fetch PO and Releases ****');
1274 END IF;
1275
1276 -- Fetch into global temporary table all documents that satisfy our initial selection criteria
1277 INSERT INTO igc_cbc_po_process_gt
1278 (document_type,
1279 po_header_id,
1280 po_release_id,
1281 line_id,
1282 line_location_id,
1283 distribution_id,
1284 accrue_on_receipt,
1285 quantity_ordered,
1286 quantity_billed,
1287 encumbered_flag,
1288 gl_encumbered_date,
1289 gl_encumbered_period_name
1290 )
1291 SELECT
1292 DECODE(pod.po_release_id, NULL, 'PO', 'REL') document_type,
1293 pod.po_header_id,
1294 pod.po_release_id,
1295 pod.po_line_id,
1296 pod.line_location_id,
1297 pod.po_distribution_id,
1298 pod.accrue_on_receipt_flag,
1299 pod.quantity_ordered,
1300 pod.quantity_billed,
1301 NVL(pod.encumbered_flag,'N'),
1302 pod.gl_encumbered_date,
1303 pod.gl_encumbered_period_name
1304 FROM po_distributions_all pod,
1305 po_line_locations_all poll,
1306 po_lines_all pol
1307 WHERE DECODE(poll.accrue_on_receipt_flag,
1308 'N',
1309 NVL(pod.quantity_ordered,0) - GREATEST(NVL(pod.quantity_billed,0),
1310 NVL(pod.unencumbered_quantity,0)),
1311 'Y',
1312 NVL(pod.quantity_ordered,0) - GREATEST(NVL(pod.quantity_delivered,0),
1313 NVL(pod.unencumbered_quantity,0)),
1314 0)<> 0
1315 AND pol.po_header_id = poll.po_header_id
1316 AND poll.po_line_id = pol.po_line_id
1317 AND pod.line_location_id = poll.line_location_id
1318 AND pod.po_line_id = pol.po_line_id
1319 AND pod.po_header_id = pol.po_header_id
1320 AND pod.po_line_id = pol.po_line_id
1321 AND NVL(pol.closed_code,'X') <> 'FINALLY CLOSED'
1322 AND NVL(pol.cancel_flag,'N') = 'N'
1323 AND pol.org_id = p_org_id
1324 AND NVL(poll.closed_code,'X') <> 'FINALLY CLOSED'
1325 AND poll.shipment_type IN ('STANDARD','PLANNED','BLANKET','SCHEDULED')
1326 AND NVL(poll.cancel_flag,'N') = 'N'
1327 AND poll.org_id = p_org_id
1328 AND pod.prevent_encumbrance_flag = 'N'
1329 AND pod.org_id = p_org_id
1330 AND pod.gl_encumbered_date >= p_prev_year_start_date
1331 AND pod.gl_encumbered_date <= p_prev_year_end_date
1332 ;
1333
1334 IF (g_debug_mode = 'Y') THEN
1335 SELECT COUNT(1) INTO l_rec_count FROM igc_cbc_po_process_gt;
1336 Put_Debug_Msg (l_full_path,p_debug_msg => 'Insert Record Count: '||l_rec_count);
1337 END IF;
1338
1339 EXCEPTION
1340 WHEN OTHERS THEN
1341 IF (FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)) THEN
1342 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, 'Fetch_PO_And_Releases');
1343 END IF;
1344 IF ( g_unexp_level >= g_debug_level ) THEN
1345 FND_MESSAGE.SET_NAME('IGC','IGC_LOGGING_UNEXP_ERROR');
1346 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
1347 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
1348 FND_LOG.MESSAGE ( g_unexp_level,l_full_path, TRUE);
1349 END IF;
1350 APP_EXCEPTION.Raise_Exception;
1351
1352 END Fetch_PO_And_Releases;
1353
1354
1355
1356
1357 -- Procedure Fetch_Requisitions
1358 -- ============================
1359 --
1360 -- This procedure clears down the global temporary table and then retrieves all Requisition
1361 -- documents that satisfy the selection criteria, placing them into the table for processing.
1362 --
1363 -- IN Parameters
1364 -- -------------
1365 -- p_org_id Org Id
1366 -- p_prev_year_start_date Start Date of year being closed
1367 -- p_prev_year_end_date End Date of year being closed
1368 --
1369 -- OUT Parameters
1370 -- --------------
1371 --
1372 --
1373 --
1374 --
1375 PROCEDURE Fetch_Requisitions(p_org_id IN NUMBER,
1376 p_prev_year_start_date IN DATE,
1377 p_prev_year_end_date IN DATE
1378 ) IS
1379
1380 l_sql_string VARCHAR2(100) := null ;
1381 l_rec_count NUMBER;
1382
1383 l_full_path VARCHAR2(500) := g_path||'Fetch_Requisitions';
1384 BEGIN
1385 IF (g_debug_mode = 'Y') THEN
1386 Put_Debug_Msg (l_full_path,p_debug_msg => '**** Fetch Requisitions ****');
1387 END IF;
1388
1389 DELETE FROM IGC_CBC_PO_PROCESS_GT ;
1390 -- l_sql_string := 'TRUNCATE table IGC_CBC_PO_PROCESS_GT' ;
1391 -- EXECUTE IMMEDIATE l_sql_string;
1392
1393 -- Fetch into global temporary table all documents that satisfy our initial selection criteria
1394 -- Added the OR clause for REQs having
1395 -- backing BPA. In this case the gl_encumbered_date and the
1396 -- cbc accounting date should be updated even though there
1397 -- is no actual encumbrance to be moved.
1398 -- 3173178, Bidisha S
1399 INSERT INTO igc_cbc_po_process_gt
1400 (
1401 document_type,
1402 req_header_id,
1403 line_id,
1404 distribution_id,
1405 encumbered_flag,
1406 gl_encumbered_date,
1407 gl_encumbered_period_name,
1408 prevent_encumbrance_flag,
1409 blanket_po_header_id
1410 )
1411 SELECT
1412 'REQ',
1413 prl.requisition_header_id,
1414 prl.requisition_line_id,
1415 prd.distribution_id,
1416 NVL(prd.encumbered_flag, 'N'),
1417 prd.gl_encumbered_date,
1418 prd.gl_encumbered_period_name,
1419 Nvl(prd.prevent_encumbrance_flag,'N'),
1420 prl.blanket_po_header_id
1421 FROM
1422 po_requisition_lines_all prl,
1423 po_req_distributions_all prd
1424 WHERE
1425 NVL(prl.closed_code, 'X') NOT IN ('CANCELLED','FINALLY CLOSED')
1426 AND NVL(prl.cancel_flag, 'N') = 'N'
1427 AND NVL(prl.line_location_id, -999) = -999
1428 AND prl.source_type_code = 'VENDOR'
1429 AND prl.org_id = p_org_id
1430 AND (NVL(prd.prevent_encumbrance_flag, 'N') = 'N'
1431 OR (NVL(prd.prevent_encumbrance_flag, 'N') = 'Y'
1432 AND prl.blanket_po_header_id IS NOT NULL))
1433 AND prd.requisition_line_id = prl.requisition_line_id
1434 AND prd.org_id = p_org_id
1435 AND prd.gl_encumbered_date BETWEEN p_prev_year_start_date AND p_prev_year_end_date ;
1436
1437 IF (g_debug_mode = 'Y') THEN
1438 SELECT COUNT(1) INTO l_rec_count FROM igc_cbc_po_process_gt;
1439 Put_Debug_Msg (l_full_path,p_debug_msg => 'Insert Record Count: '||l_rec_count);
1440 END IF;
1441
1442 EXCEPTION
1443 WHEN OTHERS THEN
1444 IF (FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)) THEN
1445 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, 'Fetch_Requisitions');
1446 END IF;
1447 IF ( g_unexp_level >= g_debug_level ) THEN
1448 FND_MESSAGE.SET_NAME('IGC','IGC_LOGGING_UNEXP_ERROR');
1449 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
1450 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
1451 FND_LOG.MESSAGE ( g_unexp_level,l_full_path, TRUE);
1452 END IF;
1453 APP_EXCEPTION.Raise_Exception;
1454
1455 END Fetch_Requisitions ;
1456
1457
1458 -- Procedure Fetch_BPAs
1459 -- ============================
1460 --
1461 -- This procedure clears down the global temporary table and then retrieves all BPA
1462 -- documents that satisfy the selection criteria, placing them into the table for processing.
1463 -- Procedure has been added as part of changes being done for PRC.FP.J
1464 -- under bug 3173178
1465 --
1466 -- IN Parameters
1467 -- -------------
1468 -- p_org_id Org Id
1469 -- p_prev_year_start_date Start Date of year being closed
1470 -- p_prev_year_end_date End Date of year being closed
1471 --
1472 -- OUT Parameters
1473 -- --------------
1474 --
1475 --
1476 --
1477 --
1478 PROCEDURE Fetch_BPAs(p_org_id IN NUMBER,
1479 p_prev_year_start_date IN DATE,
1480 p_prev_year_end_date IN DATE
1481 ) IS
1482
1483 l_sql_string VARCHAR2(100) := null ;
1484 l_rec_count NUMBER;
1485
1486 l_full_path VARCHAR2(500) := g_path||'Fetch_BPAs';
1487 BEGIN
1488 IF (g_debug_mode = 'Y') THEN
1489 Put_Debug_Msg (l_full_path,p_debug_msg => '**** Fetch Blanket Agreements ****');
1490 END IF;
1491
1492 DELETE FROM IGC_CBC_PO_PROCESS_GT ;
1493
1494 -- Fetch into global temporary table all documents that satisfy our initial selection criteria
1495 INSERT INTO igc_cbc_po_process_gt
1496 (
1497 document_type,
1498 po_header_id,
1499 line_id,
1500 distribution_id,
1501 quantity_ordered,
1502 quantity_billed,
1503 encumbered_flag,
1504 gl_encumbered_date,
1505 gl_encumbered_period_name
1506 )
1507 SELECT
1508 'PA',
1509 poh.po_header_id,
1510 NULL,
1511 pod.po_distribution_id,
1512 pod.encumbered_amount+ pod.unencumbered_amount, --pod.amount_to_encumber,
1513 pod.unencumbered_quantity,
1514 Nvl(pod.encumbered_flag,'N'),
1515 pod.gl_encumbered_date,
1516 pod.gl_encumbered_period_name
1517 FROM po_distributions_all pod,
1518 po_headers_all poh
1519 WHERE (Nvl(pod.encumbered_amount+ pod.unencumbered_amount,0) > 0
1520 AND Nvl(pod.encumbered_amount+ pod.unencumbered_amount,0) <> Nvl(pod.unencumbered_amount,0)
1521 OR Nvl(pod.encumbered_amount+ pod.unencumbered_amount,0) <> Nvl(poh.blanket_total_amount,0))
1522 AND Nvl(poh.encumbrance_required_flag,'N') = 'Y'
1523 AND poh.type_lookup_code = 'BLANKET'
1524 AND poh.closed_date IS NULL
1525 AND Nvl(poh.cancel_flag,'N') = 'N'
1526 AND pod.po_header_id = poh.po_header_id
1527 AND pod.distribution_type = 'AGREEMENT'
1528 AND Nvl(pod.prevent_encumbrance_flag,'N') = 'N'
1529 AND pod.org_id = p_org_id
1530 AND poh.org_id = p_org_id
1531 AND pod.gl_encumbered_date >= p_prev_year_start_date
1532 AND pod.gl_encumbered_date <= p_prev_year_end_date;
1533
1534 IF (g_debug_mode = 'Y') THEN
1535 SELECT COUNT(1) INTO l_rec_count FROM igc_cbc_po_process_gt;
1536 Put_Debug_Msg (l_full_path,p_debug_msg => 'Insert Record Count: '||l_rec_count);
1537 END IF;
1538
1539 EXCEPTION
1540 WHEN OTHERS THEN
1541 IF (FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)) THEN
1542 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, 'Fetch_BPAs');
1543 END IF;
1544 IF ( g_unexp_level >= g_debug_level ) THEN
1545 FND_MESSAGE.SET_NAME('IGC','IGC_LOGGING_UNEXP_ERROR');
1546 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
1547 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
1548 FND_LOG.MESSAGE ( g_unexp_level,l_full_path, TRUE);
1549 END IF;
1550 APP_EXCEPTION.Raise_Exception;
1551
1552 END Fetch_BPAs ;
1553
1554
1555 -- Validate Distributions
1556 -- ======================
1557 --
1558 -- This procedure validates the distributions of the document being processed.
1559 --
1560 -- IN Parameters
1561 -- -------------
1562 -- p_batch_size User entered value used to determine batch size of bulk fetches
1563 -- p_document_type Type of document: PO, REQ or REL
1564 -- p_document_subtype Subtype of document type: BLANKET, SCHEDULED, PLANNED, STANDARD, etc
1565 -- p_document_id Id of document
1566 --
1567 -- OUT Parameters
1568 -- --------------
1569 --
1570 --
1571 FUNCTION Validate_Distributions(p_batch_size IN NUMBER,
1572 p_document_type IN VARCHAR2,
1573 p_document_subtype IN VARCHAR2,
1574 p_document_id IN NUMBER
1575 ) RETURN VARCHAR2 AS
1576
1577 TYPE valid_dist_rec_type IS RECORD
1578 (line_id igc_tbl_number,
1579 line_location_id igc_tbl_number,
1580 distribution_id igc_tbl_number,
1581 invoice_number igc_tbl_varchar100,
1582 result_error_code igc_tbl_varchar40);
1583
1584 l_valid_dist_rec valid_dist_rec_type ;
1585
1586 TYPE c_valid_dist_type IS REF CURSOR ;
1587
1588 c_valid_dist c_valid_dist_type;
1589
1590 l_msg_data VARCHAR2(2000) := null ;
1591 l_return_status VARCHAR2(1):= FND_API.G_RET_STS_SUCCESS;
1592
1593 l_full_path VARCHAR2(500) := g_path||'Validate_Distributions';
1594 BEGIN
1595
1596 IF (g_debug_mode = 'Y') THEN
1597 Put_Debug_Msg (l_full_path,p_debug_msg => '**** Validate Distributions ****');
1598 END IF;
1599
1600 -- Set cursor to check distributions for Standard PO's
1601 IF p_document_type = 'PO' and p_document_subtype = 'STANDARD'
1602 THEN
1603 OPEN c_valid_dist FOR
1604 SELECT DISTINCT tmp.line_id,
1605 tmp.line_location_id,
1606 tmp.distribution_id,
1607 ai.invoice_num,
1608 DECODE(NVL(aid.match_status_flag, 'N'), 'N', 'IGC_PO_YEP_INV_NAPPR',
1609 'T', 'IGC_PO_YEP_INV_NAPPR',
1610 DECODE(NVL(ai.payment_status_flag, 'N'), 'N', 'IGC_PO_YEP_INV_NPAID',
1611 DECODE(NVL(aip.posted_flag, 'N'), 'N', 'IGC_PO_YEP_INV_PAY_NPOST',
1612 DECODE(SIGN(tmp.quantity_ordered - tmp.quantity_billed), -1,
1613 'IGC_PO_YEP_PO_OVERBILLED')))) result_error_code
1614 FROM ap_invoices ai,
1615 ap_invoice_payments aip,
1616 ap_invoice_distributions aid,
1617 igc_cbc_po_process_gt tmp
1618 WHERE ai.invoice_id = aid.invoice_id
1619 AND aip.invoice_id(+) = ai.invoice_id
1620 AND aid.po_distribution_id = tmp.distribution_id
1621 AND tmp.accrue_on_receipt = 'N'
1622 AND tmp.encumbered_flag = 'Y'
1623 AND tmp.po_header_id = p_document_id
1624 AND ai.cancelled_date IS NULL
1625 ORDER BY result_error_code ASC;
1626
1627 -- Set cursor to check distributions for Scheduled and Blanket Releases
1628 ELSIF p_document_type = 'REL'
1629 AND p_document_subtype IN ('SCHEDULED', 'BLANKET')
1630 THEN
1631 OPEN c_valid_dist FOR
1632 SELECT DISTINCT tmp.line_id,
1633 tmp.line_location_id,
1634 tmp.distribution_id,
1635 ai.invoice_num,
1636 DECODE(NVL(aid.match_status_flag, 'N'), 'N', 'IGC_PO_YEP_INV_NAPPR',
1637 'T', 'IGC_PO_YEP_INV_NAPPR',
1638 DECODE(NVL(ai.payment_status_flag, 'N'), 'N', 'IGC_PO_YEP_INV_NPAID',
1639 DECODE(NVL(aip.posted_flag, 'N'), 'N', 'IGC_PO_YEP_INV_PAY_NPOST',
1640 DECODE(SIGN(tmp.quantity_ordered - tmp.quantity_billed), -1,
1641 'IGC_PO_YEP_REL_OVERBILLED')))) result_error_code
1642 FROM ap_invoices ai,
1643 ap_invoice_payments aip,
1644 ap_invoice_distributions aid,
1645 igc_cbc_po_process_gt tmp
1646 WHERE ai.invoice_id = aid.invoice_id
1647 AND aip.invoice_id(+) = ai.invoice_id
1648 AND aid.po_distribution_id = tmp.distribution_id
1649 AND tmp.accrue_on_receipt = 'N'
1650 AND tmp.encumbered_flag = 'Y'
1651 AND tmp.encumbered_flag = 'Y'
1652 AND tmp.po_release_id = p_document_id
1653 AND ai.cancelled_date IS NULL
1654 ORDER BY result_error_code ASC;
1655
1656 -- If not a Standard PO, Blanket Release or Scheduled Release, then no distributions so return success
1657 ELSE
1658 IF (g_debug_mode = 'Y') THEN
1659 Put_Debug_Msg (l_full_path,p_debug_msg => 'No distributions for this document type');
1660 END IF;
1661 RETURN FND_API.G_RET_STS_SUCCESS;
1662 END IF; -- p_document_type = 'PO' and p_document_subtype = 'STANDARD'
1663
1664 -- fetch distributions in batches determined by user parameter
1665 LOOP
1666 FETCH c_valid_dist BULK COLLECT INTO l_valid_dist_rec.line_id,
1667 l_valid_dist_rec.line_location_id,
1668 l_valid_dist_rec.distribution_id,
1669 l_valid_dist_rec.invoice_number,
1670 l_valid_dist_rec.result_error_code
1671 LIMIT p_batch_size;
1672 -- replaced line below, as this does not work !
1673 -- IF c_valid_dist%NOTFOUND
1674 IF l_valid_dist_rec.distribution_id.FIRST IS NULL
1675 THEN
1676 IF (g_debug_mode = 'Y') THEN
1677 Put_Debug_Msg (l_full_path,p_debug_msg => 'No more distributions retrieved');
1678 END IF;
1679 EXIT ;
1680 END IF;
1681 IF (g_debug_mode = 'Y') THEN
1682 Put_Debug_Msg (l_full_path,p_debug_msg => 'Fetched latest batch');
1683 END IF;
1684
1685 -- Loop through retrieved distribution details
1686 FOR l_index IN l_valid_dist_rec.distribution_id.FIRST .. l_valid_dist_rec.distribution_id.LAST
1687 LOOP
1688 IF (g_debug_mode = 'Y') THEN
1689 Put_Debug_Msg (l_full_path,p_debug_msg => 'distribution id:'||l_valid_dist_rec.distribution_id(l_index));
1690 END IF;
1691 IF l_valid_dist_rec.result_error_code(l_index) IS NOT NULL
1692 THEN
1693 -- report any errors and set return status to error
1694 FND_MESSAGE.set_name('IGC',l_valid_dist_rec.result_error_code(l_index));
1695 IF l_valid_dist_rec.result_error_code(l_index) LIKE 'IGC_PO_YEP_INV_%'
1696 THEN
1697 FND_MESSAGE.set_token('INVOICE_NUM',l_valid_dist_rec.invoice_number(l_index));
1698 END IF;
1699 l_msg_data := FND_MESSAGE.get;
1700 Insert_Exception_Record(p_exception_reason => l_msg_data,
1701 p_exception_code => l_valid_dist_rec.result_error_code(l_index),
1702 p_document_type => p_document_type,
1703 p_document_id => p_document_id,
1704 p_line_id => l_valid_dist_rec.line_id(l_index),
1705 p_line_location_id => l_valid_dist_rec.line_location_id(l_index),
1706 p_distribution_id => l_valid_dist_rec.distribution_id(l_index)
1707 );
1708 l_return_status := FND_API.G_RET_STS_ERROR;
1709 ELSE
1710 EXIT;
1711 END IF; -- result_error_code(l_index) IS NOT NULL
1712 END LOOP;
1713
1714 l_valid_dist_rec.distribution_id.DELETE;
1715
1716 END LOOP;
1717
1718 CLOSE c_valid_dist;
1719
1720 RETURN l_return_status;
1721
1722 EXCEPTION
1723 WHEN OTHERS THEN
1724 IF (FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)) THEN
1725 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, 'Validate_Distributions');
1726 END IF;
1727 IF ( g_unexp_level >= g_debug_level ) THEN
1728 FND_MESSAGE.SET_NAME('IGC','IGC_LOGGING_UNEXP_ERROR');
1729 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
1730 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
1731 FND_LOG.MESSAGE ( g_unexp_level,l_full_path, TRUE);
1732 END IF;
1733 APP_EXCEPTION.Raise_Exception;
1734
1735
1736 END Validate_Distributions ;
1737
1738
1739 -- Procedure Create_Journal_Adjustments
1740 -- ====================================
1741 --
1742 -- This Procedure creates adjustments journals to back out the carried forward encumbrances
1743 -- of backing documents that have already been created by the call to the PO funds checker.
1744 --
1745 -- IN Parameters
1746 -- -------------
1747 -- p_sob_id Set of Books Id
1748 -- p_year Year being processed
1749 -- p_document_type Type of document
1750 -- p_document_subtype Subtype of document
1751 -- p_distribution_id_tbl Table of distribution id's being processed for this document
1752 -- p_prev_year_end_period Name of final period of year being rolled forward
1753 -- p_prev_year_end_num Number of final period of year being rolled forward
1754 -- p_prev_year_end_quarter Number of final quarter of year being rolled forward
1755 -- p_curr_year_start_period Name of final period of new year
1756 -- p_curr_year_end_num Number of final period of new year
1757 -- p_prev_year_end_quarter Number of final quarter of new year
1758 --
1759 -- OUT Parameters
1760 -- --------------
1761 -- x_return_code Indicates the return status of the procedure:
1762 -- 0 - Need to terminate processing successfully
1763 -- 1 - Need to terminate processing with warning
1764 -- 2 - Need to terminate processing with error
1765 -- -99 - Successful, continue processing
1766 --
1767 -- bug 2804025 ssmales 19-Feb-2003 new procedure create_journal_adjustments
1768 --
1769 PROCEDURE Create_Journal_Adjustments(p_sobid IN NUMBER,
1770 p_year IN NUMBER,
1771 p_document_type IN VARCHAR2,
1772 p_document_subtype IN VARCHAR2,
1773 p_distribution_id_tbl IN igc_tbl_number,
1774 p_prev_year_end_period IN VARCHAR2,
1775 p_prev_year_end_num IN NUMBER,
1776 p_prev_year_end_quarter IN NUMBER,
1777 p_curr_year_start_period IN VARCHAR2,
1778 p_curr_year_start_num IN NUMBER,
1779 p_curr_year_start_quarter IN NUMBER,
1780 x_return_code OUT NOCOPY NUMBER
1781 ) AS
1782
1783 l_packet_id NUMBER;
1784 l_result_code BOOLEAN;
1785 l_return_code VARCHAR2(1);
1786
1787 l_full_path VARCHAR2(500) := g_path||'create_journal_adjustments';
1788 BEGIN
1789
1790 IF (g_debug_mode = 'Y') THEN
1791 Put_Debug_Msg (l_full_path,p_debug_msg => '**** Create Journal Adjustments ****');
1792 END IF;
1793
1794 --Process Standard and Planned PO's with Backing Requisitions
1795 IF p_document_type = 'PO' AND p_document_subtype IN ('STANDARD','PLANNED')
1796 THEN
1797 IF (g_debug_mode = 'Y') THEN
1798 Put_Debug_Msg (l_full_path,p_debug_msg => 'Starting Req Adjustments for POs');
1799 END IF;
1800 -- cancel the requisition encumbrance in the previous year
1801 SELECT gl_bc_packets_s.nextval
1802 INTO l_packet_id
1803 FROM DUAL;
1804
1805 FORALL l_index IN p_distribution_id_tbl.FIRST .. p_distribution_id_tbl.LAST
1806 INSERT INTO gl_bc_packets
1807 (
1808 packet_id,
1809 Ledger_id,
1810 je_source_name,
1811 je_category_name,
1812 code_combination_id,
1813 actual_flag,
1814 period_name,
1815 period_year,
1816 period_num,
1817 quarter_num,
1818 currency_code,
1819 status_code,
1820 last_update_date,
1821 last_updated_by,
1822 budget_version_id,
1823 encumbrance_type_id,
1824 entered_dr,
1825 entered_cr,
1826 accounted_dr,
1827 accounted_cr,
1828 ussgl_transaction_code,
1829 reference1,
1830 reference2,
1831 reference3,
1832 reference4,
1833 reference5,
1834 je_line_description
1835 )
1836 SELECT
1837 l_packet_id,
1838 glsob.set_of_books_id,
1839 'Purchasing',
1840 'Requisitions',
1841 prd.budget_account_id,
1842 'E',
1843 p_prev_year_end_period,
1844 p_year,
1845 p_prev_year_end_num,
1846 p_prev_year_end_quarter,
1847 glsob.currency_code,
1848 'P',
1849 sysdate,
1850 g_user_id,
1851 NULL,
1852 fsp.req_encumbrance_type_id,
1853 -1 * (DECODE(base_cur.minimum_accountable_unit,
1854 NULL,
1855 ROUND((prl.unit_price + po_tax_sv.get_tax('REQ',prd.distribution_id)
1856 / prd.req_line_quantity)
1857 * (GREATEST
1858 (
1859 DECODE
1860 (NVL(poll.accrue_on_receipt_flag,'N'),
1861 'N',
1862 (prd.req_line_quantity
1863 - po_uom_s.po_uom_convert_p(pol.unit_meas_lookup_code,
1864 prl.unit_meas_lookup_code,
1865 prl.item_id)
1866 * GREATEST(NVL(pod.quantity_billed,0),
1867 NVL(pod.unencumbered_quantity,0)
1868 )
1869 ),
1870 'Y',
1871 (prd.req_line_quantity
1872 - po_uom_s.po_uom_convert_p(pol.unit_meas_lookup_code,
1873 prl.unit_meas_lookup_code,
1874 prl.item_id)
1875 * GREATEST(NVL(pod.quantity_delivered,0),
1876 NVL(pod.unencumbered_quantity,0))
1877 )
1878 ) -- DECODE
1879 ,0) -- GREATEST
1880 ),
1881 base_cur.precision), -- ROUND
1882
1883 ROUND(((prl.unit_price + po_tax_sv.get_tax('REQ',prd.distribution_id)
1884 / prd.req_line_quantity)
1885 * ((GREATEST
1886 (
1887 DECODE
1888 (NVL(poll.accrue_on_receipt_flag,'N'),
1889 'N',
1890 (prd.req_line_quantity
1891 - po_uom_s.po_uom_convert_p(pol.unit_meas_lookup_code,
1892 prl.unit_meas_lookup_code,
1893 prl.item_id)
1894 * GREATEST(NVL(pod.quantity_billed,0),
1895 NVL(pod.unencumbered_quantity,0)
1896 )
1897 ),
1898 'Y',
1899 (prd.req_line_quantity
1900 - po_uom_s.po_uom_convert_p(pol.unit_meas_lookup_code,
1901 prl.unit_meas_lookup_code,
1902 prl.item_id)
1903 * GREATEST (NVL(pod.quantity_delivered,0),
1904 NVL(pod.unencumbered_quantity,0))
1905
1906 )
1907 ) -- DECODE
1908 ,0) -- GREATEST
1909 ) / base_cur.minimum_accountable_unit)
1910 * base_cur.minimum_accountable_unit),
1911 base_cur.precision))) Entered_Dr,
1912 0 Entered_Cr,
1913 -1 * (DECODE(base_cur.minimum_accountable_unit,
1914 NULL,
1915 ROUND((prl.unit_price + po_tax_sv.get_tax('REQ',prd.distribution_id)
1916 / prd.req_line_quantity)
1917 * (GREATEST
1918 (
1919 DECODE
1920 (NVL(poll.accrue_on_receipt_flag,'N'),
1921 'N',
1922 (prd.req_line_quantity
1923 - po_uom_s.po_uom_convert_p(pol.unit_meas_lookup_code,
1924 prl.unit_meas_lookup_code,
1925 prl.item_id)
1926 * GREATEST(NVL(pod.quantity_billed,0),
1927 NVL(pod.unencumbered_quantity,0)
1928 )
1929 ),
1930 'Y',
1931 (prd.req_line_quantity
1932 - po_uom_s.po_uom_convert_p(pol.unit_meas_lookup_code,
1933 prl.unit_meas_lookup_code,
1934 prl.item_id)
1935 * GREATEST(NVL(pod.quantity_delivered,0),
1936 NVL(pod.unencumbered_quantity,0))
1937 )
1938 ) -- DECODE
1939 ,0) -- GREATEST
1940 ),
1941 base_cur.precision), -- ROUND
1942
1943 ROUND(((prl.unit_price + po_tax_sv.get_tax('REQ',prd.distribution_id)
1944 / prd.req_line_quantity)
1945 * ((GREATEST
1946 (
1947 DECODE
1948 (NVL(poll.accrue_on_receipt_flag,'N'),
1949 'N',
1950 (prd.req_line_quantity
1951 - po_uom_s.po_uom_convert_p(pol.unit_meas_lookup_code,
1952 prl.unit_meas_lookup_code,
1953 prl.item_id)
1954 * GREATEST(NVL(pod.quantity_billed,0),
1955 NVL(pod.unencumbered_quantity,0)
1956 )
1957 ),
1958 'Y',
1959 (prd.req_line_quantity
1960 - po_uom_s.po_uom_convert_p(pol.unit_meas_lookup_code,
1961 prl.unit_meas_lookup_code,
1962 prl.item_id)
1963 * GREATEST(NVL(pod.quantity_delivered,0),
1964 NVL(pod.unencumbered_quantity,0))
1965
1966 )
1967 ) -- DECODE
1968 ,0) -- GREATEST
1969 ) / base_cur.minimum_accountable_unit)
1970 * base_cur.minimum_accountable_unit),
1971 base_cur.precision))) Accounted_Dr,
1972 0 Accounted_Cr,
1973 prd.ussgl_transaction_code,
1974 'REQ',
1975 prl.requisition_header_id,
1976 prd.distribution_id,
1977 prh.segment1,
1978 prl.reference_num,
1979 SUBSTR(prl.item_description,1,25) || '-Year End Process, Adjust Requisition Encumbrance entry'
1980 FROM
1981 fnd_currencies base_cur,
1982 gl_sets_of_books glsob,
1983 financials_system_parameters fsp,
1984 po_requisition_lines prl,
1985 po_req_distributions prd,
1986 po_requisition_headers prh,
1987 po_line_locations poll,
1988 po_distributions pod,
1989 po_lines pol
1990 WHERE
1991 NVL(prl.closed_code,'OPEN') NOT IN ('CANCELLED','FINALLY CLOSED')
1992 AND NVL(prl.cancel_flag,'N') = 'N'
1993 AND NVL(prd.prevent_encumbrance_flag,'N') = 'N'
1994 AND prd.requisition_line_id = prl.requisition_line_id
1995 AND prl.line_location_id = poll.line_location_id
1996 AND pod.line_location_id = poll.line_location_id
1997 AND pod.po_distribution_id = p_distribution_id_tbl(l_index)
1998 AND poll.shipment_type IN ('STANDARD','PLANNED')
1999 AND NVL(poll.cancel_flag,'N') = 'N'
2000 AND NVL(poll.closed_code,'OPEN') <> 'FINALLY CLOSED'
2001 AND base_cur.currency_code = glsob.currency_code
2002 AND fsp.set_of_books_id = glsob.set_of_books_id
2003 AND pol.po_line_id = poll.po_line_id
2004 AND prh.requisition_header_id = prl.requisition_header_id ;
2005
2006 -- COMMIT;
2007
2008 IF (g_debug_mode = 'Y') THEN
2009 Put_Debug_Msg (l_full_path,p_debug_msg => 'Completed insert to gl_bc_packets');
2010 END IF;
2011 l_result_code := gl_funds_checker_pkg.glxfck(
2012 p_sobid => p_sobid,
2013 p_packetid => l_packet_id,
2014 p_mode => 'F',
2015 p_partial_resv_flag => 'N',
2016 p_override => 'N',
2017 p_conc_flag => 'Y',
2018 p_user_id => g_user_id,
2019 p_user_resp_id => g_resp_id,
2020 p_return_code => l_return_code
2021 );
2022 -- COMMIT;
2023
2024 IF NOT(l_result_code) OR (l_return_code NOT IN ('A','S'))
2025 THEN
2026 IF (g_debug_mode = 'Y') THEN
2027 Put_Debug_Msg (l_full_path,p_debug_msg => 'gl funds check failure:'||gl_funds_checker_pkg.get_debug);
2028 END IF;
2029 x_return_code := 2 ;
2030 RETURN;
2031 END IF;
2032 IF (g_debug_mode = 'Y') THEN
2033 Put_Debug_Msg (l_full_path,p_debug_msg => 'Completed prev year adjustment');
2034 END IF;
2035
2036 -- Now repeat the above to cancel the requisition encumbrances in the current year
2037 SELECT gl_bc_packets_s.nextval
2038 INTO l_packet_id
2039 FROM DUAL;
2040
2041 FORALL l_index IN p_distribution_id_tbl.FIRST .. p_distribution_id_tbl.LAST
2042 INSERT INTO gl_bc_packets
2043 (
2044 packet_id,
2045 Ledger_id,
2046 je_source_name,
2047 je_category_name,
2048 code_combination_id,
2049 actual_flag,
2050 period_name,
2051 period_year,
2052 period_num,
2053 quarter_num,
2054 currency_code,
2055 status_code,
2056 last_update_date,
2057 last_updated_by,
2058 budget_version_id,
2059 encumbrance_type_id,
2060 entered_dr,
2061 entered_cr,
2062 accounted_dr,
2063 accounted_cr,
2064 ussgl_transaction_code,
2065 reference1,
2066 reference2,
2067 reference3,
2068 reference4,
2069 reference5,
2070 je_line_description
2071 )
2072 SELECT
2073 l_packet_id,
2074 glsob.set_of_books_id,
2075 'Purchasing',
2076 'Requisitions',
2077 prd.budget_account_id,
2078 'E',
2079 p_curr_year_start_period,
2080 p_year + 1,
2081 p_curr_year_start_num,
2082 p_curr_year_start_quarter,
2083 glsob.currency_code,
2084 'P',
2085 sysdate,
2086 g_user_id,
2087 NULL,
2088 fsp.req_encumbrance_type_id,
2089 0 Entered_Dr,
2090 -1 * (DECODE(base_cur.minimum_accountable_unit,
2091 NULL,
2092 ROUND((prl.unit_price + po_tax_sv.get_tax('REQ',prd.distribution_id)
2093 / prd.req_line_quantity)
2094 * (GREATEST
2095 (
2096 DECODE
2097 (NVL(poll.accrue_on_receipt_flag,'N'),
2098 'N',
2099 (prd.req_line_quantity
2100 - po_uom_s.po_uom_convert_p(pol.unit_meas_lookup_code,
2101 prl.unit_meas_lookup_code,
2102 prl.item_id)
2103 * GREATEST(NVL(pod.quantity_billed,0),
2104 NVL(pod.unencumbered_quantity,0)
2105 )
2106 ),
2107 'Y',
2108 (prd.req_line_quantity
2109 - po_uom_s.po_uom_convert_p(pol.unit_meas_lookup_code,
2110 prl.unit_meas_lookup_code,
2111 prl.item_id)
2112 * GREATEST(NVL(pod.quantity_delivered,0),
2113 NVL(pod.unencumbered_quantity,0))
2114 )
2115 ) -- DECODE
2116 ,0) -- GREATEST
2117 ),
2118 base_cur.precision), -- ROUND
2119
2120 ROUND(((prl.unit_price + po_tax_sv.get_tax('REQ',prd.distribution_id)
2121 / prd.req_line_quantity)
2122 * ((GREATEST
2123 (
2124 DECODE
2125 (NVL(poll.accrue_on_receipt_flag,'N'),
2126 'N',
2127 (prd.req_line_quantity
2128 - po_uom_s.po_uom_convert_p(pol.unit_meas_lookup_code,
2129 prl.unit_meas_lookup_code,
2130 prl.item_id)
2131 * GREATEST(NVL(pod.quantity_billed,0),
2132 NVL(pod.unencumbered_quantity,0)
2133 )
2134 ),
2135 'Y',
2136 (prd.req_line_quantity
2137 - po_uom_s.po_uom_convert_p(pol.unit_meas_lookup_code,
2138 prl.unit_meas_lookup_code,
2139 prl.item_id)
2140 * GREATEST(NVL(pod.quantity_delivered,0),
2141 NVL(pod.unencumbered_quantity,0))
2142 )
2143 ) -- DECODE
2144 ,0) -- GREATEST
2145 ) / base_cur.minimum_accountable_unit)
2146 * base_cur.minimum_accountable_unit),
2147 base_cur.precision))) Entered_Cr,
2148 0 Accounted_Dr,
2149 -1 * (DECODE(base_cur.minimum_accountable_unit,
2150 NULL,
2151 ROUND((prl.unit_price + po_tax_sv.get_tax('REQ',prd.distribution_id)
2152 / prd.req_line_quantity)
2153 * (GREATEST
2154 (
2155 DECODE
2156 (NVL(poll.accrue_on_receipt_flag,'N'),
2157 'N',
2158 (prd.req_line_quantity
2159 - po_uom_s.po_uom_convert_p(pol.unit_meas_lookup_code,
2160 prl.unit_meas_lookup_code,
2161 prl.item_id)
2162 * GREATEST(NVL(pod.quantity_billed,0),
2163 NVL(pod.unencumbered_quantity,0)
2164 )
2165 ),
2166 'Y',
2167 (prd.req_line_quantity
2168 - po_uom_s.po_uom_convert_p(pol.unit_meas_lookup_code,
2169 prl.unit_meas_lookup_code,
2170 prl.item_id)
2171 * GREATEST(NVL(pod.quantity_delivered,0),
2172 NVL(pod.unencumbered_quantity,0))
2173 )
2174 ) -- DECODE
2175 ,0) -- GREATEST
2176 ),
2177 base_cur.precision), -- ROUND
2178
2179 ROUND(((prl.unit_price + po_tax_sv.get_tax('REQ',prd.distribution_id)
2180 / prd.req_line_quantity)
2181 * ((GREATEST
2182 (
2183 DECODE
2184 (NVL(poll.accrue_on_receipt_flag,'N'),
2185 'N',
2186 (prd.req_line_quantity
2187 - po_uom_s.po_uom_convert_p(pol.unit_meas_lookup_code,
2188 prl.unit_meas_lookup_code,
2189 prl.item_id)
2190 * GREATEST(NVL(pod.quantity_billed,0),
2191 NVL(pod.unencumbered_quantity,0)
2192 )
2193 ),
2194 'Y',
2195 (prd.req_line_quantity
2196 - po_uom_s.po_uom_convert_p(pol.unit_meas_lookup_code,
2197 prl.unit_meas_lookup_code,
2198 prl.item_id)
2199 * GREATEST(NVL(pod.quantity_delivered,0),
2200 NVL(pod.unencumbered_quantity,0))
2201 )
2202 ) -- DECODE
2203 ,0) -- GREATEST
2204 ) / base_cur.minimum_accountable_unit)
2205 * base_cur.minimum_accountable_unit),
2206 base_cur.precision))) Accounted_Cr,
2207 prd.ussgl_transaction_code,
2208 'REQ',
2209 prl.requisition_header_id,
2210 prd.distribution_id,
2211 prh.segment1,
2212 prl.reference_num,
2213 SUBSTR(prl.item_description,1,25) || '-Year End Process, Adjust Requisition Encumbrance entry'
2214 FROM
2215 fnd_currencies base_cur,
2216 gl_sets_of_books glsob,
2217 financials_system_parameters fsp,
2218 po_requisition_lines prl,
2219 po_req_distributions prd,
2220 po_requisition_headers prh,
2221 po_line_locations poll,
2222 po_distributions pod,
2223 po_lines pol
2224 WHERE
2225 NVL(prl.closed_code,'OPEN') NOT IN ('CANCELLED','FINALLY CLOSED')
2226 AND NVL(prl.cancel_flag,'N') = 'N'
2227 AND NVL(prd.prevent_encumbrance_flag,'N') = 'N'
2228 AND prd.requisition_line_id = prl.requisition_line_id
2229 AND prl.line_location_id = poll.line_location_id
2230 AND pod.line_location_id = poll.line_location_id
2231 AND pod.po_distribution_id = p_distribution_id_tbl(l_index)
2232 AND poll.shipment_type IN ('STANDARD','PLANNED')
2233 AND NVL(poll.cancel_flag,'N') = 'N'
2234 AND NVL(poll.closed_code,'OPEN') <> 'FINALLY CLOSED'
2235 AND base_cur.currency_code = glsob.currency_code
2236 AND fsp.set_of_books_id = glsob.set_of_books_id
2237 AND pol.po_line_id = poll.po_line_id
2238 AND prh.requisition_header_id = prl.requisition_header_id
2239 ;
2240
2241 -- COMMIT;
2242
2243 IF (g_debug_mode = 'Y') THEN
2244 Put_Debug_Msg (l_full_path,p_debug_msg => 'Completed insert to gl_bc_packets');
2245 END IF;
2246 l_result_code := gl_funds_checker_pkg.glxfck(
2247 p_sobid => p_sobid,
2248 p_packetid => l_packet_id,
2249 p_mode => 'F',
2250 p_partial_resv_flag => 'N',
2251 p_override => 'N',
2252 p_conc_flag => 'Y',
2253 p_user_id => g_user_id,
2254 p_user_resp_id => g_resp_id,
2255 p_return_code => l_return_code
2256 );
2257 -- COMMIT;
2258
2259 IF NOT(l_result_code) OR (l_return_code NOT IN ('A','S'))
2260 THEN
2261 IF (g_debug_mode = 'Y') THEN
2262 Put_Debug_Msg (l_full_path,p_debug_msg => 'gl funds check failure');
2263 END IF;
2264 x_return_code := 2 ;
2265 RETURN;
2266 END IF ;
2267
2268 --Process Blanket Releases with Backing Requisitions
2269 ELSIF p_document_type = 'REL' AND p_document_subtype IN ('BLANKET')
2270 THEN
2271 IF (g_debug_mode = 'Y') THEN
2272 Put_Debug_Msg (l_full_path,p_debug_msg => 'Starting Req Adjustments for Releases');
2273 END IF;
2274 -- cancel the requisition encumbrance in the previous year
2275 SELECT gl_bc_packets_s.nextval
2276 INTO l_packet_id
2277 FROM DUAL;
2278
2279 FORALL l_index IN p_distribution_id_tbl.FIRST .. p_distribution_id_tbl.LAST
2280 INSERT INTO gl_bc_packets
2281 (
2282 packet_id,
2283 Ledger_id,
2284 je_source_name,
2285 je_category_name,
2286 code_combination_id,
2287 actual_flag,
2288 period_name,
2289 period_year,
2290 period_num,
2291 quarter_num,
2292 currency_code,
2293 status_code,
2294 last_update_date,
2295 last_updated_by,
2296 budget_version_id,
2297 encumbrance_type_id,
2298 entered_dr,
2299 entered_cr,
2300 accounted_dr,
2301 accounted_cr,
2302 ussgl_transaction_code,
2303 reference1,
2304 reference2,
2305 reference3,
2306 reference4,
2307 reference5,
2308 je_line_description
2309 )
2310 SELECT
2311 l_packet_id,
2312 glsob.set_of_books_id,
2313 'Purchasing',
2314 'Requisitions',
2315 prd.budget_account_id,
2316 'E',
2317 p_prev_year_end_period,
2318 p_year,
2319 p_prev_year_end_num,
2320 p_prev_year_end_quarter,
2321 glsob.currency_code,
2322 'P',
2323 sysdate,
2324 g_user_id,
2325 NULL,
2326 fsp.req_encumbrance_type_id,
2327 -1 * (DECODE(base_cur.minimum_accountable_unit,
2328 NULL,
2329 ROUND((prl.unit_price + po_tax_sv.get_tax('REQ',prd.distribution_id)
2330 / prd.req_line_quantity)
2331 * (GREATEST
2332 (
2333 DECODE
2334 (NVL(poll.accrue_on_receipt_flag,'N'),
2335 'N',
2336 (prd.req_line_quantity
2337 - po_uom_s.po_uom_convert_p(pol.unit_meas_lookup_code,
2338 prl.unit_meas_lookup_code,
2339 prl.item_id)
2340 * GREATEST(NVL(pod.quantity_billed,0),
2341 NVL(pod.quantity_delivered,0)
2342 )
2343 ),
2344 'Y',
2345 (prd.req_line_quantity
2346 - po_uom_s.po_uom_convert_p(pol.unit_meas_lookup_code,
2347 prl.unit_meas_lookup_code,
2348 prl.item_id)
2349 * NVL(pod.quantity_delivered,0)
2350 )
2351 ) -- DECODE
2352 ,0) -- GREATEST
2353 ),
2354 base_cur.precision), -- ROUND
2355
2356 ROUND(((prl.unit_price + po_tax_sv.get_tax('REQ',prd.distribution_id)
2357 / prd.req_line_quantity)
2358 * ((GREATEST
2359 (
2360 DECODE
2361 (NVL(poll.accrue_on_receipt_flag,'N'),
2362 'N',
2363 (prd.req_line_quantity
2364 - po_uom_s.po_uom_convert_p(pol.unit_meas_lookup_code,
2365 prl.unit_meas_lookup_code,
2366 prl.item_id)
2367 * GREATEST(NVL(pod.quantity_billed,0),
2368 NVL(pod.quantity_delivered,0)
2369 )
2370 ),
2371 'Y',
2372 (prd.req_line_quantity
2373 - po_uom_s.po_uom_convert_p(pol.unit_meas_lookup_code,
2374 prl.unit_meas_lookup_code,
2375 prl.item_id)
2376 * NVL(pod.quantity_delivered,0)
2377 )
2378 ) -- DECODE
2379 ,0) -- GREATEST
2380 ) / base_cur.minimum_accountable_unit)
2381 * base_cur.minimum_accountable_unit),
2382 base_cur.precision))) Entered_Dr,
2383 0 Entered_Cr,
2384 -1 * (DECODE(base_cur.minimum_accountable_unit,
2385 NULL,
2386 ROUND((prl.unit_price + po_tax_sv.get_tax('REQ',prd.distribution_id)
2387 / prd.req_line_quantity)
2388 * (GREATEST
2389 (
2390 DECODE
2391 (NVL(poll.accrue_on_receipt_flag,'N'),
2392 'N',
2393 (prd.req_line_quantity
2394 - po_uom_s.po_uom_convert_p(pol.unit_meas_lookup_code,
2395 prl.unit_meas_lookup_code,
2396 prl.item_id)
2397 * GREATEST(NVL(pod.quantity_billed,0),
2398 NVL(pod.quantity_delivered,0)
2399 )
2400 ),
2401 'Y',
2402 (prd.req_line_quantity
2403 - po_uom_s.po_uom_convert_p(pol.unit_meas_lookup_code,
2404 prl.unit_meas_lookup_code,
2405 prl.item_id)
2406 * NVL(pod.quantity_delivered,0)
2407 )
2408 ) -- DECODE
2409 ,0) -- GREATEST
2410 ),
2411 base_cur.precision), -- ROUND
2412
2413 ROUND(((prl.unit_price + po_tax_sv.get_tax('REQ',prd.distribution_id)
2414 / prd.req_line_quantity)
2415 * ((GREATEST
2416 (
2417 DECODE
2418 (NVL(poll.accrue_on_receipt_flag,'N'),
2419 'N',
2420 (prd.req_line_quantity
2421 - po_uom_s.po_uom_convert_p(pol.unit_meas_lookup_code,
2422 prl.unit_meas_lookup_code,
2423 prl.item_id)
2424 * GREATEST(NVL(pod.quantity_billed,0),
2425 NVL(pod.quantity_delivered,0)
2426 )
2427 ),
2428 'Y',
2429 (prd.req_line_quantity
2430 - po_uom_s.po_uom_convert_p(pol.unit_meas_lookup_code,
2431 prl.unit_meas_lookup_code,
2432 prl.item_id)
2433 * NVL(pod.quantity_delivered,0)
2434 )
2435 ) -- DECODE
2436 ,0) -- GREATEST
2437 ) / base_cur.minimum_accountable_unit)
2438 * base_cur.minimum_accountable_unit),
2439 base_cur.precision))) Accounted_Dr,
2440 0 Accounted_Cr,
2441 prd.ussgl_transaction_code,
2442 'REQ',
2443 prl.requisition_header_id,
2444 prd.distribution_id,
2445 prh.segment1,
2446 prl.reference_num,
2447 SUBSTR(prl.item_description,1,25) || '-Year End Process, Adjust Requisition Encumbrance entry'
2448 FROM
2449 fnd_currencies base_cur,
2450 gl_sets_of_books glsob,
2451 financials_system_parameters fsp,
2452 po_requisition_lines prl,
2453 po_req_distributions prd,
2454 po_requisition_headers prh,
2455 po_line_locations poll,
2456 po_distributions pod,
2457 po_lines pol
2458 WHERE
2459 NVL(prl.closed_code,'OPEN') NOT IN ('CANCELLED','FINALLY CLOSED')
2460 AND NVL(prl.cancel_flag,'N') = 'N'
2461 AND NVL(prd.prevent_encumbrance_flag,'N') = 'N'
2462 AND prd.requisition_line_id = prl.requisition_line_id
2463 AND prl.line_location_id = poll.line_location_id
2464 AND pod.line_location_id = poll.line_location_id
2465 AND pod.po_distribution_id = p_distribution_id_tbl(l_index)
2466 AND NVL(poll.cancel_flag,'N') = 'N'
2467 AND NVL(poll.closed_code,'OPEN') <> 'FINALLY CLOSED'
2468 AND base_cur.currency_code = glsob.currency_code
2469 AND fsp.set_of_books_id = glsob.set_of_books_id
2470 AND pol.po_line_id = poll.po_line_id
2471 AND poll.shipment_type IN ('BLANKET')
2472 AND prh.requisition_header_id = prl.requisition_header_id
2473 ;
2474
2475 l_result_code := gl_funds_checker_pkg.glxfck(
2476 p_sobid => p_sobid,
2477 p_packetid => l_packet_id,
2478 p_mode => 'F',
2479 p_partial_resv_flag => 'N',
2480 p_override => 'N',
2481 p_conc_flag => 'Y',
2482 p_user_id => g_user_id,
2483 p_user_resp_id => g_resp_id,
2484 p_return_code => l_return_code
2485 );
2486 IF NOT(l_result_code) OR (l_return_code NOT IN ('A','S'))
2487 THEN
2488 x_return_code := 2 ;
2489 RETURN;
2490 END IF;
2491 IF (g_debug_mode = 'Y') THEN
2492 Put_Debug_Msg (l_full_path,p_debug_msg => 'Completed prev year adjustment');
2493 END IF;
2494
2495 -- Now repeat the above to cancel the requisition encumbrances in the current year
2496 SELECT gl_bc_packets_s.nextval
2497 INTO l_packet_id
2498 FROM DUAL;
2499
2500 FORALL l_index IN p_distribution_id_tbl.FIRST .. p_distribution_id_tbl.LAST
2501 INSERT INTO gl_bc_packets
2502 (
2503 packet_id,
2504 Ledger_id,
2505 je_source_name,
2506 je_category_name,
2507 code_combination_id,
2508 actual_flag,
2509 period_name,
2510 period_year,
2511 period_num,
2512 quarter_num,
2513 currency_code,
2514 status_code,
2515 last_update_date,
2516 last_updated_by,
2517 budget_version_id,
2518 encumbrance_type_id,
2519 entered_dr,
2520 entered_cr,
2521 accounted_dr,
2522 accounted_cr,
2523 ussgl_transaction_code,
2524 reference1,
2525 reference2,
2526 reference3,
2527 reference4,
2528 reference5,
2529 je_line_description
2530 )
2531 SELECT
2532 l_packet_id,
2533 glsob.set_of_books_id,
2534 'Purchasing',
2535 'Requisitions',
2536 prd.budget_account_id,
2537 'E',
2538 p_curr_year_start_period,
2539 p_year + 1,
2540 p_curr_year_start_num,
2541 p_curr_year_start_quarter,
2542 glsob.currency_code,
2543 'P',
2544 sysdate,
2545 g_user_id,
2546 NULL,
2547 fsp.req_encumbrance_type_id,
2548 0 Entered_Dr,
2549 -1 * (DECODE(base_cur.minimum_accountable_unit,
2550 NULL,
2551 ROUND((prl.unit_price + po_tax_sv.get_tax('REQ',prd.distribution_id)
2552 / prd.req_line_quantity)
2553 * (GREATEST
2554 (
2555 DECODE
2556 (NVL(poll.accrue_on_receipt_flag,'N'),
2557 'N',
2558 (prd.req_line_quantity
2559 - po_uom_s.po_uom_convert_p(pol.unit_meas_lookup_code,
2560 prl.unit_meas_lookup_code,
2561 prl.item_id)
2562 * GREATEST(NVL(pod.quantity_billed,0),
2563 NVL(pod.quantity_delivered,0)
2564 )
2565 ),
2566 'Y',
2567 (prd.req_line_quantity
2568 - po_uom_s.po_uom_convert_p(pol.unit_meas_lookup_code,
2569 prl.unit_meas_lookup_code,
2570 prl.item_id)
2571 * NVL(pod.quantity_delivered,0)
2572 )
2573 ) -- DECODE
2574 ,0) -- GREATEST
2575 ),
2576 base_cur.precision), -- ROUND
2577
2578 ROUND(((prl.unit_price + po_tax_sv.get_tax('REQ',prd.distribution_id)
2579 / prd.req_line_quantity)
2580 * ((GREATEST
2581 (
2582 DECODE
2583 (NVL(poll.accrue_on_receipt_flag,'N'),
2584 'N',
2585 (prd.req_line_quantity
2586 - po_uom_s.po_uom_convert_p(pol.unit_meas_lookup_code,
2587 prl.unit_meas_lookup_code,
2588 prl.item_id)
2589 * GREATEST(NVL(pod.quantity_billed,0),
2590 NVL(pod.quantity_delivered,0)
2591 )
2592 ),
2593 'Y',
2594 (prd.req_line_quantity
2595 - po_uom_s.po_uom_convert_p(pol.unit_meas_lookup_code,
2596 prl.unit_meas_lookup_code,
2597 prl.item_id)
2598 * NVL(pod.quantity_delivered,0)
2599 )
2600 ) -- DECODE
2601 ,0) -- GREATEST
2602 ) / base_cur.minimum_accountable_unit)
2603 * base_cur.minimum_accountable_unit),
2604 base_cur.precision))) Entered_Cr,
2605 0 Accounted_Dr,
2606 -1 * (DECODE(base_cur.minimum_accountable_unit,
2607 NULL,
2608 ROUND((prl.unit_price + po_tax_sv.get_tax('REQ',prd.distribution_id)
2609 / prd.req_line_quantity)
2610 * (GREATEST
2611 (
2612 DECODE
2613 (NVL(poll.accrue_on_receipt_flag,'N'),
2614 'N',
2615 (prd.req_line_quantity
2616 - po_uom_s.po_uom_convert_p(pol.unit_meas_lookup_code,
2617 prl.unit_meas_lookup_code,
2618 prl.item_id)
2619 * GREATEST(NVL(pod.quantity_billed,0),
2620 NVL(pod.quantity_delivered,0)
2621 )
2622 ),
2623 'Y',
2624 (prd.req_line_quantity
2625 - po_uom_s.po_uom_convert_p(pol.unit_meas_lookup_code,
2626 prl.unit_meas_lookup_code,
2627 prl.item_id)
2628 * NVL(pod.quantity_delivered,0)
2629 )
2630 ) -- DECODE
2631 ,0) -- GREATEST
2632 ),
2633 base_cur.precision), -- ROUND
2634
2635 ROUND(((prl.unit_price + po_tax_sv.get_tax('REQ',prd.distribution_id)
2636 / prd.req_line_quantity)
2637 * ((GREATEST
2638 (
2639 DECODE
2640 (NVL(poll.accrue_on_receipt_flag,'N'),
2641 'N',
2642 (prd.req_line_quantity
2643 - po_uom_s.po_uom_convert_p(pol.unit_meas_lookup_code,
2644 prl.unit_meas_lookup_code,
2645 prl.item_id)
2646 * GREATEST(NVL(pod.quantity_billed,0),
2647 NVL(pod.quantity_delivered,0)
2648 )
2649 ),
2650 'Y',
2651 (prd.req_line_quantity
2652 - po_uom_s.po_uom_convert_p(pol.unit_meas_lookup_code,
2653 prl.unit_meas_lookup_code,
2654 prl.item_id)
2655 * NVL(pod.quantity_delivered,0)
2656 )
2657 ) -- DECODE
2658 ,0) -- GREATEST
2659 ) / base_cur.minimum_accountable_unit)
2660 * base_cur.minimum_accountable_unit),
2661 base_cur.precision))) Accounted_Cr,
2662 prd.ussgl_transaction_code,
2663 'REQ',
2664 prl.requisition_header_id,
2665 prd.distribution_id,
2666 prh.segment1,
2667 prl.reference_num,
2668 SUBSTR(prl.item_description,1,25) || '-Year End Process, Adjust Requisition Encumbrance entry'
2669 FROM
2670 fnd_currencies base_cur,
2671 gl_sets_of_books glsob,
2672 financials_system_parameters fsp,
2673 po_requisition_lines prl,
2674 po_req_distributions prd,
2675 po_requisition_headers prh,
2676 po_line_locations poll,
2677 po_distributions pod,
2678 po_lines pol
2679 WHERE
2680 NVL(prl.closed_code,'OPEN') NOT IN ('CANCELLED','FINALLY CLOSED')
2681 AND NVL(prl.cancel_flag,'N') = 'N'
2682 AND NVL(prd.prevent_encumbrance_flag,'N') = 'N'
2683 AND prd.requisition_line_id = prl.requisition_line_id
2684 AND prl.line_location_id = poll.line_location_id
2685 AND pod.line_location_id = poll.line_location_id
2686 AND pod.po_distribution_id = p_distribution_id_tbl(l_index)
2687 -- AND poll.shipment_type IN ('STANDARD','PLANNED')
2688 AND NVL(poll.cancel_flag,'N') = 'N'
2689 AND NVL(poll.closed_code,'OPEN') <> 'FINALLY CLOSED'
2690 AND base_cur.currency_code = glsob.currency_code
2691 AND fsp.set_of_books_id = glsob.set_of_books_id
2692 AND pol.po_line_id = poll.po_line_id
2693 AND poll.shipment_type IN ('BLANKET')
2694 AND prh.requisition_header_id = prl.requisition_header_id
2695 ;
2696
2697 l_result_code := gl_funds_checker_pkg.glxfck(
2698 p_sobid => p_sobid,
2699 p_packetid => l_packet_id,
2700 p_mode => 'F',
2701 p_partial_resv_flag => 'N',
2702 p_override => 'N',
2703 p_conc_flag => 'Y',
2704 p_user_id => g_user_id,
2705 p_user_resp_id => g_resp_id,
2706 p_return_code => l_return_code
2707 );
2708 IF NOT(l_result_code) OR (l_return_code NOT IN ('A','S'))
2709 THEN
2710 x_return_code := 2 ;
2711 RETURN;
2712 END IF ;
2713
2714
2715 --Process Scheduled Releases backed by Planned PO's
2716 ELSIF p_document_type = 'REL' AND p_document_subtype IN ('SCHEDULED')
2717 THEN
2718 IF (g_debug_mode = 'Y') THEN
2719 Put_Debug_Msg (l_full_path,p_debug_msg => 'Starting PO Adjustments for Releases');
2720 END IF;
2721 -- cancel the PO encumbrance in the previous year
2722 SELECT gl_bc_packets_s.nextval
2723 INTO l_packet_id
2724 FROM DUAL;
2725
2726 FORALL l_index IN p_distribution_id_tbl.FIRST .. p_distribution_id_tbl.LAST
2727 INSERT INTO gl_bc_packets
2728 (
2729 packet_id,
2730 Ledger_id,
2731 je_source_name,
2732 je_category_name,
2733 code_combination_id,
2734 actual_flag,
2735 period_name,
2736 period_year,
2737 period_num,
2738 quarter_num,
2739 currency_code,
2740 status_code,
2741 last_update_date,
2742 last_updated_by,
2743 budget_version_id,
2744 encumbrance_type_id,
2745 entered_dr,
2746 entered_cr,
2747 accounted_dr,
2748 accounted_cr,
2749 ussgl_transaction_code,
2750 reference1,
2751 reference2,
2752 reference3,
2753 reference4,
2754 reference5,
2755 je_line_description
2756 )
2757 SELECT
2758 l_packet_id,
2759 glsob.set_of_books_id,
2760 'Purchasing',
2761 'Purchases',
2762 prd.budget_account_id,
2763 'E',
2764 p_prev_year_end_period,
2765 p_year,
2766 p_prev_year_end_num,
2767 p_prev_year_end_quarter,
2768 glsob.currency_code,
2769 'P',
2770 sysdate,
2771 g_user_id,
2772 NULL,
2773 fsp.purch_encumbrance_type_id,
2774 -1 * (DECODE(base_cur.minimum_accountable_unit,
2775 NULL,
2776 ROUND((poll.price_override + po_tax_sv.get_tax('PO',pod.po_distribution_id)
2777 / pod.quantity_ordered)
2778 * NVL(pod.rate,1)
2779 * (
2780 DECODE
2781 (NVL(prll.accrue_on_receipt_flag,'N'),
2782 'N',
2783 (prd.quantity_ordered
2784 - GREATEST(NVL(prd.quantity_billed,0),
2785 NVL(pod.quantity_delivered,0)
2786 )
2787 ),
2788 'Y',
2789 (prd.quantity_ordered
2790 - NVL(prd.quantity_delivered,0)
2791 )
2792 ) -- DECODE
2793 ),
2794 base_cur.precision), -- ROUND
2795
2796 ROUND(((poll.price_override + po_tax_sv.get_tax('PO',pod.po_distribution_id)
2797 / pod.quantity_ordered)
2798 * NVL(pod.rate,1)
2799 * ((
2800 DECODE
2801 (NVL(prll.accrue_on_receipt_flag,'N'),
2802 'N',
2803 (prd.quantity_ordered
2804 - GREATEST(NVL(prd.quantity_billed,0),
2805 NVL(pod.quantity_delivered,0)
2806 )
2807 ),
2808 'Y',
2809 (prd.quantity_ordered
2810 - NVL(prd.quantity_delivered,0)
2811 )
2812 ) -- DECODE
2813 ) / base_cur.minimum_accountable_unit)
2814 * base_cur.minimum_accountable_unit),
2815 base_cur.precision))) Entered_Dr,
2816 0 Entered_Cr,
2817 -1 * (DECODE(base_cur.minimum_accountable_unit,
2818 NULL,
2819 ROUND((poll.price_override + po_tax_sv.get_tax('PO',pod.po_distribution_id)
2820 / pod.quantity_ordered)
2821 * NVL(pod.rate,1)
2822 * (
2823 DECODE
2824 (NVL(prll.accrue_on_receipt_flag,'N'),
2825 'N',
2826 (prd.quantity_ordered
2827 - GREATEST(NVL(prd.quantity_billed,0),
2828 NVL(pod.quantity_delivered,0)
2829 )
2830 ),
2831 'Y',
2832 (prd.quantity_ordered
2833 - NVL(prd.quantity_delivered,0)
2834 )
2835 ) -- DECODE
2836 ),
2837 base_cur.precision), -- ROUND
2838
2839 ROUND(((poll.price_override + po_tax_sv.get_tax('PO',pod.po_distribution_id)
2840 / pod.quantity_ordered)
2841 * NVL(pod.rate,1)
2842 * ((
2843 DECODE
2844 (NVL(prll.accrue_on_receipt_flag,'N'),
2845 'N',
2846 (prd.quantity_ordered
2847 - GREATEST(NVL(prd.quantity_billed,0),
2848 NVL(pod.quantity_delivered,0)
2849 )
2850 ),
2851 'Y',
2852 (prd.quantity_ordered
2853 - NVL(prd.quantity_delivered,0)
2854 )
2855 ) -- DECODE
2856 ) / base_cur.minimum_accountable_unit)
2857 * base_cur.minimum_accountable_unit),
2858 base_cur.precision))) Accounted_Dr,
2859 0 Accounted_Cr,
2860 prd.ussgl_transaction_code,
2861 'PO',
2862 poh.po_header_id,
2863 pod.po_distribution_id,
2864 poh.segment1,
2865 NULL,
2866 SUBSTR(pol.item_description,1,25) || '-Year End Process, Adjust Planned PO Encumbrance entry'
2867 FROM
2868 fnd_currencies base_cur,
2869 gl_sets_of_books glsob,
2870 financials_system_parameters fsp,
2871 po_headers poh,
2872 po_line_locations poll,
2873 po_line_locations prll,
2874 po_distributions pod,
2875 po_distributions prd,
2876 po_lines pol
2877 WHERE
2878 NVL(poll.closed_code,'OPEN') <> ('FINALLY CLOSED')
2879 AND NVL(poll.cancel_flag,'N') = 'N'
2880 AND poh.po_header_id = poll.po_header_id
2881 AND poll.line_location_id = pod.line_location_id
2882 AND pod.po_distribution_id = prd.source_distribution_id
2883 AND prd.po_distribution_id = p_distribution_id_tbl(l_index)
2884 AND NVL(prd.prevent_encumbrance_flag,'N') = 'N'
2885 AND NVL(prd.encumbered_flag,'N') = 'Y'
2886 AND NVL(prll.cancel_flag,'N') = 'N'
2887 AND NVL(prll.closed_code,'OPEN') <> ('FINALLY CLOSED')
2888 AND prll.shipment_type IN ('SCHEDULED')
2889 AND prll.line_location_id = prd.line_location_id
2890 AND base_cur.currency_code = glsob.currency_code
2891 AND fsp.set_of_books_id = glsob.set_of_books_id
2892 AND pol.po_line_id = poll.po_line_id
2893 ;
2894
2895 l_result_code := gl_funds_checker_pkg.glxfck(
2896 p_sobid => p_sobid,
2897 p_packetid => l_packet_id,
2898 p_mode => 'F',
2899 p_partial_resv_flag => 'N',
2900 p_override => 'N',
2901 p_conc_flag => 'Y',
2902 p_user_id => g_user_id,
2903 p_user_resp_id => g_resp_id,
2904 p_return_code => l_return_code
2905 );
2906 IF NOT(l_result_code) OR (l_return_code NOT IN ('A','S'))
2907 THEN
2908 x_return_code := 2 ;
2909 RETURN;
2910 END IF;
2911 IF (g_debug_mode = 'Y') THEN
2912 Put_Debug_Msg (l_full_path,p_debug_msg => 'Completed prev year adjustment');
2913 END IF;
2914
2915 -- Now repeat the above to cancel the requisition encumbrances in the current year
2916 SELECT gl_bc_packets_s.nextval
2917 INTO l_packet_id
2918 FROM DUAL;
2919
2920 FORALL l_index IN p_distribution_id_tbl.FIRST .. p_distribution_id_tbl.LAST
2921 INSERT INTO gl_bc_packets
2922 (
2923 packet_id,
2924 Ledger_id,
2925 je_source_name,
2926 je_category_name,
2927 code_combination_id,
2928 actual_flag,
2929 period_name,
2930 period_year,
2931 period_num,
2932 quarter_num,
2933 currency_code,
2934 status_code,
2935 last_update_date,
2936 last_updated_by,
2937 budget_version_id,
2938 encumbrance_type_id,
2939 entered_dr,
2940 entered_cr,
2941 accounted_dr,
2942 accounted_cr,
2943 ussgl_transaction_code,
2944 reference1,
2945 reference2,
2946 reference3,
2947 reference4,
2948 reference5,
2949 je_line_description
2950 )
2951 SELECT
2952 l_packet_id,
2953 glsob.set_of_books_id,
2954 'Purchasing',
2955 'Purchases',
2956 prd.budget_account_id,
2957 'E',
2958 p_curr_year_start_period,
2959 p_year + 1,
2960 p_curr_year_start_num,
2961 p_curr_year_start_quarter,
2962 glsob.currency_code,
2963 'P',
2964 sysdate,
2965 g_user_id,
2966 NULL,
2967 fsp.purch_encumbrance_type_id,
2968 0 Entered_Dr,
2969 -1 * (DECODE(base_cur.minimum_accountable_unit,
2970 NULL,
2971 ROUND((poll.price_override + po_tax_sv.get_tax('PO',pod.po_distribution_id)
2972 / pod.quantity_ordered)
2973 * NVL(pod.rate,1)
2974 * (
2975 DECODE
2976 (NVL(prll.accrue_on_receipt_flag,'N'),
2977 'N',
2978 (prd.quantity_ordered
2979 - GREATEST(NVL(prd.quantity_billed,0),
2980 NVL(pod.quantity_delivered,0)
2981 )
2982 ),
2983 'Y',
2984 (prd.quantity_ordered
2985 - NVL(prd.quantity_delivered,0)
2986 )
2987 ) -- DECODE
2988 ),
2989 base_cur.precision), -- ROUND
2990
2991 ROUND(((poll.price_override + po_tax_sv.get_tax('PO',pod.po_distribution_id)
2992 / pod.quantity_ordered)
2993 * NVL(pod.rate,1)
2994 * ((
2995 DECODE
2996 (NVL(prll.accrue_on_receipt_flag,'N'),
2997 'N',
2998 (prd.quantity_ordered
2999 - GREATEST(NVL(prd.quantity_billed,0),
3000 NVL(pod.quantity_delivered,0)
3001 )
3002 ),
3003 'Y',
3004 (prd.quantity_ordered
3005 - NVL(prd.quantity_delivered,0)
3006 )
3007 ) -- DECODE
3008 ) / base_cur.minimum_accountable_unit)
3009 * base_cur.minimum_accountable_unit),
3010 base_cur.precision))) Entered_Cr,
3011 0 Accounted_Dr,
3012 -1 * (DECODE(base_cur.minimum_accountable_unit,
3013 NULL,
3014 ROUND((poll.price_override + po_tax_sv.get_tax('PO',pod.po_distribution_id)
3015 / pod.quantity_ordered)
3016 * NVL(pod.rate,1)
3017 * (
3018 DECODE
3019 (NVL(prll.accrue_on_receipt_flag,'N'),
3020 'N',
3021 (prd.quantity_ordered
3022 - GREATEST(NVL(prd.quantity_billed,0),
3023 NVL(pod.quantity_delivered,0)
3024 )
3025 ),
3026 'Y',
3027 (prd.quantity_ordered
3028 - NVL(prd.quantity_delivered,0)
3029 )
3030 ) -- DECODE
3031 ),
3032 base_cur.precision), -- ROUND
3033
3034 ROUND(((poll.price_override + po_tax_sv.get_tax('PO',pod.po_distribution_id)
3035 / pod.quantity_ordered)
3036 * NVL(pod.rate,1)
3037 * ((
3038 DECODE
3039 (NVL(prll.accrue_on_receipt_flag,'N'),
3040 'N',
3041 (prd.quantity_ordered
3042 - GREATEST(NVL(prd.quantity_billed,0),
3043 NVL(pod.quantity_delivered,0)
3044 )
3045 ),
3046 'Y',
3047 (prd.quantity_ordered
3048 - NVL(prd.quantity_delivered,0)
3049 )
3050 ) -- DECODE
3051 ) / base_cur.minimum_accountable_unit)
3052 * base_cur.minimum_accountable_unit),
3053 base_cur.precision))) Accounted_Cr,
3054 prd.ussgl_transaction_code,
3055 'PO',
3056 poh.po_header_id,
3057 pod.po_distribution_id,
3058 poh.segment1,
3059 NULL,
3060 SUBSTR(pol.item_description,1,25) || '-Year End Process, Adjust Planned PO Encumbrance entry'
3061 FROM
3062 fnd_currencies base_cur,
3063 gl_sets_of_books glsob,
3064 financials_system_parameters fsp,
3065 po_headers poh,
3066 po_line_locations poll,
3067 po_line_locations prll,
3068 po_distributions pod,
3069 po_distributions prd,
3070 po_lines pol
3071 WHERE
3072 NVL(poll.closed_code,'OPEN') <> ('FINALLY CLOSED')
3073 AND NVL(poll.cancel_flag,'N') = 'N'
3074 AND poh.po_header_id = poll.po_header_id
3075 AND poll.line_location_id = pod.line_location_id
3076 AND pod.po_distribution_id = prd.source_distribution_id
3077 AND prd.po_distribution_id = p_distribution_id_tbl(l_index)
3078 AND NVL(prd.prevent_encumbrance_flag,'N') = 'N'
3079 AND NVL(prd.encumbered_flag,'N') = 'Y'
3080 AND NVL(prll.cancel_flag,'N') = 'N'
3081 AND NVL(prll.closed_code,'OPEN') <> ('FINALLY CLOSED')
3082 AND prll.shipment_type IN ('SCHEDULED')
3083 AND prll.line_location_id = prd.line_location_id
3084 AND base_cur.currency_code = glsob.currency_code
3085 AND fsp.set_of_books_id = glsob.set_of_books_id
3086 AND pol.po_line_id = poll.po_line_id
3087 ;
3088
3089 l_result_code := gl_funds_checker_pkg.glxfck(
3090 p_sobid => p_sobid,
3091 p_packetid => l_packet_id,
3092 p_mode => 'F',
3093 p_partial_resv_flag => 'N',
3094 p_override => 'N',
3095 p_conc_flag => 'Y',
3096 p_user_id => g_user_id,
3097 p_user_resp_id => g_resp_id,
3098 p_return_code => l_return_code
3099 );
3100 IF NOT(l_result_code) OR (l_return_code NOT IN ('A','S'))
3101 THEN
3102 x_return_code := 2 ;
3103 RETURN;
3104 END IF ;
3105
3106 END IF;
3107
3108 IF (g_debug_mode = 'Y') THEN
3109 Put_Debug_Msg (l_full_path,p_debug_msg => 'Completed journal adjustments');
3110 END IF;
3111 x_return_code := -99 ;
3112
3113 EXCEPTION
3114 WHEN OTHERS THEN
3115 IF (FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)) THEN
3116 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, 'Create Journal Adjustment');
3117 END IF;
3118 IF ( g_unexp_level >= g_debug_level ) THEN
3119 FND_MESSAGE.SET_NAME('IGC','IGC_LOGGING_UNEXP_ERROR');
3120 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
3121 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
3122 FND_LOG.MESSAGE ( g_unexp_level,l_full_path, TRUE);
3123 END IF;
3124 APP_EXCEPTION.Raise_Exception;
3125 END Create_Journal_Adjustments;
3126
3127
3128
3129 -- Procedure Process_Document
3130 -- ==========================
3131 --
3132 -- This Procedure processes the document when the process has been called in Final Mode.
3133 -- This entails unreserving the encumbrance in the previous year and reserving in the current year.
3134 -- The CBC Acct Date of the document is also updated to the new year, as well as the GL date of any
3135 -- relevant distributions
3136 --
3137 -- IN Parameters
3138 -- -------------
3139 -- p_sob_id Set of Books Id
3140 -- p_org_id Org Id
3141 -- p_year Year being processed
3142 -- p_process_phase User entered processing phase: F - Final, P - Preliminary
3143 -- p_document_type Type of document: PO, REQ or REL
3144 -- p_document_subtype Subtype of document type: BLANKET, SCHEDULED, PLANNED, STANDARD, etc
3145 -- p_document_id Id of document
3146 -- p_prev_year_end_date End Date of year being closed
3147 -- p_prev_year_end_period End period name of year being closed
3148 -- p_prev_year_end_num End period number of year being closed
3149 -- p_prev_year_end_quarter End quarter number of year being closed
3150 -- p_curr_year_start_date Start Date of current year
3151 -- p_curr_year_start_period First period of current year
3152 -- p_curr_year_start_num Start period number of current year
3153 -- p_curr_year_start_quarter Start quarter number of current year
3154 --
3155 -- OUT Parameters
3156 -- --------------
3157 -- x_return_code Indicates the return status of the procedure:
3158 -- 0 - Need to terminate processing successfully
3159 -- 1 - Need to terminate processing with warning
3160 -- 2 - Need to terminate processing with error
3161 -- -99 - Successful, continue processing
3162 -- x_msg_buf stores any error message encountered
3163 --
3164 --
3165 -- bug 2804025 ssmales 19-Feb-2003 added new params
3166 PROCEDURE Process_Document(p_sobid IN NUMBER,
3167 p_org_id IN NUMBER,
3168 p_year IN NUMBER,
3169 p_process_phase IN VARCHAR2,
3170 p_document_type IN VARCHAR2,
3171 p_document_subtype IN VARCHAR2,
3172 p_document_id IN NUMBER,
3173 p_prev_year_end_date IN DATE,
3174 p_prev_year_end_period IN VARCHAR2,
3175 p_prev_year_end_num IN NUMBER,
3176 p_prev_year_end_quarter IN NUMBER,
3177 p_prev_cbc_acct_date IN DATE,
3178 p_curr_year_start_date IN DATE,
3179 p_curr_year_start_period IN VARCHAR2,
3180 p_curr_year_start_num IN NUMBER,
3181 p_curr_year_start_quarter IN NUMBER,
3182 x_return_code OUT NOCOPY NUMBER,
3183 x_msg_buf OUT NOCOPY VARCHAR2
3184 ) AS
3185
3186 -- need to initialize following tables, values are irrelevant
3187 l_distribution_id_tbl igc_tbl_number := igc_tbl_number(0);
3188 l_gl_enc_date_tbl igc_tbl_date := igc_tbl_date(sysdate);
3189 l_gl_enc_prd_tbl igc_tbl_varchar30 := igc_tbl_varchar30(null);
3190
3191 TYPE c_prev_val_type IS REF CURSOR ;
3192
3193 c_prev_val c_prev_val_type ;
3194
3195 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
3196 l_msg_count NUMBER := 0;
3197 l_msg_data VARCHAR2(2000) := null ;
3198 l_info_request VARCHAR2(25) := null ;
3199 l_document_status VARCHAR2(240) := null ;
3200 l_online_report_id NUMBER := 0 ;
3201 l_return_code VARCHAR2(25) := null ;
3202 l_error_msg VARCHAR2(2000) := null ;
3203 l_return_value NUMBER := -99 ;
3204 l_document_type VARCHAR2(25) := null ;
3205 l_err_code VARCHAR2(100) := null ;
3206
3207 l_full_path VARCHAR2(500) := g_path||'Process_Document';
3208 BEGIN
3209 IF (g_debug_mode = 'Y') THEN
3210 Put_Debug_Msg (l_full_path,p_debug_msg => '**** Process Document ****');
3211 Put_Debug_Msg (l_full_path,p_debug_msg => 'Document Type : '||p_document_type);
3212 Put_Debug_Msg (l_full_path,p_debug_msg => 'Document Subtype : '||p_document_subtype);
3213 Put_Debug_Msg (l_full_path,p_debug_msg => 'Document ID : '||p_document_id);
3214 END IF;
3215
3216 l_distribution_id_tbl.DELETE;
3217 l_gl_enc_date_tbl.DELETE;
3218 l_gl_enc_prd_tbl.DELETE;
3219
3220 -- set cursor select depending upon document type
3221 -- IF p_document_type = 'PO'
3222 IF p_document_type IN ('PO' , 'PA')
3223 THEN
3224 OPEN c_prev_val FOR SELECT distribution_id,
3225 gl_encumbered_date,
3226 gl_encumbered_period_name
3227 FROM igc_cbc_po_process_gt
3228 WHERE encumbered_flag = 'Y'
3229 AND po_header_id = p_document_id ;
3230 ELSIF p_document_type = 'REL'
3231 THEN
3232 OPEN c_prev_val FOR SELECT distribution_id,
3233 gl_encumbered_date,
3234 gl_encumbered_period_name
3235 FROM igc_cbc_po_process_gt
3236 WHERE encumbered_flag = 'Y'
3237 AND po_release_id = p_document_id ;
3238 ELSIF p_document_type = 'REQ'
3239 THEN
3240 -- Requisitions created from backing BPA will not be encumbered
3241 -- when they are first created , hence the prevent_encumbrance_flag
3242 -- will be set to 'Y'. However once they are matched to a PO, subsequent
3243 -- encumberance actions on the PO resets the flag on the requisition thus
3244 -- enucmbering the requisition. For this reason, we should also
3245 -- move the date on the requisition even though there will not be
3246 -- any outstanding encumbrances per se.
3247 -- New for PRC.FP.J - 3173178
3248 OPEN c_prev_val FOR SELECT distribution_id,
3249 gl_encumbered_date,
3250 gl_encumbered_period_name
3251 FROM igc_cbc_po_process_gt
3252 WHERE (encumbered_flag = 'Y'
3253 OR ( prevent_encumbrance_flag = 'Y'
3254 AND blanket_po_header_id IS NOT NULL))
3255 AND req_header_id = p_document_id ;
3256 END IF; -- p_document_type = 'PO'
3257
3258 -- retrieve all distributions' date details for this document
3259 FETCH c_prev_val BULK COLLECT INTO l_distribution_id_tbl,
3260 l_gl_enc_date_tbl,
3261 l_gl_enc_prd_tbl;
3262 CLOSE c_prev_val;
3263
3264 IF l_distribution_id_tbl.FIRST IS NULL THEN
3265 Put_Debug_Msg (l_full_path,p_debug_msg => 'No encumbered distributions to process');
3266 l_err_code := 'IGC_PO_YEP_NO_ENC_DIST';
3267 FND_MESSAGE.set_name('IGC',l_err_code);
3268 l_msg_data := FND_MESSAGE.get;
3269 Insert_Exception_Record(p_exception_reason => l_msg_data,
3270 p_exception_code => l_err_code,
3271 p_document_type => p_document_type,
3272 p_document_id => p_document_id);
3273 x_return_code := -99;
3274 RETURN;
3275 END IF;
3276
3277 IF (g_debug_mode = 'Y') THEN
3278 Put_Debug_Msg (l_full_path,p_debug_msg => 'completed get previous values');
3279 END IF;
3280
3281 -- We need to unreserve funds as of the last date of the previous fiscal year, therefore update
3282 -- the CBC acct date to this date
3283 IGC_CBC_PO_GRP.update_cbc_acct_date(p_document_id => p_document_id,
3284 p_document_type => p_document_type,
3285 p_document_sub_type => p_document_subtype,
3286 p_cbc_acct_date => p_prev_year_end_date,
3287 p_api_version => 1,
3288 p_init_msg_list => FND_API.G_FALSE,
3289 p_commit => FND_API.G_FALSE,
3290 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
3291 x_return_status => l_return_status,
3292 x_msg_count => l_msg_count,
3293 x_msg_data => l_msg_data
3294 );
3295 IF (g_debug_mode = 'Y') THEN
3296 Put_Debug_Msg (l_full_path,p_debug_msg => 'Completed Update CBC Acct Date');
3297 END IF;
3298
3299 -- if update unsuccessful then report errors and terminate processing with errors.
3300 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
3301 THEN
3302 l_msg_data := '';
3303 For j in 1..NVL(l_msg_count,0) LOOP
3304 l_msg_data := FND_MSG_PUB.Get(p_msg_index => j,
3305 p_encoded => 'T');
3306 Insert_Exception_Record(p_exception_reason => l_msg_data,
3307 p_exception_code => l_return_status,
3308 p_document_type => p_document_type,
3309 p_document_id => p_document_id);
3310 x_return_code := 2 ;
3311 FND_MESSAGE.set_name('IGC','IGC_PO_YEP_ACCT_DATE_UPD_ERR');
3312 x_msg_buf := FND_MESSAGE.get ;
3313 RETURN;
3314 END LOOP;
3315 END IF; -- l_return_status <> fnd_api.g_ret_sts_success
3316
3317 -- set document_type for use in PO funds checker
3318 IF p_document_type = 'REQ'
3319 THEN
3320 l_document_type := 'REQUISITION';
3321 ELSIF p_document_type = 'PO'
3322 THEN
3323 l_document_type := 'PO';
3324 ELSIF p_document_type = 'REL'
3325 THEN
3326 l_document_type := 'RELEASE';
3327 ELSIF p_document_type = 'PA'
3328 THEN
3329 l_document_type := 'PA';
3330 ELSE
3331 l_document_type := p_document_type;
3332 END IF; -- p_document_type = 'REQ'
3333
3334 -- we need to commit, as the PO funds checker is called as an autonomous transaction, so if we
3335 -- do not commit the updated date will not be visible to this process
3336 COMMIT;
3337
3338 -- Unreserve Funds
3339 IF (g_debug_mode = 'Y') THEN
3340 Put_Debug_Msg (l_full_path,p_debug_msg => 'Calling Unreserve...');
3341 Put_Debug_Msg (l_full_path,p_debug_msg => 'document type :'||l_document_type);
3342 Put_Debug_Msg (l_full_path,p_debug_msg => 'document subtype :'||p_document_subtype);
3343 Put_Debug_Msg (l_full_path,p_debug_msg => 'document id :'||p_document_id);
3344 Put_Debug_Msg (l_full_path,p_debug_msg => 'action_date :'||p_prev_year_end_date);
3345 END IF;
3346
3347 l_return_value :=
3348 PO_DOCUMENT_ACTIONS_SV.po_request_action(action => 'IGC YEAR END UNRESERVE',
3349 document_type => l_document_type,
3350 document_subtype => p_document_subtype,
3351 document_id => p_document_id,
3352 line_id => NULL,
3353 shipment_id => NULL,
3354 distribution_id => NULL,
3355 employee_id => NULL,
3356 new_document_status => NULL,
3357 offline_code => NULL,
3358 note => NULL,
3359 approval_path_id => NULL,
3360 forward_to_id => NULL,
3361 action_date => p_prev_year_end_date,
3362 override_funds => NULL,
3363 info_request => l_info_request,
3364 document_status => l_document_status,
3365 online_report_id => l_online_report_id,
3366 return_code => l_return_code,
3367 error_msg => l_error_msg
3368 );
3369
3370 IF (g_debug_mode = 'Y') THEN
3371 Put_Debug_Msg (l_full_path,p_debug_msg => 'completed PO Request Action - Unreserve');
3372 Put_Debug_Msg (l_full_path,p_debug_msg => 'return value:'||l_return_value );
3373 Put_Debug_Msg (l_full_path,p_debug_msg => 'info_request:'||l_info_request );
3374 Put_Debug_Msg (l_full_path,p_debug_msg => 'document_status'||l_document_status );
3375 Put_Debug_Msg (l_full_path,p_debug_msg => 'online report id:'||l_online_report_id );
3376 Put_Debug_Msg (l_full_path,p_debug_msg => 'return code:'||l_return_code );
3377 Put_Debug_Msg (l_full_path,p_debug_msg => 'error msg: '||l_error_msg );
3378 END IF;
3379
3380 -- report any errors with the unreserve
3381 IF l_return_value <> 0 OR l_return_code IN ('R','F','T','P') OR length(trim(l_error_msg)) > 0 --l_error_msg IS NOT NULL
3382 THEN
3383 FND_MESSAGE.set_name('IGC','IGC_PO_YEP_DOC_FAIL_FC');
3384 l_msg_data := FND_MESSAGE.get;
3385 l_msg_data := l_msg_data ||' - '||l_error_msg;
3386 Insert_Exception_Record(p_exception_reason => l_msg_data,
3387 p_exception_code => l_return_code,
3388 p_document_type => p_document_type,
3389 p_document_id => p_document_id);
3390
3391 -- if the funds check itself has failed we need to revert the cbc acct date back to it's
3392 -- original value
3393 IF l_return_code NOT IN ('S','A')
3394 THEN
3395 IGC_CBC_PO_GRP.update_cbc_acct_date(p_document_id => p_document_id,
3396 p_document_type => p_document_type,
3397 p_document_sub_type => p_document_subtype,
3398 p_cbc_acct_date => p_prev_cbc_acct_date,
3399 p_api_version => 1,
3400 p_init_msg_list => FND_API.G_FALSE,
3401 p_commit => FND_API.G_FALSE,
3402 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
3403 x_return_status => l_return_status,
3404 x_msg_count => l_msg_count,
3405 x_msg_data => l_msg_data
3406 );
3407
3408 IF (g_debug_mode = 'Y') THEN
3409 Put_Debug_Msg (l_full_path,p_debug_msg => 'completed Update CBC Acct Date - Unreserve failure');
3410 END IF;
3411 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
3412 THEN
3413 -- if update unsuccessful report errors
3414 l_msg_data := '';
3415 For j in 1..NVL(l_msg_count,0) LOOP
3416 l_msg_data := FND_MSG_PUB.Get(p_msg_index => j,
3417 p_encoded => 'T');
3418 Insert_Exception_Record(p_exception_reason => l_msg_data,
3419 p_exception_code => l_return_status,
3420 p_document_type => p_document_type,
3421 p_document_id => p_document_id);
3422 END LOOP;
3423 END IF; -- l_return_status <> fnd_api.g_ret_sts_success
3424
3425 -- commit updates
3426 COMMIT ;
3427
3428 END IF; -- l_return_code NOT in ('S','A')
3429
3430 -- as the unreserve failed, terminate processing with error
3431 x_return_code := 2;
3432 FND_MESSAGE.set_name('IGC','IGC_PO_YEP_DOC_FAIL_FC');
3433 x_msg_buf := FND_MESSAGE.get;
3434 RETURN;
3435 END IF; -- l_return_value <> 0 ...
3436
3437 -- Unreserve successful, so continue
3438
3439 -- as the funds check will have released the locks on the document, we need to relock
3440 -- However, as the commit in the funds checker is to be removed in the near future, we will commit
3441 -- here ourselves, so as not to invalidate the following lock.
3442 COMMIT;
3443 -- Lock the document and children
3444 IF Lock_Documents(p_document_type => p_document_type,
3445 p_document_id => p_document_id) <> FND_API.G_RET_STS_SUCCESS
3446 THEN
3447 -- if unable to relock doc report error and do not process this doc any further
3448 l_err_code := 'IGC_PO_YEP_RELOCK_DOCUMENT';
3449 FND_MESSAGE.set_name('IGC',l_err_code);
3450 l_msg_data := FND_MESSAGE.get;
3451 Insert_Exception_Record(p_exception_reason => l_msg_data,
3452 p_document_type => p_document_type,
3453 p_document_id => p_document_id);
3454 -- do not process this doc any further
3455 x_return_code := -99;
3456 RETURN;
3457 END IF; -- Lock_Documents
3458
3459
3460 -- Update the GL date of all distributions related to the document
3461 IF p_document_type = 'REQ'
3462 THEN
3463 BEGIN
3464 FORALL l_index IN l_distribution_id_tbl.FIRST .. l_distribution_id_tbl.LAST
3465 UPDATE po_req_distributions prd
3466 SET prd.gl_encumbered_date = p_curr_year_start_date,
3467 prd.gl_encumbered_period_name = p_curr_year_start_period
3468 WHERE prd.distribution_id = l_distribution_id_tbl(l_index);
3469 EXCEPTION
3470 WHEN OTHERS THEN
3471 Rollback; -- release locks
3472 IF ( g_unexp_level >= g_debug_level ) THEN
3473 FND_MESSAGE.SET_NAME('IGC','IGC_LOGGING_UNEXP_ERROR');
3474 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
3475 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
3476 FND_LOG.MESSAGE ( g_unexp_level,l_full_path, TRUE);
3477 END IF;
3478 -- Terminate processing with error
3479 IF (FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)) THEN
3480 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, 'Process_Document');
3481 END IF;
3482 x_return_code := 2;
3483 fnd_message.set_name('IGC','IGC_LOGGING_USER_ERROR');
3484 x_msg_buf := fnd_message.get;
3485 RETURN;
3486 END;
3487 IF (g_debug_mode = 'Y') THEN
3488 Put_Debug_Msg (l_full_path,p_debug_msg => 'completed update of po_req_distributions table');
3489 END IF;
3490 ELSE
3491 BEGIN
3492 FORALL l_index IN l_distribution_id_tbl.FIRST .. l_distribution_id_tbl.LAST
3493 UPDATE po_distributions pod
3494 SET pod.gl_encumbered_date = p_curr_year_start_date,
3495 pod.gl_encumbered_period_name = p_curr_year_start_period
3496 WHERE pod.po_distribution_id = l_distribution_id_tbl(l_index);
3497 EXCEPTION
3498 WHEN OTHERS THEN
3499 Rollback; -- release locks
3500 IF ( g_unexp_level >= g_debug_level ) THEN
3501 FND_MESSAGE.SET_NAME('IGC','IGC_LOGGING_UNEXP_ERROR');
3502 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
3503 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
3504 FND_LOG.MESSAGE ( g_unexp_level,l_full_path, TRUE);
3505 END IF;
3506 -- Terminate processing with error
3507 IF (FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)) THEN
3508 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, 'Process_Document');
3509 END IF;
3510 x_return_code := 2;
3511 fnd_message.set_name('IGC','IGC_LOGGING_USER_ERROR');
3512 x_msg_buf := fnd_message.get;
3513 RETURN;
3514 END;
3515 IF (g_debug_mode = 'Y') THEN
3516 Put_Debug_Msg (l_full_path,p_debug_msg => 'completed update of po_distributions table');
3517 END IF;
3518 END IF ; -- p_document_type = 'REQ'
3519
3520
3521 -- Update the cbc acct date of the document to the first date of the current year
3522 IGC_CBC_PO_GRP.update_cbc_acct_date(p_document_id => p_document_id,
3523 p_document_type => p_document_type,
3524 p_document_sub_type => p_document_subtype,
3525 p_cbc_acct_date => p_curr_year_start_date,
3526 p_api_version => 1,
3527 p_init_msg_list => FND_API.G_FALSE,
3528 p_commit => FND_API.G_FALSE,
3529 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
3530 x_return_status => l_return_status,
3531 x_msg_count => l_msg_count,
3532 x_msg_data => l_msg_data
3533 );
3534 IF (g_debug_mode = 'Y') THEN
3535 Put_Debug_Msg (l_full_path,p_debug_msg => 'completed Update CBC Acct Date - current year');
3536 END IF;
3537
3538 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
3539 THEN
3540 -- if update unsuccessful rollback the transaction, report errors and terminate processing with error
3541 Rollback ;
3542 l_msg_data := '';
3543 For j in 1..NVL(l_msg_count,0) LOOP
3544 l_msg_data := FND_MSG_PUB.Get(p_msg_index => j,
3545 p_encoded => 'T');
3546 Insert_Exception_Record(p_exception_reason => l_msg_data,
3547 p_exception_code => l_return_status,
3548 p_document_type => p_document_type,
3549 p_document_id => p_document_id);
3550 END LOOP;
3551 x_return_code := 2;
3552 FND_MESSAGE.set_name('IGC','IGC_PO_YEP_ACCT_DATE_UPD_ERR');
3553 x_msg_buf := FND_MESSAGE.get;
3554 END IF; -- l_return_status <> fnd_api.g_ret_sts_success
3555
3556
3557
3558 -- we need to commit, as the PO funds checker is called as an autonomous transaction, so if we
3559 -- do not commit the updated date will not be visible to this process
3560 COMMIT;
3561
3562 -- Reserve Funds
3563 IF (g_debug_mode = 'Y') THEN
3564 Put_Debug_Msg (l_full_path,p_debug_msg => 'Calling Reserve...');
3565 Put_Debug_Msg (l_full_path,p_debug_msg => 'document type :'||l_document_type);
3566 Put_Debug_Msg (l_full_path,p_debug_msg => 'document subtype :'||p_document_subtype);
3567 Put_Debug_Msg (l_full_path,p_debug_msg => 'document id :'||p_document_id);
3568 END IF;
3569
3570 l_return_value :=
3571 PO_DOCUMENT_ACTIONS_SV.po_request_action(action => 'IGC YEAR END RESERVE',
3572 document_type => l_document_type,
3573 document_subtype => p_document_subtype,
3574 document_id => p_document_id,
3575 line_id => NULL,
3576 shipment_id => NULL,
3577 distribution_id => NULL,
3578 employee_id => NULL,
3579 new_document_status => NULL,
3580 offline_code => NULL,
3581 note => NULL,
3582 approval_path_id => NULL,
3583 forward_to_id => NULL,
3584 action_date => NULL,
3585 override_funds => NULL,
3586 info_request => l_info_request,
3587 document_status => l_document_status,
3588 online_report_id => l_online_report_id,
3589 return_code => l_return_code,
3590 error_msg => l_error_msg
3591 );
3592 IF (g_debug_mode = 'Y') THEN
3593 Put_Debug_Msg (l_full_path,p_debug_msg => 'completed PO Request Action - Reserve');
3594 Put_Debug_Msg (l_full_path,p_debug_msg => 'return value:'||l_return_value );
3595 Put_Debug_Msg (l_full_path,p_debug_msg => 'info_request:'||l_info_request );
3596 Put_Debug_Msg (l_full_path,p_debug_msg => 'document_status'||l_document_status );
3597 Put_Debug_Msg (l_full_path,p_debug_msg => 'online report id:'||l_online_report_id );
3598 Put_Debug_Msg (l_full_path,p_debug_msg => 'return code:'||l_return_code );
3599 Put_Debug_Msg (l_full_path,p_debug_msg => 'error msg: '||l_error_msg );
3600 END IF;
3601
3602 IF l_return_value <> 0 OR l_return_code IN ('R','F','T','P') OR length(trim(l_error_msg)) > 0 --l_error_msg IS NOT NULL
3603 THEN
3604 -- if unsuccessful report errors
3605 FND_MESSAGE.set_name('IGC','IGC_PO_YEP_DOC_FAIL_FC');
3606 l_msg_data := FND_MESSAGE.get;
3607 l_msg_data := l_msg_data ||' - '||l_error_msg;
3608 Insert_Exception_Record(p_exception_reason => l_msg_data,
3609 p_exception_code => l_return_code,
3610 p_document_type => p_document_type,
3611 p_document_id => p_document_id);
3612
3613 IF l_return_code NOT IN ('S','A')
3614 THEN
3615
3616 -- as the funds check will have released the locks on the document, we need to relock
3617 -- Lock the document and children
3618 IF Lock_Documents(p_document_type => p_document_type,
3619 p_document_id => p_document_id) <> FND_API.G_RET_STS_SUCCESS
3620 THEN
3621 -- if unable to relock doc report error and do not process this doc any further
3622 l_err_code := 'IGC_PO_YEP_RELOCK_DOCUMENT';
3623 FND_MESSAGE.set_name('IGC',l_err_code);
3624 l_msg_data := FND_MESSAGE.get;
3625 Insert_Exception_Record(p_exception_reason => l_msg_data,
3626 p_exception_code => l_err_code,
3627 p_document_type => p_document_type,
3628 p_document_id => p_document_id);
3629 -- terminate processing with error
3630 x_return_code := 2;
3631 RETURN;
3632 END IF; -- Lock_Documents
3633
3634 -- if funds check itself is unsuccessful then we need to revert the cbc acct date
3635 -- back to it's original value
3636 IGC_CBC_PO_GRP.update_cbc_acct_date(p_document_id => p_document_id,
3637 p_document_type => p_document_type,
3638 p_document_sub_type => p_document_subtype,
3639 p_cbc_acct_date => p_prev_year_end_date,
3640 p_api_version => 1,
3641 p_init_msg_list => FND_API.G_FALSE,
3642 p_commit => FND_API.G_FALSE,
3643 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
3644 x_return_status => l_return_status,
3645 x_msg_count => l_msg_count,
3646 x_msg_data => l_msg_data
3647 );
3648 IF (g_debug_mode = 'Y') THEN
3649 Put_Debug_Msg (l_full_path,p_debug_msg => 'completed Update CBC Acct Date - Reserve failure');
3650 END IF;
3651
3652 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
3653 THEN
3654 -- if update unsuccessful then report errors
3655 l_msg_data := '';
3656 For j in 1..NVL(l_msg_count,0) LOOP
3657 l_msg_data := FND_MSG_PUB.Get(p_msg_index => j,
3658 p_encoded => 'T');
3659 Insert_Exception_Record(p_exception_reason => l_msg_data,
3660 p_exception_code => l_return_status,
3661 p_document_type => p_document_type,
3662 p_document_id => p_document_id);
3663 END LOOP;
3664 END IF; -- l_return_status <> fnd_api.g_ret_stst_success
3665
3666 -- As reserve funds has failed, reset the GL dates of all distributions related to the document
3667 IF p_document_type = 'REQ'
3668 THEN
3669 BEGIN
3670 FORALL l_index IN l_distribution_id_tbl.FIRST .. l_distribution_id_tbl.LAST
3671 UPDATE po_req_distributions prd
3672 SET prd.gl_encumbered_date = l_gl_enc_date_tbl(l_index),
3673 prd.gl_encumbered_period_name = l_gl_enc_prd_tbl(l_index)
3674 WHERE prd.distribution_id = l_distribution_id_tbl(l_index);
3675 EXCEPTION
3676 WHEN OTHERS THEN
3677 Rollback; -- release locks
3678 IF ( g_unexp_level >= g_debug_level ) THEN
3679 FND_MESSAGE.SET_NAME('IGC','IGC_LOGGING_UNEXP_ERROR');
3680 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
3681 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
3682 FND_LOG.MESSAGE ( g_unexp_level,l_full_path, TRUE);
3683 END IF;
3684 -- Terminate processing with error
3685 IF (FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)) THEN
3686 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, 'Process_Document');
3687 END IF;
3688 x_return_code := 2;
3689 fnd_message.set_name('IGC','IGC_LOGGING_USER_ERROR');
3690 x_msg_buf := fnd_message.get;
3691 RETURN;
3692 END;
3693 IF (g_debug_mode = 'Y') THEN
3694 Put_Debug_Msg (l_full_path,p_debug_msg => 'completed 2nd update of po_req_distributions table');
3695 END IF;
3696 ELSE
3697 BEGIN
3698 FORALL l_index IN l_distribution_id_tbl.FIRST .. l_distribution_id_tbl.LAST
3699 UPDATE po_distributions pod
3700 SET pod.gl_encumbered_date = l_gl_enc_date_tbl(l_index),
3701 pod.gl_encumbered_period_name = l_gl_enc_prd_tbl(l_index)
3702 WHERE pod.po_distribution_id = l_distribution_id_tbl(l_index);
3703 EXCEPTION
3704 WHEN OTHERS THEN
3705 Rollback; -- release locks
3706 IF ( g_unexp_level >= g_debug_level ) THEN
3707 FND_MESSAGE.SET_NAME('IGC','IGC_LOGGING_UNEXP_ERROR');
3708 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
3709 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
3710 FND_LOG.MESSAGE ( g_unexp_level,l_full_path, TRUE);
3711 END IF;
3712 -- Terminate processing with error
3713 IF (FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)) THEN
3714 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, 'Process_Document');
3715 END IF;
3716 x_return_code := 2;
3717 fnd_message.set_name('IGC','IGC_LOGGING_USER_ERROR');
3718 x_msg_buf := fnd_message.get;
3719 RETURN;
3720 END;
3721 IF (g_debug_mode = 'Y') THEN
3722 Put_Debug_Msg (l_full_path,p_debug_msg => 'completed 2nd update of po_distributions table');
3723 END IF;
3724 END IF ; -- p_document_type = 'REQ'
3725
3726 -- commit update transaction
3727 COMMIT;
3728
3729
3730 -- terminate processing with error
3731 x_return_code := 2;
3732 FND_MESSAGE.set_name('IGC','IGC_PO_YEP_DOC_FAIL_FC');
3733 x_msg_buf := FND_MESSAGE.get;
3734 RETURN;
3735
3736 END IF; -- l_return_code NOT IN ('S','A')
3737
3738 -- if successful commit updates and return success
3739 ELSE
3740
3741 -- PO team have made changes to their code so that
3742 -- they dont encumber backing documents in case of the
3743 -- year end process. This means the functionality
3744 -- to create journal adjustments is no longer required
3745 -- Their change is in POXENC1B.pls 115.12
3746 -- Bidisha S, 14-Oct-2003.
3747 -- bug 2804025 ssmales 19-Feb-2003 added call to create_journal_adjustments
3748 -- Create_Journal_Adjustments(p_sobid => p_sobid,
3749 -- p_year => p_year,
3750 -- p_document_type => p_document_type,
3751 -- p_document_subtype => p_document_subtype,
3752 -- p_distribution_id_tbl => l_distribution_id_tbl,
3753 -- p_prev_year_end_period => p_prev_year_end_period,
3754 -- p_prev_year_end_num => p_prev_year_end_num,
3755 -- p_prev_year_end_quarter => p_prev_year_end_quarter,
3756 -- p_curr_year_start_period => p_curr_year_start_period,
3757 -- p_curr_year_start_num => p_curr_year_start_num,
3758 -- p_curr_year_start_quarter => p_curr_year_start_quarter,
3759 -- x_return_code => x_return_code
3760 -- ) ;
3761 -- IF x_return_code = 2
3762 -- THEN
3763 -- -- Commit anyway so that the record in gl_bc_packet gets
3764 -- -- saved and gives us a chance to see why the funds check failed.
3765 -- COMMIT;
3766 -- FND_MESSAGE.set_name('IGC','IGC_PO_YEP_DOC_FAIL_FC');
3767 -- x_msg_buf := FND_MESSAGE.get;
3768 -- Insert_Exception_Record(p_exception_reason => x_msg_buf,
3769 -- p_exception_code => 'IGC_PO_YEP_DOC_FAIL_FC',
3770 -- p_document_type => p_document_type,
3771 -- p_document_id => p_document_id);
3772 -- RETURN;
3773 --
3774 -- END IF;
3775
3776 COMMIT;
3777 x_return_code := -99;
3778 END IF; -- l_return_value <> 0 ...
3779
3780 EXCEPTION
3781 WHEN OTHERS THEN
3782 Rollback;
3783 IF (FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)) THEN
3784 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, 'Process_Document');
3785 END IF;
3786 IF ( g_unexp_level >= g_debug_level ) THEN
3787 FND_MESSAGE.SET_NAME('IGC','IGC_LOGGING_UNEXP_ERROR');
3788 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
3789 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
3790 FND_LOG.MESSAGE ( g_unexp_level,l_full_path, TRUE);
3791 END IF;
3792 x_return_code := 2;
3793 fnd_message.set_name('IGC','IGC_LOGGING_USER_ERROR');
3794 x_msg_buf := fnd_message.get;
3795
3796 END Process_Document;
3797
3798
3799
3800
3801 -- Procedure Validate_and_Process_Doc
3802 -- ==================================
3803 --
3804 -- This procedure carries out validation and processing of a single document for Year End
3805 -- processing. The Document is initially locked, then validated for correct status and any
3806 -- related documents being in error. If applicable, a call is made to validate the document's
3807 -- distributions. Providing all validations have been passed successfully and the processing
3808 -- mode is Final, a call is made to carry out Year End encumbrance processing on the doc.
3809 --
3810 -- IN Parameters
3811 -- -------------
3812 -- p_sob_id Set of Books Id
3813 -- p_org_id Org Id
3814 -- p_year Year being processed
3815 -- p_process_phase User entered processing phase: F - Final, P - Preliminary
3816 -- p_process_frozen User entered choice whether to process Frozen documents: Y or N
3817 -- p_batch_size User entered value used to determine batch size of bulk fetches
3818 -- p_document_type Type of document: PO, REQ or REL
3819 -- p_po_release_id Id of document if a Release
3820 -- p_po_header_id Id of document if a PO
3821 -- p_req_header_id Id of document if a Requisition
3822 -- p_prev_year_end_date End Date of year being closed
3823 -- p_curr_year_start_date Start Date of current year
3824 -- p_curr_year_start_period First period of current year
3825 -- p_conc_request_id Current Concurrent Request Id
3826 --
3827 -- OUT Parameters
3828 -- --------------
3829 -- x_return_code Indicates the return status of the procedure:
3830 -- 0 - Need to terminate processing successfully
3831 -- 1 - Need to terminate processing with warning
3832 -- 2 - Need to terminate processing with error
3833 -- -99 - Successful, continue processing
3834 -- x_msg_buf stores any error message encountered
3835 --
3836 --
3837 -- bug 2804025 ssmales 19-Feb-2003 added new parameters
3838 PROCEDURE Validate_And_Process_Doc(p_sobid IN NUMBER,
3839 p_org_id IN NUMBER,
3840 p_year IN NUMBER,
3841 p_process_phase IN VARCHAR2,
3842 p_process_frozen IN VARCHAR2,
3843 p_batch_size IN NUMBER,
3844 p_document_type IN VARCHAR2,
3845 p_po_release_id IN NUMBER,
3846 p_po_header_id IN NUMBER,
3847 p_req_header_id IN NUMBER,
3848 p_prev_year_end_date IN DATE,
3849 p_prev_year_end_period IN VARCHAR2,
3850 p_prev_year_end_num IN NUMBER,
3851 p_prev_year_end_quarter IN NUMBER,
3852 p_curr_year_start_date IN DATE,
3853 p_curr_year_start_period IN VARCHAR2,
3854 p_curr_year_start_num IN NUMBER,
3855 p_curr_year_start_quarter IN NUMBER,
3856 p_conc_request_id IN NUMBER,
3857 x_return_code OUT NOCOPY NUMBER,
3858 x_msg_buf OUT NOCOPY VARCHAR2
3859 ) IS
3860
3861 CURSOR c_get_releases(p_release_id NUMBER) IS
3862 SELECT authorization_status auth_status,
3863 hold_flag,
3864 release_type document_subtype,
3865 frozen_flag,
3866 cbc_accounting_date
3867 FROM po_releases
3868 WHERE po_release_id = p_release_id ;
3869
3870 CURSOR c_get_po_headers(p_header_id NUMBER) IS
3871 SELECT authorization_status auth_status,
3872 user_hold_flag hold_flag,
3873 type_lookup_code document_subtype,
3874 frozen_flag,
3875 cbc_accounting_date
3876 FROM po_headers
3877 WHERE po_header_id = p_header_id ;
3878
3879 CURSOR c_get_requisitions(p_req_id NUMBER) IS
3880 SELECT authorization_status auth_status,
3881 closed_code,
3882 type_lookup_code document_subtype,
3883 cbc_accounting_date
3884 FROM po_requisition_headers
3885 WHERE requisition_header_id = p_req_id ;
3886
3887 -- ssmales 7-Feb-03 Added conc_request_id clause to two cursors below - bug 2791502
3888 -- Bug 2803967,
3889 -- Removed the clause "AND ipe.exception_code = 'IGC_PO_YEP_REL_INV_STATE'"
3890 CURSOR c_get_release_errors(p_header_id NUMBER) IS
3891 SELECT 'x'
3892 FROM po_releases por,
3893 igc_cbc_po_process_excpts_all ipe
3894 WHERE por.po_header_id = p_header_id
3895 AND ipe.document_type = 'REL'
3896 AND ipe.document_id = por.po_release_id
3897 AND ipe.conc_request_id = p_conc_request_id ;
3898
3899 -- Bug 2803967,
3900 -- Removed the clause "AND ipe.exception_code = 'IGC_PO_YEP_PO_INV_STATE'"
3901 CURSOR c_get_po_errors(p_req_id NUMBER) IS
3902 SELECT 'x'
3903 FROM po_line_locations poll,
3904 po_requisition_lines porl,
3905 igc_cbc_po_process_excpts_all ipe
3906 WHERE porl.requisition_header_id = p_req_id
3907 AND porl.line_location_id = poll.line_location_id
3908 AND ipe.document_id = poll.po_header_id
3909 AND ipe.conc_request_id = p_conc_request_id ;
3910
3911 CURSOR c_get_po_future(p_req_id NUMBER) IS
3912 SELECT 'x'
3913 FROM po_distributions pod,
3914 po_requisition_lines porl,
3915 po_headers poh
3916 WHERE porl.requisition_header_id = p_req_id
3917 AND porl.line_location_id = pod.line_location_id
3918 AND pod.gl_encumbered_date >= p_curr_year_start_date
3919 AND pod.po_header_id = poh.po_header_id
3920 AND (
3921 NVL(poh.authorization_status,'INCOMPLETE') IN
3922 ('INCOMPLETE','REQUIRES REAPPROVAL','REJECTED','IN PROCESS','PRE-APPROVED')
3923 OR poh.user_hold_flag = 'Y'
3924 OR ( poh.frozen_flag = 'Y' AND p_process_frozen = 'N')
3925 );
3926
3927 CURSOR c_get_release_future(p_header_id NUMBER) IS
3928 SELECT 'x'
3929 FROM po_releases por,
3930 po_distributions pod
3931 WHERE por.po_header_id = p_header_id
3932 AND por.po_release_id = pod.po_release_id
3933 AND pod.gl_encumbered_date >= p_curr_year_start_date
3934 AND (
3935 NVL(por.authorization_status,'INCOMPLETE') IN
3936 ('INCOMPLETE','REQUIRES REAPPROVAL','REJECTED','RETURNED','IN PROCESS','PRE-APPROVED')
3937 OR por.hold_flag = 'Y'
3938 OR ( por.frozen_flag = 'Y' AND p_process_frozen = 'N')
3939 );
3940
3941 -- Added for PRC.FP.J, 3173178
3942 CURSOR c_get_bpa_po_errs (p_bpa_header_id NUMBER) IS
3943 SELECT 'X'
3944 FROM igc_cbc_po_process_excpts_all ipe,
3945 po_lines pol
3946 WHERE pol.from_header_id = p_bpa_header_id
3947 AND pol.po_header_id = ipe.document_id
3948 AND ipe.document_type = 'PO'
3949 AND ipe.conc_request_id = p_conc_request_id ;
3950
3951 CURSOR c_get_bpa_req_errs (p_bpa_header_id NUMBER) IS
3952 SELECT 'X'
3953 FROM igc_cbc_po_process_excpts_all ipe,
3954 po_requisition_lines prl
3955 WHERE prl.blanket_po_header_id = p_bpa_header_id
3956 AND prl.requisition_header_id = ipe.document_id
3957 AND ipe.document_type = 'REQ'
3958 AND ipe.conc_request_id = p_conc_request_id ;
3959
3960 l_release c_get_releases%ROWTYPE ;
3961 l_po c_get_po_headers%ROWTYPE ;
3962 l_requisition c_get_requisitions%ROWTYPE ;
3963 l_bpa c_get_po_headers%ROWTYPE ;
3964 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
3965 l_found_error VARCHAR2(1) ;
3966 l_found_future VARCHAR2(1) ;
3967 l_msg_data VARCHAR2(2000) := null ;
3968 l_err_code VARCHAR2(100) := null ;
3969 l_return_code NUMBER := -99 ;
3970 l_msg_buf VARCHAR2(2000) := null ;
3971
3972
3973 l_full_path VARCHAR2(500) := g_path||'Validate_and_Process_Doc';
3974 BEGIN
3975 IF (g_debug_mode = 'Y') THEN
3976 Put_Debug_Msg (l_full_path,p_debug_msg => '**** Validate and Process Document **** ');
3977 END IF;
3978
3979 -- Validate and Process Releases
3980 IF p_document_type = 'REL'
3981 THEN
3982 IF (g_debug_mode = 'Y') THEN
3983 Put_Debug_Msg (l_full_path,p_debug_msg => 'Validating Release '||p_po_release_id);
3984 END IF;
3985
3986 IF p_process_phase = 'F'
3987 THEN
3988 SAVEPOINT savepoint_release ;
3989 -- Attempt to lock the entire document, i.e. header, shipments, distributions
3990 IF Lock_Documents(p_document_type => p_document_type,
3991 p_document_id => p_po_release_id) <> FND_API.G_RET_STS_SUCCESS
3992 THEN
3993 -- if unable to lock doc, then report error and do not process this doc any further
3994 l_err_code := 'IGC_PO_YEP_LOCK_DOCUMENT';
3995 FND_MESSAGE.set_name('IGC',l_err_code);
3996 l_msg_data := FND_MESSAGE.get;
3997 Insert_Exception_Record(p_exception_reason => l_msg_data,
3998 p_document_type => p_document_type,
3999 p_document_id => p_po_release_id);
4000 x_return_code := -99;
4001 RETURN ;
4002 END IF; -- Lock_Documents
4003 END IF ; -- p_process_phase = 'F'
4004
4005 -- get release details
4006 OPEN c_get_releases(p_po_release_id);
4007 FETCH c_get_releases INTO l_release;
4008 CLOSE c_get_releases;
4009
4010 IF (g_debug_mode = 'Y') THEN
4011 Put_Debug_Msg (l_full_path,p_debug_msg => 'completed get release');
4012 END IF;
4013
4014 -- check release status
4015 IF NVL(l_release.auth_status,'INCOMPLETE') IN ('INCOMPLETE','REJECTED','RETURNED','IN PROCESS',
4016 'PRE-APPROVED','REQUIRES REAPPROVAL')
4017 OR l_release.hold_flag = 'Y'
4018 THEN
4019 -- if not valid then report error and do not process this doc any further
4020 l_err_code := 'IGC_PO_YEP_REL_INV_STATE';
4021 FND_MESSAGE.set_name('IGC',l_err_code);
4022 IF l_release.hold_flag = 'Y' THEN
4023 FND_MESSAGE.set_token('REL_STATE','ON HOLD');
4024 ELSE
4025 FND_MESSAGE.set_token('REL_STATE',NVL(l_release.auth_status,'INCOMPLETE'));
4026 END IF;
4027 l_msg_data := FND_MESSAGE.get;
4028 Insert_Exception_Record(p_exception_reason => l_msg_data,
4029 p_exception_code => l_err_code,
4030 p_document_type => p_document_type,
4031 p_document_id => p_po_release_id);
4032 IF p_process_phase = 'F'
4033 THEN
4034 ROLLBACK TO savepoint_release ;
4035 END IF;
4036 x_return_code := -99;
4037 RETURN;
4038 END IF ; -- auth_status
4039
4040 -- check if release has been frozen
4041 IF l_release.frozen_flag = 'Y'
4042 AND p_process_frozen = 'N'
4043 THEN
4044 -- if frozen and user requested not to process frozen docs then report error and
4045 -- do not process this doc any further
4046 l_err_code := 'IGC_PO_YEP_REL_FROZEN';
4047 FND_MESSAGE.set_name('IGC',l_err_code);
4048 l_msg_data := FND_MESSAGE.get;
4049 Insert_Exception_Record(p_exception_reason => l_msg_data,
4050 p_exception_code => l_err_code,
4051 p_document_type => p_document_type,
4052 p_document_id => p_po_release_id);
4053
4054 IF p_process_phase = 'F'
4055 THEN
4056 ROLLBACK TO savepoint_release ;
4057 END IF;
4058 x_return_code := -99;
4059 RETURN;
4060 END IF; -- frozen_flag = 'Y'
4061
4062 -- validate the document's distributions
4063 l_return_status:=
4064 Validate_Distributions(p_batch_size => p_batch_size,
4065 p_document_type => p_document_type,
4066 p_document_subtype => l_release.document_subtype,
4067 p_document_id => p_po_release_id
4068 );
4069
4070 IF (g_debug_mode = 'Y') THEN
4071 Put_Debug_Msg (l_full_path,p_debug_msg => 'completed Validate Distributions');
4072 END IF;
4073
4074 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
4075 THEN
4076 -- if not valid then do not process this document any further
4077 IF p_process_phase = 'F'
4078 THEN
4079 ROLLBACK TO savepoint_release;
4080 END IF;
4081 x_return_code := -99;
4082 RETURN ;
4083 END IF; -- l_return_status <> fnd_api.g_ret_sts_success
4084
4085 -- if in Final Mode, process the document
4086 IF p_process_phase = 'F'
4087 THEN
4088 -- bug 2804025 ssmales 19-Feb-2003 added new parameters in call below
4089 Process_Document(p_sobid => p_sobid,
4090 p_org_id => p_org_id,
4091 p_year => p_year,
4092 p_process_phase => p_process_phase,
4093 p_document_type => p_document_type,
4094 p_document_subtype => l_release.document_subtype,
4095 p_document_id => p_po_release_id,
4096 p_prev_year_end_date => p_prev_year_end_date,
4097 p_prev_year_end_period => p_prev_year_end_period,
4098 p_prev_year_end_num => p_prev_year_end_num,
4099 p_prev_year_end_quarter => p_prev_year_end_quarter,
4100 p_prev_cbc_acct_date => l_release.cbc_accounting_date,
4101 p_curr_year_start_date => p_curr_year_start_date,
4102 p_curr_year_start_period => p_curr_year_start_period,
4103 p_curr_year_start_num => p_curr_year_start_num,
4104 p_curr_year_start_quarter => p_curr_year_start_quarter,
4105 x_return_code => l_return_code,
4106 x_msg_buf => l_msg_buf
4107 );
4108
4109 IF (g_debug_mode = 'Y') THEN
4110 Put_Debug_Msg (l_full_path,p_debug_msg => 'completed Process Document - Release '||p_po_release_id);
4111 END IF;
4112
4113 -- return any errors
4114 IF l_return_code <> -99
4115 THEN
4116 x_return_code := l_return_code;
4117 x_msg_buf := l_msg_buf;
4118 RETURN;
4119 END IF; -- l_return_code <> -99
4120 END IF; -- p_process_phase = 'F'
4121
4122 -- Validate and Process PO's
4123 ELSIF p_document_type = 'PO'
4124 THEN
4125 IF (g_debug_mode = 'Y') THEN
4126 Put_Debug_Msg (l_full_path,p_debug_msg => 'Validating PO '||p_po_header_id);
4127 END IF;
4128
4129 IF p_process_phase = 'F'
4130 THEN
4131 SAVEPOINT savepoint_po ;
4132 --lock document incl child records
4133 IF Lock_Documents(p_document_type => p_document_type,
4134 p_document_id => p_po_header_id) <> FND_API.G_RET_STS_SUCCESS
4135 THEN
4136 -- if unable to lock doc then do not process this doc any further
4137 l_err_code := 'IGC_PO_YEP_LOCK_DOCUMENT';
4138 FND_MESSAGE.set_name('IGC',l_err_code);
4139 l_msg_data := FND_MESSAGE.get;
4140 Insert_Exception_Record(p_exception_reason => l_msg_data,
4141 p_exception_code => l_err_code,
4142 p_document_type => p_document_type,
4143 p_document_id => p_po_header_id);
4144 x_return_code := -99;
4145 RETURN ;
4146 END IF; -- Lock Documents
4147 END IF ; -- process_phase = 'F'
4148
4149 -- get PO document details
4150 OPEN c_get_po_headers(p_po_header_id);
4151 FETCH c_get_po_headers INTO l_po;
4152 CLOSE c_get_po_headers;
4153
4154 IF (g_debug_mode = 'Y') THEN
4155 Put_Debug_Msg (l_full_path,p_debug_msg => 'completed get po');
4156 END IF;
4157
4158 -- check document status
4159 IF NVL(l_po.auth_status,'INCOMPLETE') IN ('INCOMPLETE','REJECTED','IN PROCESS',
4160 'PRE-APPROVED','REQUIRES REAPPROVAL')
4161 OR l_po.hold_flag = 'Y'
4162 THEN
4163 -- if invalid then report error and do not process this doc any further
4164 l_err_code := 'IGC_PO_YEP_PO_INV_STATE';
4165 FND_MESSAGE.set_name('IGC',l_err_code);
4166 IF l_po.hold_flag = 'Y' THEN
4167 FND_MESSAGE.set_token('PO_STATE','ON HOLD');
4168 ELSE
4169 FND_MESSAGE.set_token('PO_STATE',NVL(l_po.auth_status,'INCOMPLETE'));
4170 END IF;
4171 l_msg_data := FND_MESSAGE.get;
4172 Insert_Exception_Record(p_exception_reason => l_msg_data,
4173 p_exception_code => l_err_code,
4174 p_document_type => p_document_type,
4175 p_document_id => p_po_header_id);
4176
4177 IF p_process_phase = 'F'
4178 THEN
4179 ROLLBACK TO savepoint_po ;
4180 END IF;
4181 x_return_code := -99;
4182 RETURN;
4183 END IF ; -- auth_status
4184
4185 -- check if po is frozen
4186 IF l_po.frozen_flag = 'Y'
4187 AND p_process_frozen = 'N'
4188 THEN
4189 -- if frozen and user requested not to process frozen docs then report error and
4190 -- do not process this doc any further
4191 l_err_code := 'IGC_PO_YEP_PO_FROZEN';
4192 FND_MESSAGE.set_name('IGC',l_err_code);
4193 l_msg_data := FND_MESSAGE.get;
4194 Insert_Exception_Record(p_exception_reason => l_msg_data,
4195 p_exception_code => l_err_code,
4196 p_document_type => p_document_type,
4197 p_document_id => p_po_header_id);
4198
4199 IF p_process_phase = 'F'
4200 THEN
4201 ROLLBACK TO savepoint_po ;
4202 END IF;
4203 x_return_code := -99;
4204 RETURN;
4205 END IF; -- frozen_flag = 'Y'
4206
4207 -- processing for Planned PO's
4208 IF l_po.document_subtype = 'PLANNED'
4209 THEN
4210 -- check for any related releases already being flagged as in error
4211 OPEN c_get_release_errors(p_po_header_id);
4212 FETCH c_get_release_errors INTO l_found_error;
4213 IF c_get_release_errors%FOUND
4214 THEN
4215 -- if any releases in error then report error and do not process this doc any further
4216 l_err_code := 'IGC_PO_YEP_REL_NOT_APP';
4217 FND_MESSAGE.set_name('IGC',l_err_code);
4218 l_msg_data := FND_MESSAGE.get;
4219 Insert_Exception_Record(p_exception_reason => l_msg_data,
4220 p_exception_code => l_err_code,
4221 p_document_type => p_document_type,
4222 p_document_id => p_po_header_id);
4223
4224 IF p_process_phase = 'F'
4225 THEN
4226 ROLLBACK TO savepoint_po ;
4227 END IF;
4228 CLOSE c_get_release_errors;
4229 x_return_code := -99;
4230 RETURN;
4231 END IF; -- c_get_release_errors%FOUND
4232 CLOSE c_get_release_errors;
4233 IF (g_debug_mode = 'Y') THEN
4234 Put_Debug_Msg (l_full_path,p_debug_msg => 'completed get release errors');
4235 END IF;
4236
4237 -- check for any related releases with gl encumbered dates in future years
4238 OPEN c_get_release_future(p_po_header_id);
4239 FETCH c_get_release_future INTO l_found_future;
4240 IF c_get_release_future%FOUND
4241 THEN
4242 -- if any releases in future then report error and do not process this doc any further
4243 l_err_code := 'IGC_PO_YEP_PO_REL_FUTURE';
4244 FND_MESSAGE.set_name('IGC',l_err_code);
4245 l_msg_data := FND_MESSAGE.get;
4246 Insert_Exception_Record(p_exception_reason => l_msg_data,
4247 p_exception_code => l_err_code,
4248 p_document_type => p_document_type,
4249 p_document_id => p_po_header_id);
4250
4251 IF p_process_phase = 'F'
4252 THEN
4253 ROLLBACK TO savepoint_po ;
4254 END IF;
4255 CLOSE c_get_release_future;
4256 x_return_code := -99;
4257 RETURN;
4258 END IF; -- c_get_release_future%FOUND
4259 CLOSE c_get_release_future;
4260 IF (g_debug_mode = 'Y') THEN
4261 Put_Debug_Msg (l_full_path,p_debug_msg => 'completed get release future');
4262 END IF;
4263 END IF; -- document_subtype = 'PLANNED'
4264
4265 -- Validate PO's distributions
4266 l_return_status :=
4267 Validate_Distributions(p_batch_size => p_batch_size,
4268 p_document_type => p_document_type,
4269 p_document_subtype => l_po.document_subtype,
4270 p_document_id => p_po_header_id
4271 );
4272 IF (g_debug_mode = 'Y') THEN
4273 Put_Debug_Msg (l_full_path,p_debug_msg => 'completed Validate Distributions');
4274 END IF;
4275 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
4276 THEN
4277 -- If distributions invalid report error and do not process this doc any further
4278 IF p_process_phase = 'F'
4279 THEN
4280 ROLLBACK TO savepoint_po ;
4281 END IF;
4282 x_return_code := -99;
4283 RETURN ;
4284 END IF; -- l_return_status <> fnd_api.g_ret_sts_success
4285
4286 -- IF in Final Mode process the document
4287 IF p_process_phase = 'F'
4288 THEN
4289 -- bug 2804025 ssmales 19-Feb-2003 added new parameters in call below
4290 Process_Document(p_sobid => p_sobid,
4291 p_org_id => p_org_id,
4292 p_year => p_year,
4293 p_process_phase => p_process_phase,
4294 p_document_type => p_document_type,
4295 p_document_subtype => l_po.document_subtype,
4296 p_document_id => p_po_header_id,
4297 p_prev_year_end_date => p_prev_year_end_date,
4298 p_prev_year_end_period => p_prev_year_end_period,
4299 p_prev_year_end_num => p_prev_year_end_num,
4300 p_prev_year_end_quarter => p_prev_year_end_quarter,
4301 p_prev_cbc_acct_date => l_po.cbc_accounting_date,
4302 p_curr_year_start_date => p_curr_year_start_date,
4303 p_curr_year_start_period => p_curr_year_start_period,
4304 p_curr_year_start_num => p_curr_year_start_num,
4305 p_curr_year_start_quarter => p_curr_year_start_quarter,
4306 x_return_code => l_return_code,
4307 x_msg_buf => l_msg_buf
4308 );
4309
4310 IF (g_debug_mode = 'Y') THEN
4311 Put_Debug_Msg (l_full_path,p_debug_msg => 'completed Process Document');
4312 END IF;
4313
4314 -- return any errors
4315 IF l_return_code <> -99
4316 THEN
4317 x_return_code := l_return_code;
4318 x_msg_buf := l_msg_buf;
4319 RETURN;
4320 END IF;
4321
4322 END IF; -- p_process_phase = 'F'
4323
4324 -- Validate and Process Requisitions
4325 ELSIF p_document_type = 'REQ'
4326 THEN
4327 IF (g_debug_mode = 'Y') THEN
4328 Put_Debug_Msg (l_full_path,p_debug_msg => 'Validating Requisition '||p_req_header_id);
4329 END IF;
4330
4331 IF p_process_phase = 'F'
4332 THEN
4333 SAVEPOINT savepoint_requisition ;
4334 --lock document incl child records
4335 IF Lock_Documents(p_document_type => p_document_type,
4336 p_document_id => p_req_header_id) <> FND_API.G_RET_STS_SUCCESS
4337 THEN
4338 -- if unable to lock doc then report error and do not process this doc any further
4339 l_err_code := 'IGC_PO_YEP_LOCK_DOCUMENT';
4340 FND_MESSAGE.set_name('IGC',l_err_code);
4341 l_msg_data := FND_MESSAGE.get;
4342 Insert_Exception_Record(p_exception_reason => l_msg_data,
4343 p_exception_code => l_err_code,
4344 p_document_type => p_document_type,
4345 p_document_id => p_req_header_id);
4346 -- do not process this doc any further
4347 x_return_code := -99;
4348 RETURN ;
4349 END IF; -- Lock_Documents
4350 END IF ; -- p_process_phase = 'F'
4351
4352 -- Get requisition details
4353 OPEN c_get_requisitions(p_req_header_id);
4354 FETCH c_get_requisitions INTO l_requisition;
4355 CLOSE c_get_requisitions;
4356
4357 IF (g_debug_mode = 'Y') THEN
4358 Put_Debug_Msg (l_full_path,p_debug_msg => 'completed get requisitions');
4359 END IF;
4360
4361 -- Check requisition status
4362 IF NVL(l_requisition.auth_status,'INCOMPLETE') IN ('INCOMPLETE','REJECTED','RETURNED','IN PROCESS',
4363 'PRE-APPROVED','REQUIRES REAPPROVAL')
4364 OR l_requisition.closed_code = 'ON HOLD'
4365 THEN
4366 -- if invalid status then report error and do not process this doc any further
4367 l_err_code := 'IGC_PO_YEP_REQ_INV_STATE';
4368 FND_MESSAGE.set_name('IGC',l_err_code);
4369 IF l_requisition.closed_code = 'ON HOLD' THEN
4370 FND_MESSAGE.set_token('REQ_STATE','ON HOLD');
4371 ELSE
4372 FND_MESSAGE.set_token('REQ_STATE',NVL(l_requisition.auth_status,'INCOMPLETE'));
4373 END IF;
4374 l_msg_data := FND_MESSAGE.get;
4375 Insert_Exception_Record(p_exception_reason => l_msg_data,
4376 p_exception_code => l_err_code,
4377 p_document_type => p_document_type,
4378 p_document_id => p_req_header_id);
4379
4380 IF p_process_phase = 'F'
4381 THEN
4382 ROLLBACK TO savepoint_requisition ;
4383 END IF;
4384 x_return_code := -99;
4385 RETURN;
4386 END IF ; -- auth_status
4387
4388 -- Check if related PO's have already been flagged as being in error
4389 OPEN c_get_po_errors(p_req_header_id);
4390 FETCH c_get_po_errors INTO l_found_error;
4391 IF c_get_po_errors%FOUND
4392 THEN
4393 -- If any PO's in error then report and do not process this doc any further
4394 l_err_code := 'IGC_PO_YEP_REQ_PO_NAPPR';
4395 FND_MESSAGE.set_name('IGC',l_err_code);
4396 l_msg_data := FND_MESSAGE.get;
4397 Insert_Exception_Record(p_exception_reason => l_msg_data,
4398 p_exception_code => l_err_code,
4399 p_document_type => p_document_type,
4400 p_document_id => p_req_header_id);
4401
4402 IF p_process_phase = 'F'
4403 THEN
4404 ROLLBACK TO savepoint_requisition ;
4405 END IF;
4406 CLOSE c_get_po_errors;
4407 x_return_code := -99;
4408 RETURN;
4409 END IF; -- c_get_po_errors%FOUND
4410 CLOSE c_get_po_errors;
4411 IF (g_debug_mode = 'Y') THEN
4412 Put_Debug_Msg (l_full_path,p_debug_msg => 'completed get po errors ');
4413 END IF;
4414
4415 -- Check if there are any related PO's with GL dates after the year being rolled forward
4416 OPEN c_get_po_future(p_req_header_id);
4417 FETCH c_get_po_future INTO l_found_future;
4418 IF c_get_po_future%FOUND
4419 THEN
4420 -- If any related PO's with GL dates in future years, then do not process this doc any further
4421 l_err_code := 'IGC_PO_YEP_REQ_PO_FUTURE';
4422 FND_MESSAGE.set_name('IGC',l_err_code);
4423 l_msg_data := FND_MESSAGE.get;
4424 Insert_Exception_Record(p_exception_reason => l_msg_data,
4425 p_exception_code => l_err_code,
4426 p_document_type => p_document_type,
4427 p_document_id => p_req_header_id);
4428
4429 IF p_process_phase = 'F'
4430 THEN
4431 ROLLBACK TO savepoint_requisition ;
4432 END IF;
4433 CLOSE c_get_po_future;
4434 x_return_code := -99;
4435 RETURN;
4436 END IF; -- c_get_po_future%FOUND
4437 CLOSE c_get_po_future;
4438 IF (g_debug_mode = 'Y') THEN
4439 Put_Debug_Msg (l_full_path,p_debug_msg => 'completed get po future ');
4440 END IF;
4441
4442 -- Validate Requisition's distributions
4443 l_return_status :=
4444 Validate_Distributions(p_batch_size => p_batch_size,
4445 p_document_type => p_document_type,
4446 p_document_subtype => l_requisition.document_subtype,
4447 p_document_id => p_req_header_id
4448 );
4449 IF (g_debug_mode = 'Y') THEN
4450 Put_Debug_Msg (l_full_path,p_debug_msg => 'completed validate distributions');
4451 END IF;
4452
4453 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
4454 THEN
4455 -- If distributions invalid then report error and do not process this doc any further
4456 IF p_process_phase = 'F'
4457 THEN
4458 ROLLBACK TO savepoint_requisition ;
4459 END IF;
4460 x_return_code := -99;
4461 RETURN ;
4462 END IF; -- l_return_status <> fnd_api.g_ret_sts_success
4463
4464
4465 -- if in Final Mode then process the requisition
4466 IF p_process_phase = 'F'
4467 THEN
4468 -- bug 2804025 ssmales 19-Feb-2003 added new parameters in call below
4469 Process_Document(p_sobid => p_sobid,
4470 p_org_id => p_org_id,
4471 p_year => p_year,
4472 p_process_phase => p_process_phase,
4473 p_document_type => p_document_type,
4474 p_document_subtype => l_requisition.document_subtype,
4475 p_document_id => p_req_header_id,
4476 p_prev_year_end_date => p_prev_year_end_date,
4477 p_prev_year_end_period => p_prev_year_end_period,
4478 p_prev_year_end_num => p_prev_year_end_num,
4479 p_prev_year_end_quarter => p_prev_year_end_quarter,
4480 p_prev_cbc_acct_date => l_requisition.cbc_accounting_date,
4481 p_curr_year_start_date => p_curr_year_start_date,
4482 p_curr_year_start_period => p_curr_year_start_period,
4483 p_curr_year_start_num => p_curr_year_start_num,
4484 p_curr_year_start_quarter => p_curr_year_start_quarter,
4485 x_return_code => l_return_code,
4486 x_msg_buf => l_msg_buf
4487 );
4488
4489 IF (g_debug_mode = 'Y') THEN
4490 Put_Debug_Msg (l_full_path,p_debug_msg => 'completed Process Document');
4491 END IF;
4492
4493 -- return any errors
4494 IF l_return_code <> -99
4495 THEN
4496 x_return_code := l_return_code;
4497 x_msg_buf := l_msg_buf;
4498 RETURN;
4499 END IF;
4500
4501 END IF; -- p_process_phase = 'F'
4502
4503 END IF; -- p_document_type = 'REQ'
4504
4505 -- Validate and Process BPAs
4506 -- Added for 3173178, PRC.FP.J
4507 IF p_document_type = 'PA'
4508 THEN
4509 IF (g_debug_mode = 'Y') THEN
4510 Put_Debug_Msg (l_full_path,p_debug_msg => 'Validating Blanket Agreements '||p_po_header_id);
4511 END IF;
4512
4513 IF p_process_phase = 'F'
4514 THEN
4515 SAVEPOINT savepoint_BPA ;
4516 --lock document incl child records
4517 IF Lock_Documents(p_document_type => p_document_type,
4518 p_document_id => p_po_header_id) <> FND_API.G_RET_STS_SUCCESS
4519 THEN
4520 -- if unable to lock doc then report error and do not process this doc any further
4521 l_err_code := 'IGC_PO_YEP_LOCK_DOCUMENT';
4522 FND_MESSAGE.set_name('IGC',l_err_code);
4523 l_msg_data := FND_MESSAGE.get;
4524 Insert_Exception_Record(p_exception_reason => l_msg_data,
4525 p_exception_code => l_err_code,
4526 p_document_type => p_document_type,
4527 p_document_id => p_po_header_id);
4528 -- do not process this doc any further
4529 x_return_code := -99;
4530 RETURN ;
4531 END IF; -- Lock_Documents
4532 END IF ; -- p_process_phase = 'F'
4533
4534 -- Get BPA details
4535 OPEN c_get_po_headers(p_po_header_id);
4536 FETCH c_get_po_headers INTO l_bpa;
4537 CLOSE c_get_po_headers;
4538
4539 IF (g_debug_mode = 'Y') THEN
4540 Put_Debug_Msg (l_full_path,p_debug_msg => 'completed get blanket agreements');
4541 END IF;
4542
4543 -- Check blanket agreements status
4544 IF NVL(l_bpa.auth_status,'INCOMPLETE') IN ('INCOMPLETE','REJECTED','IN PROCESS',
4545 'PRE-APPROVED','REQUIRES REAPPROVAL')
4546 OR l_bpa.hold_flag = 'Y'
4547 THEN
4548 -- if invalid status then report error and do not process this doc any further
4549 l_err_code := 'IGC_PO_YEP_BPA_INV_STATE';
4550 FND_MESSAGE.set_name('IGC',l_err_code);
4551 IF l_bpa.hold_flag = 'Y'
4552 THEN
4553 FND_MESSAGE.set_token('BPA_STATE','ON HOLD');
4554 ELSE
4555 FND_MESSAGE.set_token('BPA_STATE',NVL(l_bpa.auth_status,'INCOMPLETE'));
4556 END IF;
4557 l_msg_data := FND_MESSAGE.get;
4558 Insert_Exception_Record(p_exception_reason => l_msg_data,
4559 p_exception_code => l_err_code,
4560 p_document_type => p_document_type,
4561 p_document_id => p_po_header_id);
4562
4563 IF p_process_phase = 'F'
4564 THEN
4565 ROLLBACK TO savepoint_bpa ;
4566 END IF;
4567 x_return_code := -99;
4568 RETURN;
4569 END IF ; -- auth_status
4570
4571 -- check if BPA is frozen
4572 IF l_bpa.frozen_flag = 'Y'
4573 AND p_process_frozen = 'N'
4574 THEN
4575 -- if frozen and user requested not to process frozen docs then report error and
4576 -- do not process this doc any further
4577 l_err_code := 'IGC_PO_YEP_PO_FROZEN';
4578 FND_MESSAGE.set_name('IGC',l_err_code);
4579 l_msg_data := FND_MESSAGE.get;
4580 Insert_Exception_Record(p_exception_reason => l_msg_data,
4581 p_exception_code => l_err_code,
4582 p_document_type => p_document_type,
4583 p_document_id => p_po_header_id);
4584
4585 IF p_process_phase = 'F'
4586 THEN
4587 ROLLBACK TO savepoint_bpa ;
4588 END IF;
4589 x_return_code := -99;
4590 RETURN;
4591 END IF; -- frozen_flag = 'Y'
4592
4593 -- Check if related PO's have already been flagged as being in error
4594 OPEN c_get_bpa_po_errs(p_po_header_id);
4595 FETCH c_get_bpa_po_errs INTO l_found_error;
4596 IF c_get_bpa_po_errs%FOUND
4597 THEN
4598 -- If any PO's in error then report
4599 -- and do not process this doc any further
4600 l_err_code := 'IGC_PO_YEP_BPA_PO_NAPPR';
4601 FND_MESSAGE.set_name('IGC',l_err_code);
4602 l_msg_data := FND_MESSAGE.get;
4603 Insert_Exception_Record(p_exception_reason => l_msg_data,
4604 p_exception_code => l_err_code,
4605 p_document_type => p_document_type,
4606 p_document_id => p_po_header_id);
4607
4608 IF p_process_phase = 'F'
4609 THEN
4610 ROLLBACK TO savepoint_bpa ;
4611 END IF;
4612 CLOSE c_get_bpa_po_errs;
4613 x_return_code := -99;
4614 RETURN;
4615 END IF; -- c_get_bpa_po_errs%FOUND
4616 CLOSE c_get_bpa_po_errs;
4617 IF (g_debug_mode = 'Y') THEN
4618 Put_Debug_Msg (l_full_path,p_debug_msg => 'completed get BPA PO errors ');
4619 END IF;
4620
4621 -- check for any related Blanket releases already being flagged as in error
4622 OPEN c_get_release_errors(p_po_header_id);
4623 FETCH c_get_release_errors INTO l_found_error;
4624 IF c_get_release_errors%FOUND
4625 THEN
4626 -- if any releases in error then report error and do not process this doc any further
4627 l_err_code := 'IGC_PO_YEP_REL_NOT_APP';
4628 FND_MESSAGE.set_name('IGC',l_err_code);
4629 l_msg_data := FND_MESSAGE.get;
4630 Insert_Exception_Record(p_exception_reason => l_msg_data,
4631 p_exception_code => l_err_code,
4632 p_document_type => p_document_type,
4633 p_document_id => p_po_header_id);
4634
4635 IF p_process_phase = 'F'
4636 THEN
4637 ROLLBACK TO savepoint_bpa ;
4638 END IF;
4639 CLOSE c_get_release_errors;
4640 x_return_code := -99;
4641 RETURN;
4642 END IF; -- c_get_release_errors%FOUND
4643
4644 CLOSE c_get_release_errors;
4645
4646 IF (g_debug_mode = 'Y') THEN
4647 Put_Debug_Msg (l_full_path,p_debug_msg => 'completed get release errors for BPA');
4648 END IF;
4649
4650 -- Check if any requisitions sourced from the BPA have already been
4651 -- flagged as being in error
4652 OPEN c_get_bpa_req_errs(p_po_header_id);
4653 FETCH c_get_bpa_req_errs INTO l_found_error;
4654 IF c_get_bpa_req_errs%FOUND
4655 THEN
4656 -- If any REQs in error then report
4657 -- and do not process this doc any further
4658 l_err_code := 'IGC_PO_YEP_BPA_REQ_NAPPR';
4659 FND_MESSAGE.set_name('IGC',l_err_code);
4660 l_msg_data := FND_MESSAGE.get;
4661 Insert_Exception_Record(p_exception_reason => l_msg_data,
4662 p_exception_code => l_err_code,
4663 p_document_type => p_document_type,
4664 p_document_id => p_po_header_id);
4665
4666 IF p_process_phase = 'F'
4667 THEN
4668 ROLLBACK TO savepoint_bpa ;
4669 END IF;
4670 CLOSE c_get_bpa_req_errs;
4671 x_return_code := -99;
4672 RETURN;
4673 END IF; -- c_get_req_errors%FOUND
4674 CLOSE c_get_bpa_req_errs;
4675 IF (g_debug_mode = 'Y') THEN
4676 Put_Debug_Msg (l_full_path,p_debug_msg => 'completed get BPA REQ errors ');
4677 END IF;
4678
4679 IF (g_debug_mode = 'Y') THEN
4680 Put_Debug_Msg (l_full_path,p_debug_msg => 'BPA distributions do not need validations');
4681 END IF;
4682
4683 -- if in Final Mode then process the BPA
4684 IF p_process_phase = 'F'
4685 THEN
4686 Process_Document(p_sobid => p_sobid,
4687 p_org_id => p_org_id,
4688 p_year => p_year,
4689 p_process_phase => p_process_phase,
4690 p_document_type => p_document_type,
4691 p_document_subtype => l_bpa.document_subtype,
4692 p_document_id => p_po_header_id,
4693 p_prev_year_end_date => p_prev_year_end_date,
4694 p_prev_year_end_period => p_prev_year_end_period,
4695 p_prev_year_end_num => p_prev_year_end_num,
4696 p_prev_year_end_quarter => p_prev_year_end_quarter,
4697 p_prev_cbc_acct_date => l_bpa.cbc_accounting_date,
4698 p_curr_year_start_date => p_curr_year_start_date,
4699 p_curr_year_start_period => p_curr_year_start_period,
4700 p_curr_year_start_num => p_curr_year_start_num,
4701 p_curr_year_start_quarter => p_curr_year_start_quarter,
4702 x_return_code => l_return_code,
4703 x_msg_buf => l_msg_buf
4704 );
4705
4706 IF (g_debug_mode = 'Y') THEN
4707 Put_Debug_Msg (l_full_path,p_debug_msg => 'completed Process Document for BPA');
4708 END IF;
4709
4710 -- return any errors
4711 IF l_return_code <> -99
4712 THEN
4713 x_return_code := l_return_code;
4714 x_msg_buf := l_msg_buf;
4715 RETURN;
4716 END IF;
4717
4718 END IF; -- p_process_phase = 'F'
4719
4720 END IF; -- p_document_type = 'BPA'
4721 x_return_code := -99;
4722
4723 EXCEPTION
4724 WHEN OTHERS THEN
4725 rollback;
4726 IF (FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)) THEN
4727 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, 'Validate_And_Process_Doc');
4728 END IF;
4729 IF ( g_unexp_level >= g_debug_level ) THEN
4730 FND_MESSAGE.SET_NAME('IGC','IGC_LOGGING_UNEXP_ERROR');
4731 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
4732 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
4733 FND_LOG.MESSAGE ( g_unexp_level,l_full_path, TRUE);
4734 END IF;
4735 x_return_code := 2 ;
4736 fnd_message.set_name('IGC','IGC_LOGGIN_USER_ERROR');
4737 x_msg_buf := fnd_message.get;
4738
4739 END Validate_And_Process_Doc;
4740
4741
4742
4743
4744 -- Procedure Year_End_Main
4745 -- =======================
4746 --
4747 -- This is the main procedure of the PO/CBC Year End Process.
4748 -- This process, to be run at Year End, carries forward encumbrances to the next fiscal year.
4749 -- Encumbrances are carried forward in both the Standard and Commitment Budgets at a transactional
4750 -- level for Requisitions, whilst encumbrances for PO's are carried forward only in the Standard
4751 -- Budget. Funds reservation in the Standard Budget is carried out in Forced Mode.
4752 --
4753 -- IN Parameters
4754 -- -------------
4755 -- p_sob_id Set of Books Id
4756 -- p_org_id Org Id
4757 -- p_process_phase User entered processing phase: F - Final, P - Preliminary
4758 -- p_year User entered Year being closed
4759 -- p_process_frozen User entered choice whether to process Frozen documents: Y or N
4760 -- p_trunc_exception User entered choice to truncate the exception table: Y or N
4761 -- p_batch_size User entered value used to determine batch size of bulk fetches
4762 --
4763 -- OUT Parameters
4764 -- --------------
4765 -- errbuf Standard Concurrent Processing Error Buffer
4766 -- retcode Standard Concurrent Processing Return Code
4767 --
4768 --
4769 PROCEDURE Year_End_Main(errbuf OUT NOCOPY VARCHAR2,
4770 retcode OUT NOCOPY VARCHAR2,
4771 /* Bug No : 6341012. MOAC uptake. SOB_ID, ORG_ID are no more retrieved from profile values in R12 */
4772 -- p_sobid IN NUMBER,
4773 -- p_org_id IN NUMBER,
4774 p_process_phase IN VARCHAR2,
4775 p_year IN NUMBER,
4776 p_process_frozen IN VARCHAR2,
4777 p_trunc_exception IN VARCHAR2,
4778 p_batch_size IN NUMBER
4779 ) IS
4780
4781 -- Bug No : 6341012. MOAC uptake. Local variables for SOB_ID,SOB_NAME,ORG_ID
4782 l_sob_id NUMBER;
4783 l_sob_name VARCHAR2(30);
4784 l_org_id NUMBER;
4785
4786
4787 CURSOR c_get_recs(c_p_doc_type VARCHAR2) IS
4788 SELECT DISTINCT tmp.po_release_id,
4789 tmp.po_header_id,
4790 tmp.req_header_id
4791 FROM igc_cbc_po_process_gt tmp
4792 WHERE document_type = c_p_doc_type ;
4793
4794 l_document_type VARCHAR2(3);
4795 l_po_enc_on BOOLEAN := FALSE;
4796 l_req_enc_on BOOLEAN := FALSE;
4797 l_prev_year_start_date DATE;
4798 l_prev_year_end_date DATE;
4799 l_curr_year_start_date DATE;
4800 l_curr_year_start_period gl_periods.period_name%TYPE ;
4801 l_conc_request_id NUMBER := FND_GLOBAL.conc_request_id ;
4802
4803
4804 -- bug 2804025 ssmales 19-Feb-2003 added following local variables
4805 l_prev_year_end_period gl_periods.period_name%TYPE ;
4806 l_prev_year_end_num gl_periods.period_num%TYPE ;
4807 l_prev_year_end_quarter gl_periods.quarter_num%TYPE ;
4808 l_curr_year_start_num gl_periods.period_num%TYPE ;
4809 l_curr_year_start_quarter gl_periods.quarter_num%TYPE ;
4810
4811
4812
4813 TYPE document_rec_type IS RECORD
4814 (po_release_id igc_tbl_number,
4815 po_header_id igc_tbl_number,
4816 req_header_id igc_tbl_number);
4817
4818 l_document_id_rec document_rec_type;
4819 l_return_code NUMBER := -99 ;
4820 l_msg_buf VARCHAR2(2000) := null ;
4821
4822
4823 l_full_path VARCHAR2(500) := g_path||'Year_End_Main';
4824 BEGIN
4825 IF (g_debug_mode = 'Y') THEN
4826 Put_Debug_Msg (l_full_path,p_debug_msg => '**** Year End Main ****');
4827 END IF;
4828
4829 /* Bug No : 6341012. MOAC Uptake. SOB_ID,ORG_ID values are retrieved
4830 and in following code p_sobid is changed to l_sob_id and p_org_id is changed l_org_id */
4831 l_org_id := MO_GLOBAL.get_current_org_id;
4832 MO_UTILS.get_ledger_info(l_org_id,l_sob_id,l_sob_name);
4833
4834 -- Validate Budgetary Control Parameters
4835 Validate_BC_Params(p_sobid => l_sob_id,
4836 p_org_id => l_org_id,
4837 p_process_phase => p_process_phase,
4838 p_year => p_year,
4839 p_trunc_exception => p_trunc_exception,
4840 x_po_enc_on => l_po_enc_on,
4841 x_req_enc_on => l_req_enc_on,
4842 x_return_code => l_return_code,
4843 x_msg_buf => l_msg_buf
4844 );
4845 IF (g_debug_mode = 'Y') THEN
4846 Put_Debug_Msg (l_full_path,p_debug_msg => '**** Year End Main - completed Validate BC Params ****');
4847 END IF;
4848
4849 -- If not successful return, then terminate processing
4850 IF l_return_code <> -99
4851 THEN
4852 Log_Error(p_sobid => l_sob_id,
4853 p_org_id => l_org_id,
4854 p_conc_request_id => l_conc_request_id,
4855 p_process_phase => p_process_phase) ;
4856
4857 Execute_Exceptions_Report(p_sobid => l_sob_id,
4858 p_org_id => l_org_id,
4859 p_conc_request_id => l_conc_request_id,
4860 p_process_phase => p_process_phase,
4861 p_year => p_year
4862 );
4863
4864 retcode := l_return_code;
4865 errbuf := l_msg_buf;
4866 return;
4867 END IF; -- l_return_code <> -99
4868
4869 -- Validate Period Statuses
4870
4871 -- bug 2804025 added params for prev_year_end_period/num/quarter and curr_year_start_num/quarter
4872 IF NOT
4873 Validate_Period_Status(p_sobid => l_sob_id,
4874 p_org_id => l_org_id,
4875 p_process_phase => p_process_phase,
4876 p_year => p_year,
4877 x_prev_year_start_date => l_prev_year_start_date,
4878 x_prev_year_end_date => l_prev_year_end_date,
4879 x_prev_year_end_period => l_prev_year_end_period,
4880 x_prev_year_end_num => l_prev_year_end_num,
4881 x_prev_year_end_quarter => l_prev_year_end_quarter,
4882 x_curr_year_start_date => l_curr_year_start_date,
4883 x_curr_year_start_period => l_curr_year_start_period,
4884 x_curr_year_start_num => l_curr_year_start_num,
4885 x_curr_year_start_quarter => l_curr_year_start_quarter
4886 )
4887 THEN
4888 -- Validation failed so terminate processing
4889 Log_Error(p_sobid => l_sob_id,
4890 p_org_id => l_org_id,
4891 p_conc_request_id => l_conc_request_id,
4892 p_process_phase => p_process_phase) ;
4893
4894 Execute_Exceptions_Report(p_sobid => l_sob_id,
4895 p_org_id => l_org_id,
4896 p_conc_request_id => l_conc_request_id,
4897 p_process_phase => p_process_phase,
4898 p_year => p_year
4899 );
4900 retcode := 0;
4901 errbuf := null;
4902 return;
4903 END IF; -- NOT Validate_Period_Status
4904
4905 IF (g_debug_mode = 'Y') THEN
4906 Put_Debug_Msg (l_full_path,p_debug_msg => '**** Year End Main - Completed Validate Period Status ****');
4907 END IF;
4908
4909 -- IF purchasing encumbrance is enabled then get details of PO's and Release to be processed
4910 -- fetching these into a temporary table
4911 IF l_po_enc_on
4912 THEN
4913 Fetch_PO_And_Releases(p_org_id => l_org_id,
4914 p_prev_year_start_date => l_prev_year_start_date,
4915 p_prev_year_end_date => l_prev_year_end_date
4916 );
4917
4918 IF (g_debug_mode = 'Y') THEN
4919 Put_Debug_Msg (l_full_path,p_debug_msg => '**** Year End Main - completed Fetch PO and Releases ****');
4920 END IF;
4921 END IF; -- l_po_enc_on
4922
4923 -- process one document type at a time
4924 -- Modified the for loop to include BPAs as the 4th document type to be
4925 -- processed. - 3173178
4926 FOR l_doctype_index IN 1 .. 4
4927 LOOP
4928 IF l_doctype_index = 1 AND l_po_enc_on
4929 THEN
4930 l_document_type := 'REL' ;
4931 ELSIF l_doctype_index = 2 AND l_po_enc_on
4932 THEN
4933 l_document_type := 'PO' ;
4934 ELSIF l_doctype_index = 3 AND l_req_enc_on
4935 THEN
4936 l_document_type := 'REQ' ;
4937 -- IF requisition encumbrance is enabled then get details of Requisitions to be processed
4938 -- fetching these into the temporary table.
4939 -- The reason req's are fetched at this stage is to reduce the number of rows in the temporary table
4940 Fetch_Requisitions(p_org_id => l_org_id,
4941 p_prev_year_start_date => l_prev_year_start_date,
4942 p_prev_year_end_date => l_prev_year_end_date
4943 );
4944 ELSIF l_doctype_index = 4 AND l_req_enc_on
4945 THEN
4946 l_document_type := 'PA' ;
4947 -- IF requisition encumbrance is enabled then get details of BPAs to be processed
4948 -- fetching these into the temporary table.
4949 Fetch_BPAs (p_org_id => l_org_id,
4950 p_prev_year_start_date => l_prev_year_start_date,
4951 p_prev_year_end_date => l_prev_year_end_date
4952 );
4953 END IF ; -- l_doctype_index = 1 and l_po_enc_on
4954
4955 IF (g_debug_mode = 'Y') THEN
4956 Put_Debug_Msg (l_full_path,p_debug_msg => 'Processing Type: '||NVL(l_document_type,'NONE'));
4957 END IF;
4958
4959 -- retrieve and process records from temporary table
4960 OPEN c_get_recs(l_document_type) ;
4961
4962 LOOP
4963
4964 -- fetch records in batches determined by user entered parameter
4965 FETCH c_get_recs BULK COLLECT INTO l_document_id_rec.po_release_id,
4966 l_document_id_rec.po_header_id,
4967 l_document_id_rec.req_header_id
4968 LIMIT p_batch_size ;
4969
4970 IF (g_debug_mode = 'Y') THEN
4971 Put_Debug_Msg (l_full_path,p_debug_msg => 'Fetched latest batch of records');
4972 Put_Debug_Msg (l_full_path,p_debug_msg => 'total records retrieved '||l_document_id_rec.po_header_id.LAST);
4973 END IF;
4974
4975 -- EXIT WHEN c_get_recs%NOTFOUND ; <= removed as found not to work !!!
4976 EXIT WHEN l_document_id_rec.po_header_id.FIRST IS NULL;
4977
4978 -- loop through fetched records, processing each in turn
4979 FOR l_doc_index IN l_document_id_rec.po_release_id.FIRST .. l_document_id_rec.po_release_id.LAST
4980 LOOP
4981
4982 IF (g_debug_mode = 'Y') THEN
4983 Put_Debug_Msg (l_full_path,p_debug_msg => 'loop index:'||l_doc_index );
4984 Put_Debug_Msg (l_full_path,p_debug_msg => 'po header:'||l_document_id_rec.po_header_id(l_doc_index) );
4985 Put_Debug_Msg (l_full_path,p_debug_msg => 'po_release:'||l_document_id_rec.po_release_id(l_doc_index) );
4986 Put_Debug_Msg (l_full_path,p_debug_msg => 'req_header:'||l_document_id_rec.req_header_id(l_doc_index) );
4987 END IF;
4988
4989 Validate_And_Process_Doc(p_sobid => l_sob_id,
4990 p_org_id => l_org_id,
4991 p_year => p_year,
4992 p_process_phase => p_process_phase,
4993 p_process_frozen => p_process_frozen,
4994 p_batch_size => p_batch_size,
4995 p_document_type => l_document_type,
4996 p_po_release_id => l_document_id_rec.po_release_id(l_doc_index),
4997 p_po_header_id => l_document_id_rec.po_header_id(l_doc_index),
4998 p_req_header_id => l_document_id_rec.req_header_id(l_doc_index),
4999 p_prev_year_end_date => l_prev_year_end_date,
5000 p_prev_year_end_period => l_prev_year_end_period,
5001 p_prev_year_end_num => l_prev_year_end_num,
5002 p_prev_year_end_quarter => l_prev_year_end_quarter,
5003 p_curr_year_start_date => l_curr_year_start_date,
5004 p_curr_year_start_period => l_curr_year_start_period,
5005 p_curr_year_start_num => l_curr_year_start_num,
5006 p_curr_year_start_quarter => l_curr_year_start_quarter,
5007 p_conc_request_id => l_conc_request_id,
5008 x_return_code => l_return_code,
5009 x_msg_buf => l_msg_buf) ;
5010
5011 IF (g_debug_mode = 'Y') THEN
5012 Put_Debug_Msg (l_full_path,p_debug_msg => '**** Year End Main - completed Validate and Process Doc ****');
5013 END IF;
5014
5015 -- if not successful return then terminate processing
5016 IF l_return_code <> -99
5017 THEN
5018 Log_Error(p_sobid => l_sob_id,
5019 p_org_id => l_org_id,
5020 p_conc_request_id => l_conc_request_id,
5021 p_process_phase => p_process_phase) ;
5022
5023 Execute_Exceptions_Report(p_sobid => l_sob_id,
5024 p_org_id => l_org_id,
5025 p_conc_request_id => l_conc_request_id,
5026 p_process_phase => p_process_phase,
5027 p_year => p_year
5028 );
5029 retcode := l_return_code;
5030 errbuf := l_msg_buf ;
5031 RETURN;
5032 END IF; -- l_return_code <> -99
5033
5034
5035 END LOOP ; -- end loop of fetched records for this batch
5036
5037 IF (g_debug_mode = 'Y') THEN
5038 Put_Debug_Msg (l_full_path,p_debug_msg => '**** Year End Main - completed processing current batch of records ***');
5039 END IF;
5040
5041 -- log any errors for the batch just processed
5042 Log_Error(p_sobid => l_sob_id,
5043 p_org_id => l_org_id,
5044 p_conc_request_id => l_conc_request_id,
5045 p_process_phase => p_process_phase) ;
5046
5047 -- ssmales 01-May-2003 bug 2932056 added line below to cater for 8.1.7 database, which does not
5048 -- reinitialize array following no records found fetch
5049 l_document_id_rec.po_header_id.DELETE;
5050
5051 END LOOP ; -- end loop of fetching and processing records in batches
5052
5053 IF (g_debug_mode = 'Y') THEN
5054 Put_Debug_Msg (l_full_path,p_debug_msg => '**** Year End Main - completed processing '
5055 ||l_document_type||' ****');
5056 END IF;
5057
5058 CLOSE c_get_recs ;
5059
5060 END LOOP ; -- end loop of document type
5061 IF (g_debug_mode = 'Y') THEN
5062 Put_Debug_Msg (l_full_path,p_debug_msg => '**** Year End Main - completed all docs ****');
5063 END IF;
5064
5065
5066 -- terminate processing
5067
5068 Execute_Exceptions_Report(p_sobid => l_sob_id,
5069 p_org_id => l_org_id,
5070 p_conc_request_id => l_conc_request_id,
5071 p_process_phase => p_process_phase,
5072 p_year => p_year
5073 );
5074
5075 retcode := 0;
5076 errbuf := null;
5077
5078 EXCEPTION
5079 WHEN OTHERS THEN
5080 IF (FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)) THEN
5081 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, 'Year_End_Main');
5082 END IF;
5083 IF ( g_unexp_level >= g_debug_level ) THEN
5084 FND_MESSAGE.SET_NAME('IGC','IGC_LOGGING_UNEXP_ERROR');
5085 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
5086 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
5087 FND_LOG.MESSAGE ( g_unexp_level,l_full_path, TRUE);
5088 END IF;
5089 APP_EXCEPTION.Raise_Exception;
5090 retcode := 2 ;
5091 fnd_message.set_name('IGC','IGC_LOGGING_USER_ERROR');
5092 errbuf := fnd_message.get;
5093
5094
5095 END Year_End_Main ;
5096
5097
5098 END IGC_CBC_PO_YEAR_END_PKG;