1 PACKAGE BODY IGC_CC_COMMON_UTILS_PVT AS
2 /*$Header: IGCUTILB.pls 120.2.12010000.3 2009/01/10 04:55:45 vensubra ship $*/
3
4 -- -----------------------------------------------------------------------
5 -- Declare global variables.
6 -- -----------------------------------------------------------------------
7 G_PKG_NAME CONSTANT VARCHAR2(30):= 'IGC_CC_COMMON_UTILS_PVT';
8 l_debug_mode VARCHAR2(1) := NVL(FND_PROFILE.VALUE('IGC_DEBUG_ENABLED'),'N');
9
10 PROCEDURE Put_Debug_Msg (
11 p_debug_msg IN VARCHAR2
12 );
13
14 /*=======================================================================+
15 | PROCEDURE Put_Debug_Msg |
16 | |
17 | Note : This is a private function to output any debug information if |
18 | debug is enabled for the system to determine any issue that |
19 | may be happening at customer site. |
20 | |
21 | Parameters : |
22 | |
23 | Parameters for Procedure to process properly. |
24 | |
25 | p_debug_msg This is the message that is to be output to log for |
26 | debugging purposes. |
27 | |
28 +=======================================================================*/
29 PROCEDURE Put_Debug_Msg (
30 p_debug_msg IN VARCHAR2
31 ) IS
32
33 -- Constants :
34
35 l_Return_Status VARCHAR2(1);
36 l_api_name CONSTANT VARCHAR2(30) := 'Put_Debug_Msg';
37 l_prod VARCHAR2(3) := 'IGC';
38 l_sub_comp VARCHAR2(3) := 'CC';
39 l_profile_name VARCHAR2(255) := 'IGC_DEBUG_LOG_DIRECTORY';
40
41 BEGIN
42
43 IGC_MSGS_PKG.Put_Debug_Msg (p_debug_message => p_debug_msg,
44 p_profile_log_name => l_profile_name,
45 p_prod => l_prod,
46 p_sub_comp => l_sub_comp,
47 p_filename_val => NULL,
48 x_Return_Status => l_Return_Status
49 );
50 IF (l_Return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
51 raise FND_API.G_EXC_ERROR;
52 END IF;
53
54 RETURN;
55
56 -- --------------------------------------------------------------------
57 -- Exception handler section for the Put_Debug_Msg procedure.
58 -- --------------------------------------------------------------------
59 EXCEPTION
60
61 WHEN FND_API.G_EXC_ERROR THEN
62 RETURN;
63
64 WHEN OTHERS THEN
65 IF (FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)) THEN
66 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, l_api_name);
67 END IF;
68 RETURN;
69
70 END Put_Debug_Msg;
71
72 /*=======================================================================+
73 | PROCEDURE Get_Header_Desc
74 | |
75 | Note : This procedure is designed to get the descriptions of all the |
76 | coded fields stored at the header level in igc_cc_headers |
77 | It is used by forms like IGCCSUMM to get the descriptions |
78 | of the field to be displayed to the user. |
79 | |
80 | |
81 | Parameters : |
82 | |
83 | Standard header params for Public Procedures. |
84 | |
85 | p_api_version Version number for API to run |
86 | p_init_msg_list Message stack to be initialized flag |
87 | p_commit Is work to be commited here flag |
88 | p_validation_level Validation Level to be performed |
89 | p_return_status Status returned from Procedure |
90 | p_msg_count Number of messages on stack returned |
91 | p_msg_data Message text information returned |
92 | |
93 | Parameters for Procedure to process properly. |
94 | p_cc_header_id igc_cc_headers.cc_header_id |
95 | |
96 +=======================================================================*/
97 PROCEDURE Get_Header_Desc
98 (
99 p_api_version IN NUMBER,
100 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
101 p_commit IN VARCHAR2 := FND_API.G_FALSE,
102 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
103 p_return_status OUT NOCOPY VARCHAR2,
104 p_msg_count OUT NOCOPY NUMBER,
105 p_msg_data OUT NOCOPY VARCHAR2,
106
107 p_cc_header_id IN NUMBER,
108 p_type_desc OUT NOCOPY VARCHAR2,
109 p_state_desc OUT NOCOPY VARCHAR2,
110 p_apprvl_status_desc OUT NOCOPY VARCHAR2,
111 p_ctrl_status_desc OUT NOCOPY VARCHAR2,
112 p_cc_owner_name OUT NOCOPY VARCHAR2,
113 p_cc_preparer_name OUT NOCOPY VARCHAR2,
114 p_cc_access_level OUT NOCOPY VARCHAR2,
115 p_vendor_name OUT NOCOPY VARCHAR2,
116 p_bill_to_location OUT NOCOPY VARCHAR2,
117 p_vendor_site_code OUT NOCOPY VARCHAR2,
118 p_vendor_contact OUT NOCOPY VARCHAR2,
119 p_vendor_number OUT NOCOPY VARCHAR2,
120 p_term_name OUT NOCOPY VARCHAR2,
121 p_parent_cc_num OUT NOCOPY VARCHAR2,
122 p_vendor_hold_flag OUT NOCOPY VARCHAR2)
123 IS
124
125 CURSOR c_get_header (p_cc_header_id IN NUMBER)
126 IS
127 SELECT cc_header_id,
128 cc_type,
129 cc_state,
130 cc_ctrl_status,
131 cc_encmbrnc_status,
132 cc_apprvl_status,
133 vendor_id,
134 vendor_site_id,
135 vendor_contact_id,
136 term_id,
137 location_id,
138 cc_owner_user_id,
139 cc_preparer_user_id,
140 parent_header_id
141 FROM igc_cc_headers_all
142 WHERE cc_header_id = p_cc_header_id;
143
144
145 l_header_rec c_get_header%ROWTYPE;
146
147 l_msg_count NUMBER;
148 l_msg_data VARCHAR2(2000);
149 l_Return_Status VARCHAR2(1);
150 l_validation_error BOOLEAN := FALSE;
151 l_api_name CONSTANT VARCHAR2(30) := 'Get_Header_Desc';
152 l_api_version CONSTANT NUMBER := 1.0;
153
154 BEGIN
155
156 IF NOT FND_API.Compatible_API_Call ( l_api_version,
157 p_api_version,
158 l_api_name,
159 G_PKG_NAME )
160 THEN
161 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
162 END IF;
163
164 IF FND_API.to_Boolean ( p_init_msg_list ) THEN
165 FND_MSG_PUB.initialize ;
166 END IF;
167
168 -- --------------------------------------------------------------------
169 -- Initialize Return status
170 -- --------------------------------------------------------------------
171 p_return_status := FND_API.G_RET_STS_SUCCESS;
172
173 OPEN c_get_header (p_cc_header_id);
174 FETCH c_get_header INTO l_header_rec;
175 CLOSE c_get_header;
176
177 -- CC Type
178 BEGIN
179 SELECT lkpt.meaning
180 INTO p_type_desc
181 FROM fnd_lookups lkpt
182 WHERE lkpt.lookup_code = l_header_rec.cc_type
183 AND lkpt.lookup_type = 'IGC_CC_TYPE';
184 EXCEPTION
185 WHEN NO_DATA_FOUND THEN
186 p_type_desc := NULL ;
187 END;
188
189 -- CC State
190 BEGIN
191 SELECT lkpt.meaning
192 INTO p_state_desc
193 FROM fnd_lookups lkpt
194 WHERE lkpt.lookup_code = l_header_rec.cc_state
195 AND lkpt.lookup_type = 'IGC_CC_STATE';
196 EXCEPTION
197 WHEN NO_DATA_FOUND THEN
198 p_state_desc := NULL;
199 END;
200
201 -- CC Apprvl Status
202 BEGIN
203 SELECT lkpt.meaning
204 INTO p_apprvl_status_desc
205 FROM fnd_lookups lkpt
206 WHERE lkpt.lookup_code = l_header_rec.cc_apprvl_status
207 AND lkpt.lookup_type = 'IGC_CC_APPROVAL_STATUS';
208 EXCEPTION
209 WHEN NO_DATA_FOUND THEN
210 p_apprvl_status_desc := NULL;
211 END;
212
213 -- CC Ctrl Status
214 BEGIN
215 SELECT lkpt.meaning
216 INTO p_ctrl_status_desc
217 FROM fnd_lookups lkpt
218 WHERE lkpt.lookup_code = l_header_rec.cc_ctrl_status
219 AND lkpt.lookup_type = 'IGC_CC_CONTROL_STATUS';
220 EXCEPTION
221 WHEN NO_DATA_FOUND THEN
222 p_ctrl_status_desc := NULL;
223 END;
224
225 -- Vendor Name
226 -- Vendor Number
227 -- Vendor Site Code
228 BEGIN
229 SELECT pv.vendor_name,
230 pv.segment1,
231 pv.hold_flag,
232 pvs.vendor_site_code
233 INTO p_vendor_name,
234 p_vendor_number,
235 p_vendor_hold_flag,
236 p_vendor_site_code
237 FROM po_vendors pv,
238 po_vendor_sites_all pvs
239 WHERE pv.vendor_id = l_header_rec.vendor_id
240 AND pvs.vendor_site_id = l_header_rec.vendor_site_id
241 AND pvs.vendor_id = pv.vendor_id;
242 EXCEPTION
243 WHEN NO_DATA_FOUND THEN
244 p_vendor_name := NULL;
245 p_vendor_number := NULL;
246 p_vendor_site_code := NULL;
247 p_vendor_hold_flag := NULL;
248 END;
249
250 -- Vendor Contact
251 BEGIN
252 SELECT pvc.first_name ||' '|| pvc.last_name
253 INTO p_vendor_contact
254 FROM po_vendor_contacts pvc
255 WHERE pvc.vendor_contact_id (+) = l_header_rec.vendor_contact_id;
256 EXCEPTION
257 WHEN NO_DATA_FOUND THEN
258 p_vendor_contact := NULL;
259 END;
260
261 -- Preparer User Name
262 BEGIN
263 SELECT fup.user_name
264 INTO p_cc_preparer_name
265 FROM fnd_user fup
266 WHERE fup.user_id = l_header_rec.cc_preparer_user_id;
267 EXCEPTION
268 WHEN NO_DATA_FOUND THEN
269 p_cc_preparer_name := NULL;
270 END;
271
272 -- Owner User Name
273 BEGIN
274 SELECT fuo.user_name
275 INTO p_cc_owner_name
276 FROM fnd_user fuo
277 WHERE fuo.user_id = l_header_rec.cc_owner_user_id;
278 EXCEPTION
279 WHEN NO_DATA_FOUND THEN
280 p_cc_owner_name := NULL;
281 END;
282
283 -- Term Name
284 BEGIN
285 SELECT apt.name
286 INTO p_term_name
287 FROM ap_terms apt
288 WHERE apt.term_id = l_header_rec.term_id;
289 EXCEPTION
290 WHEN NO_DATA_FOUND THEN
291 p_term_name := NULL;
292 END;
293
294 -- Bill to Location
295 BEGIN
296 SELECT hrl.location_code
297 INTO p_bill_to_location
298 FROM hr_locations hrl
299 WHERE hrl.location_id = l_header_rec.location_id;
300 EXCEPTION
301 WHEN NO_DATA_FOUND THEN
302 p_bill_to_location := NULL;
303 END;
304
305 -- Parent CC Num
306 BEGIN
307 SELECT cch.cc_num
308 INTO p_parent_cc_num
309 FROM igc_cc_headers_all cch
310 WHERE cch.cc_header_id = l_header_rec.parent_header_id;
311 EXCEPTION
312 WHEN NO_DATA_FOUND THEN
313 p_parent_cc_num := NULL;
314 END;
315
316 -- Access Level
317 p_cc_access_level := SUBSTR(IGC_CC_ACCESS_PKG.get_access_level
318 (l_header_rec.cc_header_id,
319 FND_GLOBAL.USER_ID,
320 l_header_rec.cc_preparer_user_id,
321 l_header_rec.cc_owner_user_id), 1, 1);
322
323 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
324 p_data => p_msg_data );
325
326 RETURN;
327
328 -- -------------------------------------------------------------------------
329 -- Exception handler section for the Validate_CCID procedure.
330 -- -------------------------------------------------------------------------
331 EXCEPTION
332
333 WHEN FND_API.G_EXC_ERROR THEN
334
335 p_return_status := FND_API.G_RET_STS_ERROR;
336 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
337 p_data => p_msg_data );
338 RETURN;
339
340 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
341
342 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
343 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
344 p_data => p_msg_data );
345 RETURN;
346
347 WHEN OTHERS THEN
348
349 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
350 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
351 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
352 l_api_name);
353 END IF;
354
355 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
356 p_data => p_msg_data );
357 RETURN;
358
359 END Get_Header_Desc;
360
361
362 FUNCTION DATE_IS_VALID(x_form_name VARCHAR2,
363 x_gl_date gl_period_statuses.start_date%type,
364 x_po_header_id po_headers_all.po_header_id%type,
365 x_po_line_id po_lines_all.po_line_id%type,
366 x_line_location_id po_line_locations_all.line_location_id%type,
367 x_po_distribution_id po_distributions_all.po_distribution_id%type,
368 x_po_dist_num po_distributions_all.distribution_num%type,
369 x_shipment_num po_line_locations_all.shipment_num%type,
370 x_line_num po_lines_all.line_num%type)
371 RETURN BOOLEAN
372 IS
373
374 l_cc_num po_headers.segment1%type; /* cc number */
375 l_pf_date igc_cc_det_pf.cc_det_pf_date%type; /* Payment forecast date */
376 l_CC_STATE igc_cc_headers.cc_state%type;
377 l_CC_CTRL_STATUS igc_cc_headers.CC_CTRL_STATUS%type;
378 l_CC_ENCMBRNC_STATUS igc_cc_headers.CC_ENCMBRNC_STATUS%type;
379 l_CC_APPRVL_STATUS igc_cc_headers.CC_APPRVL_STATUS%type;
380 l_status BOOLEAN; /* holds value whether the CC can be matched or not */
381 l_fiscal_year_invoice gl_period_statuses.period_year%type; /* fiscal year of invoice */
382 l_fiscal_year_pf gl_period_statuses.period_year%type; /* fiscal year of specific Payment Forecast line */
383 l_period gl_period_statuses.period_name%TYPE;
384 l_open_gl_date gl_period_statuses.start_date%type; /* Holds the next open gl date */
385 l_gl_date gl_period_statuses.start_date%type;
386 l_cc_det_pf_date igc_cc_det_pf.cc_det_pf_date%type;
387 l_shipment_num po_line_locations_all.shipment_num%type;
388 l_line_location_id po_line_locations_all.line_location_id%type;
389
390 -- Cursor to get the account line id value
391 CURSOR c_acct_line(p_cc_num IN po_headers.segment1%type)
392 IS
393 SELECT CC_acct_LINE_ID
394 FROM IGC_CC_ACCT_LINES
395 WHERE cc_header_id = (SELECT cc_header_id
396 FROM IGC_CC_HEADERS
397 WHERE CC_NUM = p_cc_num)
398 AND CC_ACCT_LINE_NUM = x_shipment_num;
399
400 -- Cursor to get the distribution numbers of all the distributions for the CC
401 CURSOR for_each_dist_in_po(p_cc_num IN po_headers.segment1%type)
402 IS
403 SELECT distribution_num
404 FROM po_distributions
405 WHERE po_header_id = x_po_header_id
406 AND po_line_id = x_po_line_id
407 AND line_location_id = x_line_location_id;
408
409 -- Cursors for the call from Invoice Gateway
410 CURSOR c_get_po_line_id(p_po_header_id po_headers_all.po_header_id%type)
411 IS SELECT po_line_id
412 FROM po_lines
413 WHERE po_header_id = p_po_header_id;
414
415 CURSOR c_get_shipment_num(p_po_line_id po_lines_all.po_line_id%type)
416 IS SELECT line_location_id, shipment_num
417 FROM po_line_locations
418 WHERE po_line_id = p_po_line_id;
419
420 CURSOR c_get_cc_acct_line_id(p_shipment_num po_line_locations.shipment_num%type)
421 IS SELECT cc_acct_line_id
422 FROM igc_cc_acct_lines
423 WHERE cc_header_id = (SELECT cc_header_id
424 FROM IGC_CC_HEADERS
425 WHERE CC_NUM = l_cc_num)
426 AND cc_acct_line_num = p_shipment_num;
427
428 CURSOR c_get_distribution_num(p_po_line_id po_lines_all.po_line_id%type,
429 p_line_location_id po_line_locations.line_location_id%type)
430 IS SELECT distribution_num
431 FROM po_distributions
432 WHERE po_header_id = x_po_header_id
433 AND po_line_id = p_po_line_id
434 AND line_location_id = p_line_location_id;
435
436
437 BEGIN
438 l_status := TRUE;
439 l_open_gl_date := NULL;
440 l_gl_date := x_gl_date;
441
442 /* Get the GL period is open or not */
443 l_period := AP_UTILITIES_PKG.get_current_gl_date(l_gl_date);
444
445 IF (l_period IS NULL) THEN
446 ----------------------------------------------------------------------
447 -- Get gl_period and Date from a future period
448 ----------------------------------------------------------------------
449 AP_UTILITIES_PKG.get_open_gl_date
450 (l_gl_Date,
451 l_period,
452 l_open_gl_date
453 );
454 l_gl_date := l_open_gl_date;
455
456 IF (l_gl_date IS NULL) THEN
457 FND_MESSAGE.SET_NAME('SQLAP','AP_DISTS_NO_OPEN_FUT_PERIOD');
458 APP_EXCEPTION.RAISE_EXCEPTION;
459 END IF;
460 END IF;
461
462 /* cc number is stored in the segment1 so get it */
463 BEGIN
464 SELECT segment1
465 INTO l_cc_num
466 FROM po_headers
467 WHERE po_header_id = x_po_header_id;
468 EXCEPTION
469 WHEN OTHERS THEN
470 FND_MESSAGE.SET_NAME('IGC', 'IGC_LOGGING_UNEXP_ERROR');
471 FND_MESSAGE.SET_TOKEN('CODE', SQLCODE);
472 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
473 APP_EXCEPTION.RAISE_EXCEPTION;
474 END;
475
476
477 /* Get the different status of the CC */
478 BEGIN
479 SELECT CC_STATE,
480 CC_CTRL_STATUS,
481 CC_ENCMBRNC_STATUS,
482 CC_APPRVL_STATUS
483 INTO l_CC_STATE,
484 l_CC_CTRL_STATUS,
485 l_CC_ENCMBRNC_STATUS,
486 l_CC_APPRVL_STATUS
487 FROM IGC_CC_HEADERS
488 WHERE CC_NUM = l_cc_num;
489 EXCEPTION
490 /* No need to check for validation if its a PO, bug # 5687596*/
491 WHEN NO_DATA_FOUND THEN
492 RETURN TRUE;
493 WHEN OTHERS THEN
494 FND_MESSAGE.SET_NAME('IGC', 'IGC_LOGGING_UNEXP_ERROR');
495 FND_MESSAGE.SET_TOKEN('CODE', SQLCODE);
496 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
497 APP_EXCEPTION.RAISE_EXCEPTION;
498 END;
499
500
501 /* Check whether the status of the CC is valid to be matched */
502 IF l_CC_STATE = 'CM'
503 AND l_CC_CTRL_STATUS = 'O'
504 AND l_CC_ENCMBRNC_STATUS = 'C'
505 AND l_CC_APPRVL_STATUS = 'AP' THEN
506
507 /* Get the fiscal year of the invoice */
508 BEGIN
509 SELECT DISTINCT period_year
510 INTO l_fiscal_year_invoice
511 FROM gl_period_statuses
512 WHERE set_of_books_id = (SELECT set_of_books_id
513 FROM AP_SYSTEM_PARAMETERS)
514 AND l_gl_date BETWEEN START_DATE AND END_DATE;
515 EXCEPTION
516 WHEN OTHERS THEN
517 FND_MESSAGE.SET_NAME('IGC', 'IGC_LOGGING_UNEXP_ERROR');
518 FND_MESSAGE.SET_TOKEN('CODE', SQLCODE);
519 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
520 APP_EXCEPTION.RAISE_EXCEPTION;
521 END;
522
523 IF x_form_name = 'APXINWKB' THEN
524 /* Check whether it is the Match button(dist id is NULL) that is pressed or the Distribute button */
525 IF x_po_dist_num IS NULL THEN
526
527 /*Loop through each account line */
528 FOR c_acct_line1 IN c_acct_line(l_cc_num)
529 LOOP
530 /*Loop through each of the distributions in PO */
531 FOR c_for_each_dist_in_po1 IN for_each_dist_in_po(l_cc_num)
532 LOOP
533 /*Loop and get the pf date of only those Payment Forecast lines which
534 * have corresponding distribution line in
535 * po_distributions*/
536 BEGIN
537 SELECT CC_DET_PF_DATE
538 INTO l_cc_det_pf_date
539 FROM IGC_CC_DET_PF
540 WHERE CC_acct_LINE_ID = c_acct_line1.cc_acct_line_id
541 AND cc_det_pf_line_num = c_for_each_dist_in_po1.distribution_num;
542
543 EXCEPTION
544 WHEN OTHERS THEN
545 FND_MESSAGE.SET_NAME('IGC', 'IGC_LOGGING_UNEXP_ERROR');
546 FND_MESSAGE.SET_TOKEN('CODE', SQLCODE);
547 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
548 APP_EXCEPTION.RAISE_EXCEPTION;
549 END;
550
551 /* Get the fiscal year for the Payment Forecast Date */
552 BEGIN
553 SELECT DISTINCT period_year
554 INTO l_fiscal_year_pf
555 FROM gl_period_statuses
556 WHERE set_of_books_id = (SELECT set_of_books_id
557 FROM AP_SYSTEM_PARAMETERS)
558 AND l_CC_DET_PF_DATE BETWEEN START_DATE AND END_DATE;
559 EXCEPTION
560 WHEN OTHERS THEN
561 FND_MESSAGE.SET_NAME('IGC', 'IGC_LOGGING_UNEXP_ERROR');
562 FND_MESSAGE.SET_TOKEN('CODE', SQLCODE);
563 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
564 APP_EXCEPTION.RAISE_EXCEPTION;
565 END;
566
567 /* Even if one Payment Forecast Date fiscal year does not match
568 the fiscal year of the invoice stop processing and return false */
569 IF (l_fiscal_year_invoice = l_fiscal_year_pf) THEN
570 l_status := TRUE;
571 ELSE
572 RETURN FALSE;
573 END IF;
574 END LOOP;
575
576 END LOOP;
577 ELSE
578 /* Distribute button is pressed and a specific Distribution line is being matched */
579 BEGIN
580 /* Get the Payment Forecast Date for that particular distribution line*/
581 SELECT CC_DET_PF_DATE
582 INTO l_pf_date
583 FROM IGC_CC_DET_PF
584 WHERE CC_acct_LINE_ID = (SELECT CC_acct_LINE_ID
585 FROM IGC_CC_ACCT_LINES
586 WHERE cc_header_id = (SELECT cc_header_id
587 FROM IGC_CC_HEADERS
588 WHERE CC_NUM = l_cc_num)
589 AND CC_ACCT_LINE_NUM = x_shipment_num)
590 AND CC_DET_PF_LINE_NUM = x_po_dist_num;
591 EXCEPTION
592 WHEN OTHERS THEN
593 FND_MESSAGE.SET_NAME('IGC', 'IGC_LOGGING_UNEXP_ERROR');
594 FND_MESSAGE.SET_TOKEN('CODE', SQLCODE);
595 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
596 APP_EXCEPTION.RAISE_EXCEPTION;
597 END;
598
599
600 /* Get the fiscal year for the Payment Forecast Date */
601 BEGIN
602 SELECT DISTINCT period_year
603 INTO l_fiscal_year_pf
604 FROM gl_period_statuses
605 WHERE set_of_books_id = (SELECT set_of_books_id
606 FROM AP_SYSTEM_PARAMETERS)
607 AND l_pf_date BETWEEN START_DATE AND END_DATE;
608 EXCEPTION
609 WHEN OTHERS THEN
610 FND_MESSAGE.SET_NAME('IGC', 'IGC_LOGGING_UNEXP_ERROR');
611 FND_MESSAGE.SET_TOKEN('CODE', SQLCODE);
612 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
613 APP_EXCEPTION.RAISE_EXCEPTION;
614 END;
615
616
617
618 IF (l_fiscal_year_invoice = l_fiscal_year_pf) THEN
619 l_status := TRUE;
620 ELSE
621 RETURN FALSE;
622 END IF;
623
624 END IF;
625 -- The function is called from Invoice Gateway form
626 -- Eight cases needs to be handled
627 -- a. Only PO number is entered so check the dates of the pf lines belonging to
628 -- each of the lines and their shipments
629 -- b. PO number and line number are entered so check the dates of the pf lines
630 -- belonging to that line number and its corresponding shipments
631 -- c. PO number,line number and shipment number is entered so check the dates of
632 -- pf lines belonging only to that shipment number and line number.
633 -- d. PO number, line number, shipment number and distribution is entered so check
634 -- date only for that specfic pf line
635 -- e. PO number and shipment number entered.
636 -- f. PO number and distribution number entered.
637 -- g. PO number, shipment number and distribution number entered.
638 -- h. PO number, line number and distribution number entered.
639 ELSIF x_form_name = 'APXIISIM' THEN
640 -- Case a: Only PO number is entered so check for all the lines,shipments and distributions
641 IF x_line_num IS NULL AND x_shipment_num IS NULL AND x_po_dist_num IS NULL THEN
642
643 FOR c_get_po_line_id1 IN c_get_po_line_id(x_po_header_id)
644 LOOP
645 FOR c_get_shipment_num1 IN c_get_shipment_num(c_get_po_line_id1.po_line_id)
646 LOOP
647 FOR c_get_cc_acct_line_id1 IN c_get_cc_acct_line_id(c_get_shipment_num1.shipment_num)
648 LOOP
649 FOR c_get_distribution_num1 IN c_get_distribution_num(c_get_po_line_id1.po_line_id,
650 c_get_shipment_num1.line_location_id)
651
652 LOOP
653 BEGIN
654 SELECT cc_det_pf_date
655 INTO l_cc_det_pf_date
656 FROM igc_cc_det_pf
657 WHERE cc_acct_line_id = c_get_cc_acct_line_id1.cc_acct_line_id
658 AND cc_det_pf_line_num = c_get_distribution_num1.distribution_num;
659 EXCEPTION
660 WHEN OTHERS THEN
661 FND_MESSAGE.SET_NAME('IGC', 'IGC_LOGGING_UNEXP_ERROR');
662 FND_MESSAGE.SET_TOKEN('CODE', SQLCODE);
663 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
664 APP_EXCEPTION.RAISE_EXCEPTION;
665 END;
666
667
668 BEGIN
669 SELECT DISTINCT period_year
670 INTO l_fiscal_year_pf
671 FROM gl_period_statuses
672 WHERE set_of_books_id = (SELECT set_of_books_id
673 FROM AP_SYSTEM_PARAMETERS)
674 AND l_cc_det_pf_date BETWEEN START_DATE AND END_DATE;
675 EXCEPTION
676 WHEN OTHERS 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 APP_EXCEPTION.RAISE_EXCEPTION;
681 END;
682
683 IF (l_fiscal_year_invoice = l_fiscal_year_pf) THEN
684 l_status := TRUE;
685 ELSE
686 RETURN FALSE;
687 END IF;
688
689 END LOOP;
690 END LOOP;
691 END LOOP;
692 END LOOP;
693
694 -- Case b: Only PO number and line number is entered so check for all the shipments and its
695 -- distributions
696 ELSIF x_line_num IS NOT NULL AND x_shipment_num IS NULL AND x_po_dist_num IS NULL THEN
697
698 FOR c_get_shipment_num1 IN c_get_shipment_num(x_po_line_id)
699 LOOP
700 FOR c_get_cc_acct_line_id1 IN c_get_cc_acct_line_id(c_get_shipment_num1.shipment_num)
701 LOOP
702 FOR c_get_distribution_num1 IN c_get_distribution_num(x_po_line_id,
703 c_get_shipment_num1.line_location_id)
704
705 LOOP
706 BEGIN
707 SELECT cc_det_pf_date
708 INTO l_cc_det_pf_date
709 FROM igc_cc_det_pf
710 WHERE cc_acct_line_id = c_get_cc_acct_line_id1.cc_acct_line_id
711 AND cc_det_pf_line_num = c_get_distribution_num1.distribution_num;
712 EXCEPTION
713 WHEN OTHERS THEN
714 FND_MESSAGE.SET_NAME('IGC', 'IGC_LOGGING_UNEXP_ERROR');
715 FND_MESSAGE.SET_TOKEN('CODE', SQLCODE);
716 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
717 APP_EXCEPTION.RAISE_EXCEPTION;
718 END;
719
720 BEGIN
721 SELECT DISTINCT period_year
722 INTO l_fiscal_year_pf
723 FROM gl_period_statuses
724 WHERE set_of_books_id = (SELECT set_of_books_id
725 FROM AP_SYSTEM_PARAMETERS)
726 AND l_cc_det_pf_date BETWEEN START_DATE AND END_DATE;
727 EXCEPTION
728 WHEN OTHERS THEN
729 FND_MESSAGE.SET_NAME('IGC', 'IGC_LOGGING_UNEXP_ERROR');
730 FND_MESSAGE.SET_TOKEN('CODE', SQLCODE);
731 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
732 APP_EXCEPTION.RAISE_EXCEPTION;
733 END;
734
735
736 IF (l_fiscal_year_invoice = l_fiscal_year_pf) THEN
737 l_status := TRUE;
738 ELSE
739 RETURN FALSE;
740 END IF;
741
742 END LOOP;
743 END LOOP;
744 END LOOP;
745
746 -- Cases c and e : Po number, Line number(may or may not be entered), Shipment number is entered so
747 -- check for all the distributions
748
749 ELSIF x_shipment_num IS NOT NULL AND x_po_dist_num IS NULL THEN
750
751 FOR c_get_cc_acct_line_id1 IN c_get_cc_acct_line_id(x_shipment_num)
752 LOOP
753 FOR c_get_distribution_num1 IN c_get_distribution_num(x_po_line_id,
754 x_line_location_id)
755
756 LOOP
757 BEGIN
758 SELECT cc_det_pf_date
759 INTO l_cc_det_pf_date
760 FROM igc_cc_det_pf
761 WHERE cc_acct_line_id = c_get_cc_acct_line_id1.cc_acct_line_id
762 AND cc_det_pf_line_num = c_get_distribution_num1.distribution_num;
763 EXCEPTION
764 WHEN OTHERS THEN
765 FND_MESSAGE.SET_NAME('IGC', 'IGC_LOGGING_UNEXP_ERROR');
766 FND_MESSAGE.SET_TOKEN('CODE', SQLCODE);
767 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
768 APP_EXCEPTION.RAISE_EXCEPTION;
769 END;
770
771 BEGIN
772 SELECT DISTINCT period_year
773 INTO l_fiscal_year_pf
774 FROM gl_period_statuses
775 WHERE set_of_books_id = (SELECT set_of_books_id
776 FROM AP_SYSTEM_PARAMETERS)
777 AND l_cc_det_pf_date BETWEEN START_DATE AND END_DATE;
778 EXCEPTION
779 WHEN OTHERS THEN
780 FND_MESSAGE.SET_NAME('IGC', 'IGC_LOGGING_UNEXP_ERROR');
781 FND_MESSAGE.SET_TOKEN('CODE', SQLCODE);
782 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
783 APP_EXCEPTION.RAISE_EXCEPTION;
784 END;
785
786 IF (l_fiscal_year_invoice = l_fiscal_year_pf) THEN
787 l_status := TRUE;
788 ELSE
789 RETURN FALSE;
790 END IF;
791
792 END LOOP;
793 END LOOP;
794
795 -- Cases d, f, g and h:
796 ELSIF x_po_dist_num IS NOT NULL THEN
797
798 l_shipment_num := x_shipment_num;
799
800 IF x_shipment_num IS NULL THEN
801 BEGIN
802 SELECT line_location_id
803 INTO l_line_location_id
804 FROM po_distributions
805 WHERE po_distribution_id = x_po_distribution_id;
806
807
808 SELECT shipment_num
809 INTO l_shipment_num
810 FROM po_line_locations
811 WHERE line_location_id = l_line_location_id;
812 EXCEPTION
813 WHEN OTHERS THEN
814 FND_MESSAGE.SET_NAME('IGC', 'IGC_LOGGING_UNEXP_ERROR');
815 FND_MESSAGE.SET_TOKEN('CODE', SQLCODE);
816 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
817 APP_EXCEPTION.RAISE_EXCEPTION;
818 END;
819
820 END IF;
821
822 FOR c_get_cc_acct_line_id1 IN c_get_cc_acct_line_id(l_shipment_num)
823 LOOP
824 BEGIN
825 SELECT cc_det_pf_date
826 INTO l_cc_det_pf_date
827 FROM igc_cc_det_pf
828 WHERE cc_acct_line_id = c_get_cc_acct_line_id1.cc_acct_line_id
829 AND cc_det_pf_line_num = x_po_dist_num;
830 EXCEPTION
831 WHEN OTHERS THEN
832 FND_MESSAGE.SET_NAME('IGC', 'IGC_LOGGING_UNEXP_ERROR');
833 FND_MESSAGE.SET_TOKEN('CODE', SQLCODE);
834 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
835 APP_EXCEPTION.RAISE_EXCEPTION;
836 END;
837
838 BEGIN
839 SELECT DISTINCT period_year
840 INTO l_fiscal_year_pf
841 FROM gl_period_statuses
842 WHERE set_of_books_id = (SELECT set_of_books_id
843 FROM AP_SYSTEM_PARAMETERS)
844 AND l_cc_det_pf_date BETWEEN START_DATE AND END_DATE;
845 EXCEPTION
846 WHEN OTHERS THEN
847 FND_MESSAGE.SET_NAME('IGC', 'IGC_LOGGING_UNEXP_ERROR');
848 FND_MESSAGE.SET_TOKEN('CODE', SQLCODE);
849 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
850 APP_EXCEPTION.RAISE_EXCEPTION;
851 END;
852
853 IF (l_fiscal_year_invoice = l_fiscal_year_pf) THEN
854 l_status := TRUE;
855 ELSE
856 RETURN FALSE;
857 END IF;
858
859 END LOOP;
860
861 END IF;
862 END IF;
863
864 END IF;
865 RETURN TRUE;
866
867 END DATE_IS_VALID;
868
869 /*=======================================================================+
870 | FUNCTION XML_REPORT_ENABLED
871 | |
872 | Note : This function is designed to decide if the xml report(s) is |
873 | to be triggered or not. Presently it returns true. In future |
874 | the function can be modified to incorporate profile options |
875 and return true/false based on conditions. |
876 +=======================================================================*/
877
878 FUNCTION XML_REPORT_ENABLED
879 RETURN BOOLEAN
880 IS
881 BEGIN
882 RETURN FALSE;
883 END XML_REPORT_ENABLED;
884
885 /*=======================================================================+
886 | PROCEDURE GET_XML_LAYOUT_INFO
887 | |
888 | Note : This procedure is designed to get layout information of the |
889 | xml report that is to be generated. |
890 | |
891 | Parameters : |
892 | |
893 | p_lang Language, takes the default value |
894 | when no value is obtained |
895 | p_terr Territory, takes the default value |
896 | when no value is obtained |
897 | p_lob_code BiPubllisher Code for the XML Report |
898 | p_application_short_name Short Name of the Application |
899 | p_template_code Template Code for the XML Report |
900 +=======================================================================*/
901
902 PROCEDURE GET_XML_LAYOUT_INFO(
903 p_lang IN OUT NOCOPY VARCHAR2,
904 p_terr IN OUT NOCOPY VARCHAR2,
905 p_lob_code IN VARCHAR2,
906 p_application_short_name IN VARCHAR2,
907 p_template_code IN VARCHAR2
908 )
909 IS
910 ls_lang VARCHAR2(10);
911 v_cnt NUMBER;
912 l_lang VARCHAR2(10);
913 CURSOR xml_cur
914 IS
915 SELECT LANGUAGE,
916 TERRITORY
917 FROM XDO_LOBS
918 WHERE LOB_CODE = p_lob_code
919 AND APPLICATION_SHORT_NAME = p_application_short_name
920 AND (LOB_TYPE = 'TEMPLATE'
921 OR LOB_TYPE = 'MLS_TEMPLATE')
922 AND LANGUAGE = p_lang
923 AND XDO_FILE_TYPE = 'XSL-FO';
924 BEGIN
925 SELECT SUBSTR(userenv('LANG'),1,4) INTO ls_lang FROM dual;
926 IF ls_lang = 'US' THEN
927 p_lang := 'en' ;
928 ELSE
929 p_lang := lower(ls_lang);
930 END IF;
931 OPEN xml_cur;
932 FETCH xml_cur INTO l_lang,p_terr;
933 v_cnt := xml_cur%rowcount;
934 CLOSE xml_cur;
935 IF v_cnt = 0 THEN
936 SELECT default_language,
937 default_territory
938 INTO l_lang,
939 p_terr
940 FROM XDO_TEMPLATES_B
941 WHERE TEMPLATE_CODE = p_template_code
942 AND APPLICATION_SHORT_NAME = p_application_short_name ;
943 END IF;
944 l_lang := p_lang;
945 END GET_XML_LAYOUT_INFO;
946
947
948
949 END IGC_CC_COMMON_UTILS_PVT;
950
[Home] [Help]