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