1 PACKAGE BODY IGC_CC_COMMON_UTILS_PVT AS
2 /*$Header: IGCUTILB.pls 120.5 2011/03/03 09:41:22 dramired 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 /*=======================================================================+
76 | coded fields stored at the header level in igc_cc_headers |
73 | PROCEDURE Get_Header_Desc
74 | |
75 | Note : This procedure is designed to get the descriptions of all the |
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
213 -- CC Ctrl Status
210 p_apprvl_status_desc := NULL;
211 END;
212
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 --bug 11699202
257 AND rownum = 1;
258 EXCEPTION
259 WHEN NO_DATA_FOUND THEN
260 p_vendor_contact := NULL;
261 END;
262
263 -- Preparer User Name
264 BEGIN
265 SELECT fup.user_name
266 INTO p_cc_preparer_name
267 FROM fnd_user fup
268 WHERE fup.user_id = l_header_rec.cc_preparer_user_id;
269 EXCEPTION
270 WHEN NO_DATA_FOUND THEN
271 p_cc_preparer_name := NULL;
272 END;
273
274 -- Owner User Name
275 BEGIN
276 SELECT fuo.user_name
277 INTO p_cc_owner_name
278 FROM fnd_user fuo
279 WHERE fuo.user_id = l_header_rec.cc_owner_user_id;
280 EXCEPTION
281 WHEN NO_DATA_FOUND THEN
282 p_cc_owner_name := NULL;
283 END;
284
285 -- Term Name
286 BEGIN
287 SELECT apt.name
288 INTO p_term_name
289 FROM ap_terms apt
290 WHERE apt.term_id = l_header_rec.term_id;
291 EXCEPTION
292 WHEN NO_DATA_FOUND THEN
293 p_term_name := NULL;
294 END;
295
296 -- Bill to Location
297 BEGIN
298 SELECT hrl.location_code
299 INTO p_bill_to_location
300 FROM hr_locations hrl
301 WHERE hrl.location_id = l_header_rec.location_id;
302 EXCEPTION
303 WHEN NO_DATA_FOUND THEN
304 p_bill_to_location := NULL;
305 END;
306
307 -- Parent CC Num
308 BEGIN
309 SELECT cch.cc_num
310 INTO p_parent_cc_num
311 FROM igc_cc_headers_all cch
312 WHERE cch.cc_header_id = l_header_rec.parent_header_id;
313 EXCEPTION
314 WHEN NO_DATA_FOUND THEN
315 p_parent_cc_num := NULL;
316 END;
317
318 -- Access Level
319 p_cc_access_level := SUBSTR(IGC_CC_ACCESS_PKG.get_access_level
320 (l_header_rec.cc_header_id,
321 FND_GLOBAL.USER_ID,
322 l_header_rec.cc_preparer_user_id,
323 l_header_rec.cc_owner_user_id), 1, 1);
324
325 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
326 p_data => p_msg_data );
327
328 RETURN;
329
330 -- -------------------------------------------------------------------------
331 -- Exception handler section for the Validate_CCID procedure.
332 -- -------------------------------------------------------------------------
333 EXCEPTION
334
335 WHEN FND_API.G_EXC_ERROR THEN
336
337 p_return_status := FND_API.G_RET_STS_ERROR;
338 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
339 p_data => p_msg_data );
340 RETURN;
341
342 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
343
344 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
345 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
346 p_data => p_msg_data );
347 RETURN;
348
349 WHEN OTHERS THEN
350
351 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
352 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
353 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
354 l_api_name);
355 END IF;
356
357 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
358 p_data => p_msg_data );
359 RETURN;
360
361 END Get_Header_Desc;
362
363
364 FUNCTION DATE_IS_VALID(x_form_name VARCHAR2,
365 x_gl_date gl_period_statuses.start_date%type,
366 x_po_header_id po_headers_all.po_header_id%type,
367 x_po_line_id po_lines_all.po_line_id%type,
368 x_line_location_id po_line_locations_all.line_location_id%type,
369 x_po_distribution_id po_distributions_all.po_distribution_id%type,
370 x_po_dist_num po_distributions_all.distribution_num%type,
371 x_shipment_num po_line_locations_all.shipment_num%type,
372 x_line_num po_lines_all.line_num%type)
373 RETURN BOOLEAN
374 IS
375
376 l_cc_num po_headers.segment1%type; /* cc number */
377 l_pf_date igc_cc_det_pf.cc_det_pf_date%type; /* Payment forecast date */
378 l_CC_STATE igc_cc_headers.cc_state%type;
379 l_CC_CTRL_STATUS igc_cc_headers.CC_CTRL_STATUS%type;
380 l_CC_ENCMBRNC_STATUS igc_cc_headers.CC_ENCMBRNC_STATUS%type;
381 l_CC_APPRVL_STATUS igc_cc_headers.CC_APPRVL_STATUS%type;
382 l_status BOOLEAN; /* holds value whether the CC can be matched or not */
383 l_fiscal_year_invoice gl_period_statuses.period_year%type; /* fiscal year of invoice */
384 l_fiscal_year_pf gl_period_statuses.period_year%type; /* fiscal year of specific Payment Forecast line */
385 l_period gl_period_statuses.period_name%TYPE;
386 l_open_gl_date gl_period_statuses.start_date%type; /* Holds the next open gl date */
387 l_gl_date gl_period_statuses.start_date%type;
388 l_cc_det_pf_date igc_cc_det_pf.cc_det_pf_date%type;
389 l_shipment_num po_line_locations_all.shipment_num%type;
390 l_line_location_id po_line_locations_all.line_location_id%type;
391
392 -- Cursor to get the account line id value
393 CURSOR c_acct_line(p_cc_num IN po_headers.segment1%type)
394 IS
395 SELECT CC_acct_LINE_ID
396 FROM IGC_CC_ACCT_LINES
397 WHERE cc_header_id = (SELECT cc_header_id
398 FROM IGC_CC_HEADERS
399 WHERE CC_NUM = p_cc_num)
400 AND CC_ACCT_LINE_NUM = x_shipment_num;
401
402 -- Cursor to get the distribution numbers of all the distributions for the CC
403 CURSOR for_each_dist_in_po(p_cc_num IN po_headers.segment1%type)
404 IS
405 SELECT distribution_num
406 FROM po_distributions
407 WHERE po_header_id = x_po_header_id
408 AND po_line_id = x_po_line_id
409 AND line_location_id = x_line_location_id;
410
411 -- Cursors for the call from Invoice Gateway
412 CURSOR c_get_po_line_id(p_po_header_id po_headers_all.po_header_id%type)
413 IS SELECT po_line_id
414 FROM po_lines
415 WHERE po_header_id = p_po_header_id;
416
417 CURSOR c_get_shipment_num(p_po_line_id po_lines_all.po_line_id%type)
418 IS SELECT line_location_id, shipment_num
419 FROM po_line_locations
420 WHERE po_line_id = p_po_line_id;
421
422 CURSOR c_get_cc_acct_line_id(p_shipment_num po_line_locations.shipment_num%type)
423 IS SELECT cc_acct_line_id
424 FROM igc_cc_acct_lines
425 WHERE cc_header_id = (SELECT cc_header_id
426 FROM IGC_CC_HEADERS
427 WHERE CC_NUM = l_cc_num)
428 AND cc_acct_line_num = p_shipment_num;
429
430 CURSOR c_get_distribution_num(p_po_line_id po_lines_all.po_line_id%type,
431 p_line_location_id po_line_locations.line_location_id%type)
432 IS SELECT distribution_num
433 FROM po_distributions
434 WHERE po_header_id = x_po_header_id
435 AND po_line_id = p_po_line_id
436 AND line_location_id = p_line_location_id;
437
438
439 BEGIN
440 l_status := TRUE;
441 l_open_gl_date := NULL;
442 l_gl_date := x_gl_date;
443
444 /* Get the GL period is open or not */
445 l_period := AP_UTILITIES_PKG.get_current_gl_date(l_gl_date);
446
447 IF (l_period IS NULL) THEN
448 ----------------------------------------------------------------------
449 -- Get gl_period and Date from a future period
450 ----------------------------------------------------------------------
451 AP_UTILITIES_PKG.get_open_gl_date
452 (l_gl_Date,
453 l_period,
454 l_open_gl_date
455 );
456 l_gl_date := l_open_gl_date;
457
458 IF (l_gl_date IS NULL) THEN
459 FND_MESSAGE.SET_NAME('SQLAP','AP_DISTS_NO_OPEN_FUT_PERIOD');
460 APP_EXCEPTION.RAISE_EXCEPTION;
461 END IF;
462 END IF;
463
464 /* cc number is stored in the segment1 so get it */
465 BEGIN
466 SELECT segment1
467 INTO l_cc_num
468 FROM po_headers
469 WHERE po_header_id = x_po_header_id;
470 EXCEPTION
471 WHEN OTHERS THEN
472 FND_MESSAGE.SET_NAME('IGC', 'IGC_LOGGING_UNEXP_ERROR');
473 FND_MESSAGE.SET_TOKEN('CODE', SQLCODE);
474 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
475 APP_EXCEPTION.RAISE_EXCEPTION;
476 END;
477
478
479 /* Get the different status of the CC */
480 BEGIN
481 SELECT CC_STATE,
482 CC_CTRL_STATUS,
483 CC_ENCMBRNC_STATUS,
484 CC_APPRVL_STATUS
485 INTO l_CC_STATE,
486 l_CC_CTRL_STATUS,
487 l_CC_ENCMBRNC_STATUS,
488 l_CC_APPRVL_STATUS
489 FROM IGC_CC_HEADERS
490 WHERE CC_NUM = l_cc_num;
491 EXCEPTION
492 /* No need to check for validation if its a PO, bug # 5687596*/
493 WHEN NO_DATA_FOUND THEN
494 RETURN TRUE;
495 WHEN OTHERS THEN
496 FND_MESSAGE.SET_NAME('IGC', 'IGC_LOGGING_UNEXP_ERROR');
497 FND_MESSAGE.SET_TOKEN('CODE', SQLCODE);
498 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
499 APP_EXCEPTION.RAISE_EXCEPTION;
500 END;
501
502
503 /* Check whether the status of the CC is valid to be matched */
504 IF l_CC_STATE = 'CM'
505 AND l_CC_CTRL_STATUS = 'O'
506 AND l_CC_ENCMBRNC_STATUS = 'C'
507 AND l_CC_APPRVL_STATUS = 'AP' THEN
508
509 /* Get the fiscal year of the invoice */
510 BEGIN
511 SELECT DISTINCT period_year
512 INTO l_fiscal_year_invoice
513 FROM gl_period_statuses
514 WHERE set_of_books_id = (SELECT set_of_books_id
515 FROM AP_SYSTEM_PARAMETERS)
516 AND l_gl_date BETWEEN START_DATE AND END_DATE;
517 EXCEPTION
518 WHEN OTHERS THEN
519 FND_MESSAGE.SET_NAME('IGC', 'IGC_LOGGING_UNEXP_ERROR');
520 FND_MESSAGE.SET_TOKEN('CODE', SQLCODE);
521 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
522 APP_EXCEPTION.RAISE_EXCEPTION;
523 END;
524
525 IF x_form_name = 'APXINWKB' THEN
526 /* Check whether it is the Match button(dist id is NULL) that is pressed or the Distribute button */
527 IF x_po_dist_num IS NULL THEN
528
529 /*Loop through each account line */
530 FOR c_acct_line1 IN c_acct_line(l_cc_num)
531 LOOP
532 /*Loop through each of the distributions in PO */
533 FOR c_for_each_dist_in_po1 IN for_each_dist_in_po(l_cc_num)
534 LOOP
535 /*Loop and get the pf date of only those Payment Forecast lines which
536 * have corresponding distribution line in
537 * po_distributions*/
538 BEGIN
539 SELECT CC_DET_PF_DATE
540 INTO l_cc_det_pf_date
541 FROM IGC_CC_DET_PF
542 WHERE CC_acct_LINE_ID = c_acct_line1.cc_acct_line_id
543 AND cc_det_pf_line_num = c_for_each_dist_in_po1.distribution_num;
544
545 EXCEPTION
546 WHEN OTHERS THEN
547 FND_MESSAGE.SET_NAME('IGC', 'IGC_LOGGING_UNEXP_ERROR');
548 FND_MESSAGE.SET_TOKEN('CODE', SQLCODE);
549 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
550 APP_EXCEPTION.RAISE_EXCEPTION;
551 END;
552
553 /* Get the fiscal year for the Payment Forecast Date */
554 BEGIN
555 SELECT DISTINCT period_year
556 INTO l_fiscal_year_pf
557 FROM gl_period_statuses
558 WHERE set_of_books_id = (SELECT set_of_books_id
559 FROM AP_SYSTEM_PARAMETERS)
560 AND l_CC_DET_PF_DATE BETWEEN START_DATE AND END_DATE;
561 EXCEPTION
562 WHEN OTHERS THEN
563 FND_MESSAGE.SET_NAME('IGC', 'IGC_LOGGING_UNEXP_ERROR');
564 FND_MESSAGE.SET_TOKEN('CODE', SQLCODE);
565 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
566 APP_EXCEPTION.RAISE_EXCEPTION;
567 END;
568
569 /* Even if one Payment Forecast Date fiscal year does not match
570 the fiscal year of the invoice stop processing and return false */
571 IF (l_fiscal_year_invoice = l_fiscal_year_pf) THEN
572 l_status := TRUE;
573 ELSE
574 RETURN FALSE;
575 END IF;
576 END LOOP;
577
578 END LOOP;
579 ELSE
580 /* Distribute button is pressed and a specific Distribution line is being matched */
581 BEGIN
582 /* Get the Payment Forecast Date for that particular distribution line*/
583 SELECT CC_DET_PF_DATE
584 INTO l_pf_date
585 FROM IGC_CC_DET_PF
586 WHERE CC_acct_LINE_ID = (SELECT CC_acct_LINE_ID
587 FROM IGC_CC_ACCT_LINES
588 WHERE cc_header_id = (SELECT cc_header_id
589 FROM IGC_CC_HEADERS
590 WHERE CC_NUM = l_cc_num)
591 AND CC_ACCT_LINE_NUM = x_shipment_num)
592 AND CC_DET_PF_LINE_NUM = x_po_dist_num;
593 EXCEPTION
594 WHEN OTHERS THEN
595 FND_MESSAGE.SET_NAME('IGC', 'IGC_LOGGING_UNEXP_ERROR');
596 FND_MESSAGE.SET_TOKEN('CODE', SQLCODE);
597 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
598 APP_EXCEPTION.RAISE_EXCEPTION;
599 END;
600
601
602 /* Get the fiscal year for the Payment Forecast Date */
603 BEGIN
604 SELECT DISTINCT period_year
605 INTO l_fiscal_year_pf
606 FROM gl_period_statuses
607 WHERE set_of_books_id = (SELECT set_of_books_id
608 FROM AP_SYSTEM_PARAMETERS)
609 AND l_pf_date BETWEEN START_DATE AND END_DATE;
610 EXCEPTION
611 WHEN OTHERS THEN
612 FND_MESSAGE.SET_NAME('IGC', 'IGC_LOGGING_UNEXP_ERROR');
613 FND_MESSAGE.SET_TOKEN('CODE', SQLCODE);
614 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
615 APP_EXCEPTION.RAISE_EXCEPTION;
616 END;
617
618
619
620 IF (l_fiscal_year_invoice = l_fiscal_year_pf) THEN
621 l_status := TRUE;
622 ELSE
623 RETURN FALSE;
624 END IF;
625
626 END IF;
627 -- The function is called from Invoice Gateway form
628 -- Eight cases needs to be handled
629 -- a. Only PO number is entered so check the dates of the pf lines belonging to
630 -- each of the lines and their shipments
631 -- b. PO number and line number are entered so check the dates of the pf lines
632 -- belonging to that line number and its corresponding shipments
633 -- c. PO number,line number and shipment number is entered so check the dates of
634 -- pf lines belonging only to that shipment number and line number.
635 -- d. PO number, line number, shipment number and distribution is entered so check
636 -- date only for that specfic pf line
637 -- e. PO number and shipment number entered.
638 -- f. PO number and distribution number entered.
639 -- g. PO number, shipment number and distribution number entered.
640 -- h. PO number, line number and distribution number entered.
641 ELSIF x_form_name = 'APXIISIM' THEN
642 -- Case a: Only PO number is entered so check for all the lines,shipments and distributions
643 IF x_line_num IS NULL AND x_shipment_num IS NULL AND x_po_dist_num IS NULL THEN
644
645 FOR c_get_po_line_id1 IN c_get_po_line_id(x_po_header_id)
646 LOOP
647 FOR c_get_shipment_num1 IN c_get_shipment_num(c_get_po_line_id1.po_line_id)
648 LOOP
649 FOR c_get_cc_acct_line_id1 IN c_get_cc_acct_line_id(c_get_shipment_num1.shipment_num)
650 LOOP
651 FOR c_get_distribution_num1 IN c_get_distribution_num(c_get_po_line_id1.po_line_id,
652 c_get_shipment_num1.line_location_id)
653
654 LOOP
655 BEGIN
656 SELECT cc_det_pf_date
657 INTO l_cc_det_pf_date
658 FROM igc_cc_det_pf
659 WHERE cc_acct_line_id = c_get_cc_acct_line_id1.cc_acct_line_id
660 AND cc_det_pf_line_num = c_get_distribution_num1.distribution_num;
661 EXCEPTION
662 WHEN OTHERS THEN
663 FND_MESSAGE.SET_NAME('IGC', 'IGC_LOGGING_UNEXP_ERROR');
664 FND_MESSAGE.SET_TOKEN('CODE', SQLCODE);
665 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
666 APP_EXCEPTION.RAISE_EXCEPTION;
667 END;
668
669
670 BEGIN
671 SELECT DISTINCT period_year
672 INTO l_fiscal_year_pf
673 FROM gl_period_statuses
674 WHERE set_of_books_id = (SELECT set_of_books_id
675 FROM AP_SYSTEM_PARAMETERS)
676 AND l_cc_det_pf_date BETWEEN START_DATE AND END_DATE;
677 EXCEPTION
678 WHEN OTHERS THEN
679 FND_MESSAGE.SET_NAME('IGC', 'IGC_LOGGING_UNEXP_ERROR');
680 FND_MESSAGE.SET_TOKEN('CODE', SQLCODE);
681 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
682 APP_EXCEPTION.RAISE_EXCEPTION;
683 END;
684
685 IF (l_fiscal_year_invoice = l_fiscal_year_pf) THEN
686 l_status := TRUE;
687 ELSE
688 RETURN FALSE;
689 END IF;
690
691 END LOOP;
692 END LOOP;
693 END LOOP;
694 END LOOP;
695
696 -- Case b: Only PO number and line number is entered so check for all the shipments and its
697 -- distributions
698 ELSIF x_line_num IS NOT NULL AND x_shipment_num IS NULL AND x_po_dist_num IS NULL THEN
699
700 FOR c_get_shipment_num1 IN c_get_shipment_num(x_po_line_id)
701 LOOP
702 FOR c_get_cc_acct_line_id1 IN c_get_cc_acct_line_id(c_get_shipment_num1.shipment_num)
703 LOOP
704 FOR c_get_distribution_num1 IN c_get_distribution_num(x_po_line_id,
705 c_get_shipment_num1.line_location_id)
706
707 LOOP
708 BEGIN
709 SELECT cc_det_pf_date
710 INTO l_cc_det_pf_date
711 FROM igc_cc_det_pf
712 WHERE cc_acct_line_id = c_get_cc_acct_line_id1.cc_acct_line_id
713 AND cc_det_pf_line_num = c_get_distribution_num1.distribution_num;
714 EXCEPTION
715 WHEN OTHERS THEN
716 FND_MESSAGE.SET_NAME('IGC', 'IGC_LOGGING_UNEXP_ERROR');
717 FND_MESSAGE.SET_TOKEN('CODE', SQLCODE);
718 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
719 APP_EXCEPTION.RAISE_EXCEPTION;
720 END;
721
722 BEGIN
723 SELECT DISTINCT period_year
724 INTO l_fiscal_year_pf
725 FROM gl_period_statuses
726 WHERE set_of_books_id = (SELECT set_of_books_id
727 FROM AP_SYSTEM_PARAMETERS)
728 AND l_cc_det_pf_date BETWEEN START_DATE AND END_DATE;
729 EXCEPTION
730 WHEN OTHERS THEN
731 FND_MESSAGE.SET_NAME('IGC', 'IGC_LOGGING_UNEXP_ERROR');
732 FND_MESSAGE.SET_TOKEN('CODE', SQLCODE);
733 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
734 APP_EXCEPTION.RAISE_EXCEPTION;
735 END;
736
737
738 IF (l_fiscal_year_invoice = l_fiscal_year_pf) THEN
739 l_status := TRUE;
740 ELSE
741 RETURN FALSE;
742 END IF;
743
744 END LOOP;
745 END LOOP;
746 END LOOP;
747
748 -- Cases c and e : Po number, Line number(may or may not be entered), Shipment number is entered so
749 -- check for all the distributions
750
751 ELSIF x_shipment_num IS NOT NULL AND x_po_dist_num IS NULL THEN
752
753 FOR c_get_cc_acct_line_id1 IN c_get_cc_acct_line_id(x_shipment_num)
754 LOOP
755 FOR c_get_distribution_num1 IN c_get_distribution_num(x_po_line_id,
756 x_line_location_id)
757
758 LOOP
759 BEGIN
760 SELECT cc_det_pf_date
761 INTO l_cc_det_pf_date
762 FROM igc_cc_det_pf
763 WHERE cc_acct_line_id = c_get_cc_acct_line_id1.cc_acct_line_id
764 AND cc_det_pf_line_num = c_get_distribution_num1.distribution_num;
765 EXCEPTION
766 WHEN OTHERS THEN
767 FND_MESSAGE.SET_NAME('IGC', 'IGC_LOGGING_UNEXP_ERROR');
768 FND_MESSAGE.SET_TOKEN('CODE', SQLCODE);
769 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
770 APP_EXCEPTION.RAISE_EXCEPTION;
771 END;
772
773 BEGIN
774 SELECT DISTINCT period_year
775 INTO l_fiscal_year_pf
776 FROM gl_period_statuses
777 WHERE set_of_books_id = (SELECT set_of_books_id
778 FROM AP_SYSTEM_PARAMETERS)
779 AND l_cc_det_pf_date BETWEEN START_DATE AND END_DATE;
780 EXCEPTION
781 WHEN OTHERS THEN
782 FND_MESSAGE.SET_NAME('IGC', 'IGC_LOGGING_UNEXP_ERROR');
783 FND_MESSAGE.SET_TOKEN('CODE', SQLCODE);
784 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
785 APP_EXCEPTION.RAISE_EXCEPTION;
786 END;
787
788 IF (l_fiscal_year_invoice = l_fiscal_year_pf) THEN
789 l_status := TRUE;
790 ELSE
791 RETURN FALSE;
792 END IF;
793
794 END LOOP;
795 END LOOP;
796
797 -- Cases d, f, g and h:
798 ELSIF x_po_dist_num IS NOT NULL THEN
799
800 l_shipment_num := x_shipment_num;
801
802 IF x_shipment_num IS NULL THEN
803 BEGIN
804 SELECT line_location_id
805 INTO l_line_location_id
806 FROM po_distributions
807 WHERE po_distribution_id = x_po_distribution_id;
808
809
810 SELECT shipment_num
811 INTO l_shipment_num
812 FROM po_line_locations
813 WHERE line_location_id = l_line_location_id;
814 EXCEPTION
815 WHEN OTHERS THEN
816 FND_MESSAGE.SET_NAME('IGC', 'IGC_LOGGING_UNEXP_ERROR');
817 FND_MESSAGE.SET_TOKEN('CODE', SQLCODE);
818 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
819 APP_EXCEPTION.RAISE_EXCEPTION;
820 END;
821
822 END IF;
823
824 FOR c_get_cc_acct_line_id1 IN c_get_cc_acct_line_id(l_shipment_num)
825 LOOP
826 BEGIN
827 SELECT cc_det_pf_date
828 INTO l_cc_det_pf_date
829 FROM igc_cc_det_pf
830 WHERE cc_acct_line_id = c_get_cc_acct_line_id1.cc_acct_line_id
831 AND cc_det_pf_line_num = x_po_dist_num;
832 EXCEPTION
833 WHEN OTHERS THEN
834 FND_MESSAGE.SET_NAME('IGC', 'IGC_LOGGING_UNEXP_ERROR');
835 FND_MESSAGE.SET_TOKEN('CODE', SQLCODE);
836 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
837 APP_EXCEPTION.RAISE_EXCEPTION;
838 END;
839
840 BEGIN
841 SELECT DISTINCT period_year
842 INTO l_fiscal_year_pf
843 FROM gl_period_statuses
844 WHERE set_of_books_id = (SELECT set_of_books_id
845 FROM AP_SYSTEM_PARAMETERS)
846 AND l_cc_det_pf_date BETWEEN START_DATE AND END_DATE;
847 EXCEPTION
848 WHEN OTHERS THEN
849 FND_MESSAGE.SET_NAME('IGC', 'IGC_LOGGING_UNEXP_ERROR');
850 FND_MESSAGE.SET_TOKEN('CODE', SQLCODE);
851 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
852 APP_EXCEPTION.RAISE_EXCEPTION;
853 END;
854
855 IF (l_fiscal_year_invoice = l_fiscal_year_pf) THEN
856 l_status := TRUE;
857 ELSE
858 RETURN FALSE;
859 END IF;
860
861 END LOOP;
862
863 END IF;
864 END IF;
865
866 END IF;
867 RETURN TRUE;
868
869 END DATE_IS_VALID;
870
871 /*=======================================================================+
872 | FUNCTION XML_REPORT_ENABLED
873 | |
874 | Note : This function is designed to decide if the xml report(s) is |
875 | to be triggered or not. Presently it returns true. In future |
876 | the function can be modified to incorporate profile options |
877 and return true/false based on conditions. |
878 +=======================================================================*/
879
880 FUNCTION XML_REPORT_ENABLED
881 RETURN BOOLEAN
882 IS
883 BEGIN
884 RETURN FALSE;
885 END XML_REPORT_ENABLED;
886
887 /*=======================================================================+
888 | PROCEDURE GET_XML_LAYOUT_INFO
889 | |
890 | Note : This procedure is designed to get layout information of the |
891 | xml report that is to be generated. |
892 | |
893 | Parameters : |
894 | |
895 | p_lang Language, takes the default value |
896 | when no value is obtained |
897 | p_terr Territory, takes the default value |
898 | when no value is obtained |
899 | p_lob_code BiPubllisher Code for the XML Report |
900 | p_application_short_name Short Name of the Application |
901 | p_template_code Template Code for the XML Report |
902 +=======================================================================*/
903
904 PROCEDURE GET_XML_LAYOUT_INFO(
905 p_lang IN OUT NOCOPY VARCHAR2,
906 p_terr IN OUT NOCOPY VARCHAR2,
907 p_lob_code IN VARCHAR2,
908 p_application_short_name IN VARCHAR2,
909 p_template_code IN VARCHAR2
910 )
911 IS
912 ls_lang VARCHAR2(10);
913 v_cnt NUMBER;
914 l_lang VARCHAR2(10);
915 CURSOR xml_cur
916 IS
917 SELECT LANGUAGE,
918 TERRITORY
919 FROM XDO_LOBS
920 WHERE LOB_CODE = p_lob_code
921 AND APPLICATION_SHORT_NAME = p_application_short_name
922 AND (LOB_TYPE = 'TEMPLATE'
923 OR LOB_TYPE = 'MLS_TEMPLATE')
924 AND LANGUAGE = p_lang
925 AND XDO_FILE_TYPE = 'XSL-FO';
926 BEGIN
927 SELECT SUBSTR(userenv('LANG'),1,4) INTO ls_lang FROM dual;
928 IF ls_lang = 'US' THEN
929 p_lang := 'en' ;
930 ELSE
931 p_lang := lower(ls_lang);
932 END IF;
933 OPEN xml_cur;
934 FETCH xml_cur INTO l_lang,p_terr;
935 v_cnt := xml_cur%rowcount;
936 CLOSE xml_cur;
937 IF v_cnt = 0 THEN
938 SELECT default_language,
939 default_territory
940 INTO l_lang,
941 p_terr
942 FROM XDO_TEMPLATES_B
943 WHERE TEMPLATE_CODE = p_template_code
944 AND APPLICATION_SHORT_NAME = p_application_short_name ;
945 END IF;
946 l_lang := p_lang;
947 END GET_XML_LAYOUT_INFO;
948
949
950
951 END IGC_CC_COMMON_UTILS_PVT;
952
[Home] [Help]